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 ......
Ó¦ÓÃʾÀý£º
-- ´´½¨2¸ö²âÊÔ±í
CREATE TABLE [dbo].[Table_2019]([Data] [nchar](2019) NOT NULL)
CREATE TABLE [dbo].[Table_2020]([Data] [nchar](2020) NOT NULL)
go
-- Ìî³äÊý¾Ý
declare @i int
set @i = 0
while(@i < 20)
begin
insert Table_2019(Data) values('')
  ......
ÔÚʹÓùý³ÌÖдó¼Ò¾³£Åöµ½Êý¾Ý¿âÈÕÖ¾·Ç³£´óµÄÇé¿ö£¬ÔÚÕâÀï½éÉÜÁËÁ½ÖÖ´¦Àí·½·¨……
¡¡¡¡·½·¨Ò»
¡¡¡¡Ò»°ãÇé¿öÏ£¬SQLÊý¾Ý¿âµÄÊÕËõ²¢²»Äܴܺó³Ì¶ÈÉϼõСÊý¾Ý¿â´óС£¬ÆäÖ÷Òª×÷ÓÃÊÇÊÕËõÈÕÖ¾´óС£¬Ó¦µ±¶¨ÆÚ½øÐд˲Ù×÷ÒÔÃâÊý¾Ý¿âÈÕÖ¾¹ý´ó¡£
¡¡¡¡1¡¢ÉèÖÃÊý¾Ý¿âģʽΪ¼òµ¥Ä£Ê½£º´ò¿ªSQLÆóÒµ¹ÜÀíÆ÷£¬ÔÚ¿ØÖÆ̨¸ùÄ ......
SQL Server 2005ÖеĴ洢¹ý³Ì²¢·¢ÎÊÌâ
ÎÒÔÚSQL Server2005ÖÐÓöµ½Á˲¢·¢ÎÊÌâ¡£ÎÒ³ÖÓгµÆ±µÄ¹«¹²Æû³µÉÏÓÐһЩ¿ÕÏеÄ×ùλ¡£ÎÒÔÚ²åÈëÏúÊ۵IJéƱ֮ǰ£¬ÐèÒª²é¿´ÊÇ·ñ»¹ÓпÕÏеÄ×ùλ¡£ÎҵĴ洢¹ý³Ì×öµÄÊÂÇéÈçÏÂËùʾ£º
create PROCEDURE add_ticket — parameters DECLARE free_seats int BEGIN TRANSACTION select free_se ......