SQL Server 2008ÐÐÊý¾ÝºÍÒ³Êý¾ÝѹËõ½âÃÜ
SQL ServerµÄÐÔÄÜÖ÷Ҫȡ¾öÓÚ´ÅÅÌI/OЧÂÊ£¬Ìá¸ßI/OЧÂÊijÖÖ³ÌÐòÉϾÍÒâζ×ÅÌá¸ßÐÔÄÜ¡£SQL Server 2008ÌṩÁËÊý¾ÝѹËõ¹¦ÄÜÀ´Ìá¸ß´ÅÅÌI/O¡£
Êý¾ÝѹËõÒâζ׿õСÊý¾ÝµÄÓдÅÅÌÕ¼ÓÃÁ¿£¬ËùÒÔÊý¾ÝѹËõ¿ÉÒÔÓÃÔÚ±í£¬¾Û¼¯Ë÷Òý£¬·Ç¾Û¼¯Ë÷Òý£¬ÊÓͼË÷Òý»òÊÇ·ÖÇø±í£¬·ÖÇøË÷ÒýÉÏ¡£
Êý¾ÝѹËõ¿ÉÒÔÔÚÁ½¸ö¼¶±ðÉÏʵÏÖ£ºÐ춱ðºÍÒ³¼¶±ð¡£Ò³¼¶±ðѹËõ°üÀ¨Ð춱ðѹËõ¡£±íºÍË÷ÒýÔÚ´´½¨µÄͬʱ¾Í¿ÉÒÔѹËõ£¬Ò²¿ÉÒÔʹÓÃAlter Table Rebuild With »òÊÇ Alter Index Rebuild WithÓ﷨ѹËõÒÑ´æÔÚ±í»òÊÇË÷Òý¡£µ±¶Ñ(Heap)ÉϵÄѹËõ״̬¸Ä±äʱ£¬ËùÓеķǾۼ¯Ë÷ÒýÒ²»áÖؽ¨¡£
ÏÂÃæÈÃÎÒÃÇ¿´¿´Ñ¹Ëõ¹ý³Ìµ½µ×¶¼×öÁËЩʲô¡£
ÐÐѹËõ£º
1.¼õÉÙÔªÊý¾ÝÍ·¼Ç¼Êý¾Ý¡£
2.ËùÓеÄÊýÖµÀàÐÍ(integer,decimal,float)»ò»ùÓÚÊýÖµÀàÐ͵ÄÀàÐͶ¼»á±»Ñ¹Ëõ(datetime,money)¡£±ÈÈ磬100±»´æÔÚÒ»¸öintÐÍ×Ö¶ÎÖУ¬Õ¼4룬µ«ÊÇ´Ó0~255Ö»ÐèÒª1λ£¬Ñ¹Ëõºó£¬¾Í½ÚÊ¡ÁË3byte¿Õ¼ä¡£
3.CharºÍNChar¶¼±»´æ·ÅÓڿɱ䳤¶ÈµÄÀàÐÍÖС£ÔÒòͬÉÏ¡£±ÈÈ磬CSDN±»´æÔÚChar(10)ÖУ¬µ«ÊÇËüÖ»ÐèÒªChar(4)¿Õ¼ä£¬ËùÒÔѹËõºó¾ÍÊÍ·ÅÁËChar(6)¸ö¿Õ¼ä¡£
4.ËùÓÐÀàÐ͵ÄNULLºÍ0¶¼²»Õ¼×Ö½ÚÁË¡£
ҳѹËõ£º
1.½øÐÐÐÐѹËõ¡£
2.Ç°±êѹËõ£ºÃ¿Ò»Ò³ÖеÄËùÓÐÁУ¬ÔÚÐбêÍ·ÏÂÃ棬ÿÐж¼´æ´¢×ÅÒ»¸öÐж¨ÒåÖµ£¬Ñ¹Ëõºó£¬ËùÓÐÐеĶ¨ÒåÖµ¶¼±»Ìæ»»³ÉÐÐÍ·ÖµµÄÒýÓá£
3.×ÖµäѹËõ£º×ÖµäѹËõºÍÇ°±êѹËõÀàËÆ¡£Ç°±êѹËõÖУ¬Ò»¸öÓëÆäËûÆÕֵͨ²»Í¬µÄÖµ±»¶¨Ò嵽ÿһÁÐÉÏ¡£µ«ÊÇ×ÖµäѹËõÖУ¬Ã¿Ò»Ò³ÖÐËùÓÐÁеÄÆÕֵͨ±»´æÔÚÏÂÒ»ÐеÄÐÐÍ·Ç°Ã档Ȼºó£¬ÕâЩֵ±»Ìæ»»³ÉÐÂÐеÄÒýÓÃÖµ¡£
ÏÂÃæÎÒÃÇÀ´¿´Ò»¸öÀý×Ó:
USE tempdb
GO
CREATE TABLE TestCompression (col1 INT, col2 CHAR(50))
GO
INSERT INTO TestCompression VALUES (10, 'compression testing')
GO 5000
-- Original
EXEC sp_spaceused TestCompression
GO
-- DATA_COMPRESSION = ROW
ALTER TABLE TestCompression
REBUILD WITH (DATA_COMPRESSION = ROW);
GO
EXEC sp_spaceused TestCompression
GO
-- DATA_COMPRESSION = PAGE
ALTER TABLE TestCompression
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
EXEC sp_spaceused TestCompression
GO
-- DATA_COMPRESSION = NONE
ALTER TABLE TestCompression
REBUILD WITH (DATA_COMPRESSION = NONE);
GO
EXEC sp_spaceused TestCompression
GO
Ïà¹ØÎĵµ£º
Õ⼸Ì츺ÔðÒ»¸ö¼Ò½ÌÃÅ»§ÍøÕ¾µÄ¿ª·¢£¬»ùÓÚcakephp¿ò¼Ü¡£ÔÚÅàѵ»ú¹¹±í£¨schools£©ÖдæÔÚÒ»¸ö×Ö¶ÎsubjectÓÃÀ´´æ´¢ÁíÒ»¸öÊý¾Ý±í
£¨subjects£©ÖмǼµÄidÖµ£¬ÇÒ´æ´¢ÐÎʽΪ£º'1,2,3,4,5'¡£µ«ÊÇÔÚÓ¦Óø߼¶ËÑË÷¹ýÂËʱҳÃæselectÑ¡ÏîoptionµÄ´«ÖµÎª
subjectsµÄidÖµ£¬ÐèÒªÅжϲéѯ±íschoolsÖÐsubject×ֶδæÔÚ´Ëid£¬¼´²éѯÏÔʾ´Ë¼Ç¼ ......
sql2005ÖÐÒ»¸öxml¾ÛºÏµÄÀý×Ó ÊÕ²Ø
¸ÃÎÊÌâÀ´×ÔÂÛ̳ÌáÎÊ£¬ÑÝʾSQL´úÂëÈçÏÂ
--½¨Á¢²âÊÔ»·¾³
set nocount on
create table test(ID varchar(20),NAME varchar(20))
insert into test select '1','aaa'
insert into test select '1','bbb'
insert into test select '1','ccc'
insert into test select '2','ddd'
inser ......
--
¾ÛºÏº¯Êý
use
pubs
go
select
avg
(
distinct
price)
--
Ëãƽ¾ùÊý
from
titles
where
type
=
'
business
'
go
use
pubs
go
select
max
(ytd_sales)
--
×î´óÊý
from
titles
go
use
pubs
go
select
min
(ytd_sales)
--
×îСÊý
fr ......
µ¼Èë
Èç¹û±íÒÑ´æÔÚ£¬SQLÓï¾äΪ£º
insert into aa select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:\OutData.xls;Extended Properties=Excel 8.0')...[sheet1$]
ÆäÖУ¬aaÊDZíÃû£¬D:\OutData.xlsÊÇexcelµÄȫ·¾¶ sheet1ºó±ØÐë¼ÓÉÏ$
Èç¹û±í²»´æÔÚ£¬SQLÓï¾äΪ£º
SELECT * INTO aa from OPENDAT ......
ÎÒÃÇÒª×öµ½²»µ«»áдSQL£¬»¹Òª×öµ½Ð´³öÐÔÄÜÓÅÁ¼µÄSQLÓï¾ä¡£
£¨1£©Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
OracleµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving
table)½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏÂ,Äã±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í¡£È ......