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
Ïà¹ØÎĵµ£º
Ò»£®¼òµ¥SQL²éѯ£º
1£©:ͳ¼Æÿ¸ö²¿ÃÅÔ±¹¤µÄÊýÄ¿
select dept,count(*) from employee group by dept;
2£©:ͳ¼Æÿ¸ö²¿ÃÅÔ±¹¤µÄÊýÄ¿´óÓÚÒ»¸öµÄ¼Ç¼
select dept,count(*) from employee group by dept having count(*)>1;
3£©:ͳ¼Æ¹¤×ʳ¬¹ý1200µÄÔ±¹¤ËùÔÚ²¿ÃŵÄÃû³Æ
select e.first_name,salary,d.name
from s_emp ......
µ¼Èë
Èç¹û±íÒÑ´æÔÚ£¬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ÊÇÒ»ÖÖµäÐ͵ķǹý³Ì»¯³ÌÐòÉè¼ÆÓïÑÔ£¬ÕâÖÖÓïÑÔµÄÌصãÊÇ£º
Ö»Ö¸¶¨ÄÄЩÊý¾Ý±»²Ù×Ý£¬ÖÁÓÚ¶ÔÕâЩÊý¾ÝÒªÖ´ÐÐÄÄЩ²Ù×÷£¬ÒÔ¼°Õâ
Щ²Ù×÷ÊÇÈçºÎ
Ö´ÐÐµÄ ......
ÕªÒª£º±¾ÎĽéÉÜÁËSQL Server´æ´¢¹ý³ÌÏà¶ÔÓÚÆäËûµÄÊý¾Ý¿â·ÃÎÊ·½·¨µÄÓŵ㼰SQL Server´æ´¢¹ý³ÌµÄ·ÖÀàµÈ¡£
SQL Server´æ´¢¹ý³ÌÊÇÒ»¸ö±»ÃüÃûµÄ´æ´¢ÔÚ·þÎñÆ÷ÉϵÄTransacation-SqlÓï¾ä¼¯ºÏ,ÊÇ·â×°Öظ´ÐÔ¹¤×÷µÄÒ»ÖÖ·½·¨,ËüÖ§³ÖÓû§ÉùÃ÷µÄ±äÁ¿¡¢Ìõ¼þÖ´ÐкÍÆäËûÇ¿´óµÄ±à³Ì¹¦ÄÜ¡£
SQL Server´æ´¢¹ý³ÌÏà¶ÔÓÚÆäËûµÄÊý¾Ý¿â·ÃÎÊ·½·¨Ó ......
ÔÒò·ÖÎö:
SERVERPROPERTY º¯ÊýµÄ ServerName ÊôÐÔÓë@@SERVERNAME ·µ»ØÏàËƵÄÐÅÏ¢¡£ServerName ÊôÐÔÌṩWindows ·þÎñÆ÷ºÍʵÀýÃû³Æ£¬Á½Õß¹²Í¬¹¹³ÉΨһµÄ·þÎñÆ÷ʵÀý£»@@SERVERNAME Ìṩµ±Ç°ÅäÖõı¾µØ·þÎñÆ÷Ãû³Æ¡£
Èç¹û°²×°Ê±Î´¸ü¸ÄĬÈÏ·þÎñÆ÷Ãû³Æ£¬Ôò ServerName ÊôÐÔºÍ @@SERVERNAME ·µ»ØÏàͬµÄÐÅÏ¢¡£Èç¹ûÔÚ°²×°Ê±Òѽ«±¾µ ......