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頁ÈÔÈ»為Ë÷Òý
Ïà¹ØÎĵµ£º
drop table #Tmp --ɾ³ýÁÙʱ±í#Tmp
create table #Tmp --´´½¨ÁÙʱ±í#Tmp
(
ID int IDENTITY (1,1) not null, --´´½¨ÁÐID,²¢ÇÒÿ´ÎÐÂÔöÒ»Ìõ¼Ç¼¾Í»á¼Ó1
WokNo &nb ......
Óû§×Ô¶¨Ò庯Êý£¨User Defined Functions£©ÊÇSQL Server µÄÊý¾Ý¿â¶ÔÏó£¬Ëü²»ÄÜÓÃÓÚÖ´ÐÐһϵÁиıäÊý¾Ý¿â״̬µÄ²Ù×÷£¬µ«Ëü¿ÉÒÔÏñϵͳº¯ÊýÒ»ÑùÔÚ²éѯ»ò´æ´¢¹ý³ÌµÈµÄ³ÌÐò¶ÎÖÐʹÓã¬Ò²¿ÉÒÔÏñ´æ´¢¹ý³ÌÒ»Ñùͨ¹ý EXECUTE ÃüÁîÀ´Ö´ÐС£Óû§×Ô¶¨Ò庯ÊýÖд洢ÁËÒ»¸öTransact-SQL Àý³Ì£¬¿ÉÒÔ·µ»ØÒ»¶¨µÄÖµ¡£
¡¡¡¡ÔÚSQL Server Öиù¾Ýº ......
SQL> setsqlprompt"_user _privilege> " ͨ¹ýÒÔÉÏÉèÖã¬ÄãµÄsqlplusÌáʾ±êʶ¾Í³ÉÁËÒÔϸñʽÁË£º SYS AS SYSDBA> Õâ¾Í±íÃ÷µ±Ç°µÄµÇ¼ÕʺÅÊÇSYS£¬Éí·ÝÊÇSYSDBA¡£Çë×¢ÒâÔÚÉÏÃæÓï¾äÖÐʹÓÃÁËÁ½¸öÌØÊâµÄÔ¤¶¨ÒåµÀµÂ±äÁ¿£º_userºÍ_privilege£¬·Ö±ð¶¨ÒåÁ˵±Ç°Óû§ºÍÕâ¸öÓû§µÄµÇ¼Éí·Ý£¨È¨ÏÞ£©¡£ÏÖÔÚÎÒÃÇÔÙÔö¼ ......
SQL Server 2005ÖеĴ洢¹ý³Ì²¢·¢ÎÊÌâ
ÎÒÔÚSQL Server2005ÖÐÓöµ½Á˲¢·¢ÎÊÌâ¡£ÎÒ³ÖÓгµÆ±µÄ¹«¹²Æû³µÉÏÓÐһЩ¿ÕÏеÄ×ùλ¡£ÎÒÔÚ²åÈëÏúÊÛµÄ²éÆ±Ö®Ç°£¬ÐèÒª²é¿´ÊÇ·ñ»¹ÓпÕÏеÄ×ùλ¡£ÎҵĴ洢¹ý³Ì×öµÄÊÂÇéÈçÏÂËùʾ£º
create PROCEDURE add_ticket — parameters DECLARE free_seats int BEGIN TRANSACTION select free_se ......
Ê×ÏȽéÉÜÒ»ÏÂʲôÊÇ´æ´¢¹ý³Ì£º´æ´¢¹ý³Ì¾ÍÊǽ«³£ÓõĻòºÜ¸´ÔӵŤ×÷£¬Ô¤ÏÈÓÃSQLÓï¾äдºÃ²¢ÓÃÒ»¸öÖ¸¶¨µÄÃû³Æ´æ´¢ÆðÀ´£¬²¢ÇÒÕâÑùµÄÓï¾äÊÇ·ÅÔÚÊý¾Ý¿âÖе쬻¹¿ÉÒÔ¸ù¾ÝÌõ¼þÖ´Ðв»Í¬SQLÓï¾ä£¬ ÄÇôÒÔºóÒª½ÐÊý¾Ý¿âÌṩÓëÒѶ¨ÒåºÃµÄ´æ´¢¹ý³ÌµÄ¹¦ÄÜÏàͬµÄ·þÎñʱ£¬Ö»Ðèµ÷ÓÃexecute,¼´¿É×Ô¶¯Íê³ÉÃüÁî¡£
¡¡¡¡Çë´ó¼ÒÀ´Á˽âÒ»ÏÂ´æ´ ......