SQL Server Indexing(½é紹SQL ServerË÷Òý)
5.2 Clustered Indexes(¾Û¼¯Ë÷Òý) (page 122)
¾Û¼¯Ë÷Òý決¶¨ÁËÒ»個±í數據µÄÎïÀíÅÅÁÐ順Ðò£¬ËùÒÔ£¬Ò»個±í隻ÄÜÓÐÒ»個¾Û¼¯Ë÷Òý¡£圖5.1±íʾÁËÒ»個¾Û¼¯Ë÷ÒýµÄ結構¡£
Figure 5.1
The structure of a clustered index
Ò»個¾Û¼¯Ë÷ÒýµÄ×îµ×層(the lowest level)ÓÉËüµÄ數據頁(data pages)組³É. ¾Û¼¯Ë÷ÒýµÄ數據頁½Ð×ö這個Ë÷ÒýµÄ葉節點(leaf level of the index)¡£Ë÷ÒýµÄÆäËü²¿·Ö則ÓÉË÷Òý頁(index pages)組³É¡£×î頂層µÄË÷Òý頁½Ð×öË÷ÒýµÄ¸ù節點(index root)¡£½éì¶葉節點與¸ù節點µÄË÷Òý頁£¬½Ð×öÖÐ間頁(intermediate-level pages).
Ë÷ÒýÖÐÿ個層級µÄ·Ö頁¶¼ÊÇ鏈½ÓÔÚÒ»ÆðµÄ¡£Ë÷Òý頁ÉϵÄÿ個項Ä¿¶¼°üº¬Ò»個鍵Öµ(key value),ÒÔ¼°Ò»個Ö¸ÏòÏÂÒ»個Ë÷Òý頁µÄ×îµ×層(the lowest level)µÄÖ¸針¡£¾Û¼¯Ë÷ÒýµÄÖ¸針¾ÍÊÇÒ»個頁號(page number)£¬ÔÙ¼ÓÉÏÒ»個ÎļþID(File ID)×÷為Ç°綴£¬這ÊÇÒò為頁號僅ÔÚͬһ個數據庫ÎļþÖÐÊÇΨһµÄ¡£Ò»個ÎļþIDÔÙ¼ÓÉÏÒ»個頁號£¬¾Í組³ÉÁËÒ»個頁ID(page ID).
ÄÇ麼£¬SQL Server²é詢優»¯Æ÷ÈçºÎʹÓþۼ¯Ë÷Òý來獲È¡數據µÄÄØ£¿
1. ·µ»Ø單Ò»數據ÐÐ
SELECT * from customers WHERE customer_lname = 'Green'
Ê×ÏÈ£¬SQL Server從ϵ統±ísysindexes±íÖÐÈ¡µÃ±ícustomersµÄ¸ù頁(root page)£¬ÔÚ這個·¶ÀýÖУ¬Èç圖5.1Ëùʾ£¬ËüµÄ¸ù頁ÊÇ42. ¸ù頁ÖпÉÄÜ會Óкܶà個鍵Öµ£¬SQL Server將會ÕÒµ½²»´óì¶GreenµÄ×î´óµÄÄÇ個鍵Öµ£¬ÔÚ圖5.1ÖУ¬這個Öµ為ADAMS£¬ËüËù對應µÄÖ¸針£¬¼´頁號(page number)為58£¬ËùÒÔ頁號為58µÄË÷Òý頁將會±»獲µÃ¡£
Òò為58頁ÈÔÈ»為Ë÷Òý
Ïà¹ØÎĵµ£º
Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06
Select CONVERT ......
Óû§×Ô¶¨Ò庯Êý£¨User Defined Functions£©ÊÇSQL Server µÄÊý¾Ý¿â¶ÔÏó£¬Ëü²»ÄÜÓÃÓÚÖ´ÐÐһϵÁиıäÊý¾Ý¿â״̬µÄ²Ù×÷£¬µ«Ëü¿ÉÒÔÏñϵͳº¯ÊýÒ»ÑùÔÚ²éѯ»ò´æ´¢¹ý³ÌµÈµÄ³ÌÐò¶ÎÖÐʹÓã¬Ò²¿ÉÒÔÏñ´æ´¢¹ý³ÌÒ»Ñùͨ¹ý EXECUTE ÃüÁîÀ´Ö´ÐС£Óû§×Ô¶¨Ò庯ÊýÖд洢ÁËÒ»¸öTransact-SQL Àý³Ì£¬¿ÉÒÔ·µ»ØÒ»¶¨µÄÖµ¡£
¡¡¡¡ÔÚSQL Server Öиù¾Ýº ......
¸ÅÄÔÚ±àд°²È«´úÂëʱ£¬×îÖØÒªµÄ¹æÔòÖ®Ò»¾ÍÊÇ“¾ø¶Ô²»ÒªÃ¤Ä¿µÄÏàÐÅÓû§µÄÊäÈ딡£
ÀûÓÃADO.NET 2.0µÄSqlConnectionStringBuilderÀàÉú³ÉÊý¾Ý¿âÁ¬½Ó×Ö·û´®£¬Ëü¿ÉÒÔÓÐЧµÄ·ÀÖ¹“SQLÁ¬½Ó×Ö·û´®¶ñÒâ×¢È딣¬ÒòΪÕâ¸öÀàÊÇרÃÅΪSQL SERVERÉè¼ÆµÄËùÒÔ£»Ëü¼æÈݾÉʽ¹Ø¼ü×Ö¡£¹ØÓÚÈçºÎʹÓÃSqlConnectionS ......
<?xml version="1.0" encoding="UTF-8"?>
<mdx>
<sets>
<set>
<name>[promomonths] </name>
&n ......
ͨ¹ýË÷Òý£¬¿ÉÒÔ¼Ó¿ìÊý¾ÝµÄ²éѯËٶȺͼõÉÙϵͳµÄÏìӦʱ¼ä£»¿ÉÒÔʹ±íºÍ±íÖ®¼äµÄÁ¬½ÓËٶȼӿ졣µ«ÊÇ£¬²»ÊÇÔÚÈκÎʱºòʹÓÃË÷Òý¶¼Äܹ»´ïµ½ÕâÖÖЧ¹û¡£ÈôÔÚ²»Ç¡µ±µÄ³¡ºÏÏ£¬Ê¹ÓÃË÷Òý·´¶ø»áÊÂÓëԸΥ¡£ Ë÷ÒýÊÇÒÔ±íÁÐΪ»ù´¡µÄÊý¾Ý¿â¶ÔÏó¡£Ë÷ÒýÖб£´æ×űíÖÐÅÅÐòµÄË÷ÒýÁÐ
¡¡¡¡
ͨ¹ýË÷Òý£¬¿ÉÒÔ¼Ó¿ìÊý¾ÝµÄ²éѯËٶȺͼõÉÙϵͳµÄÏìӦʱ¼ä£ ......