·Ç¾Û¼¯Ë÷ÒýÓë¾Û¼¯Ë÷Òý¾ßÓÐÏàͬµÄ B Ê÷½á¹¹£¬ËüÃÇÖ®¼äµÄÏÔÖø²î±ðÔÚÓÚÒÔÏÂÁ½µã£º
»ù´¡±íµÄÊý¾ÝÐв»°´·Ç¾Û¼¯¼üµÄ˳ÐòÅÅÐòºÍ´æ´¢¡£
·Ç¾Û¼¯Ë÷ÒýµÄÒ¶²ãÊÇÓÉË÷ÒýÒ³¶ø²»ÊÇÓÉÊý¾ÝÒ³×é³É¡£
·Ç¾Û¼¯Ë÷Òý¼È¿ÉÒÔ½¨Ôڶѱí½á¹¹ÉÏÒ²¿ÉÒÔ½¨ÔÚ¾Û¼¯Ë÷Òý±íÉÏ£»·Ç¾Û¼¯Ë÷ÒýÖеÄÿ¸öË÷ÒýÐж¼°üº¬·Ç¾Û¼¯¼üÖµºÍÐж¨Î»·û¡£´Ë¶¨Î»·ûÖ¸Ïò¾Û¼¯Ë÷Òý»ò¶ÑÖаüº¬¸Ã¼üÖµµÄÊý¾ÝÐС£
Èç¹û±íÊǶÑÔòÐж¨Î»Æ÷ÊÇÖ¸ÏòÐеÄÖ¸Õë¡£¸ÃÖ¸ÕëÓÉÎļþ±êʶ·û (ID) ¡¢Ò³ÂëºÍÒ³ÉϵÄÐÐÊýÉú³É¡£Õû¸öÖ¸Õë³ÆΪÐÐ ID (RID) ¡£
Èç¹û±í°üº¬Óоۼ¯Ë÷Òý£¬ÔòÐж¨Î»Æ÷ÊÇÐеľۼ¯Ë÷Òý¼ü¡£Èç¹û¾Û¼¯Ë÷Òý²»ÊÇΨһµÄË÷Òý£¬ SQL Server ½«Ìí¼ÓÔÚÄÚ²¿Éú³ÉµÄÖµ£¨³ÆΪΨһֵ£©ÒÔʹËùÓÐÖظ´¼üΨһ¡£´ËËÄ×Ö½ÚµÄÖµ¶ÔÓÚÓû§²»¿É¼û¡£½öµ±ÐèҪʹ¾Û¼¯¼üΨһÒÔÓÃÓڷǾۼ¯Ë÷ÒýÖÐʱ£¬²ÅÌí¼Ó¸ÃÖµ¡£ SQL Server ͨ¹ýʹÓô洢ÔڷǾۼ¯Ë÷ÒýµÄÒ¶ÐÐÄڵľۼ¯Ë÷Òý¼üËÑË÷¾Û¼¯Ë÷ÒýÀ´¼ìË÷Êý¾ÝÐС£
B Ê÷µÄÒ³¼¯ºÏÓÉ sys.system_internals_allocation_units ϵͳÊÓͼÖÐµÄ root_page Ö¸Õ붨λ¡£
¶Ñ±í
-- ´´½¨Ò»ÕŶѱí
CREATE TABLE testHeapIndex
(
name CHAR ( 200),
type1 CHAR ( 900),
type2 CHAR ( 900)
)
-- ·Ö±ð´´½¨Ò»¸öΨһË÷ÒýºÍÒ»¸ö·ÇΨһË÷Òý
CREATE UNIQUE INDEX idx_testHeapIndex1 ON testHeapIndex ( type1 )
CREATE INDEX idx_testHeapIndex2 ON testHeapIndex ( type2 )
-- ²åÈë²âÊÔÊý¾Ý
INSERT INTO testHeapIndex VALUES ( A , A1 , A2 )
INSERT INTO testHeapIndex VALUES ( B , B1 , B2 )
INSERT INTO testHeapIndex VALUES ( C , C1 , B2 )
INSERT INTO testHeapIndex VALUES ( D , D1 , B2 )
INSERT INTO testHeapIndex VALUES ( E , E1 , C2 )
INSERT INTO testHeapIndex VALUES ( F , F1 , F1 )
INSERT INTO testHeapIndex VALUES ( G , G1 , G1 )
INSERT INTO testHeapIndex VALUES ( H , H1 , G1 )
INSERT INTO testHeapIndex VALUES ( I , I1 , G1 )
INSERT INTO testHeapIndex VALUES ( J , J1 , J1 )
-- »ñÈ¡¸Ã±íµÄÏàÓ¦Ò³ÃæÐÅÏ¢
SELECT A . NAME TABLE_NAME , B . NAME INDEX_NAME , B . INDEX_ID
FROM SYS . OBJECTS A , SYS . INDEXES B
WHERE A . OBJECT_ID = B . OBJECT_ID AND A . NAME = testHeapIndex
TRUNCATE TABLE tablepage ;
INSERT INTO tablepage EXEC ( DBCC IND(testdb,testHeapIndex,0) );
INSERT INTO tablepage EXEC ( DBCC IND(testdb,testHeapIndex,2) );
INSERT INTO tablepage EXEC ( DBCC IND(testdb,testHeapIndex,3) );
SELECT
b . name table_name ,
CASE WHEN c . type = 0 THEN ¶Ñ
WHEN c . type = 1 THEN ¾Û¼¯
WHEN c . type = 2 THEN ·Ç¾Û¼¯
ELSE ÆäËû
END index_type ,
c . name index_name ,
PagePID , IAMPID , ObjectID , IndexID , Pagetype , IndexLevel ,
NextPagePID , PrevPagePID
FROM tablepage a , sys . objects b , sys . indexes c
WHERE A . ObjectID = b . object_id
AND A . ObjectID = c . object_id
AND a . IndexID = c . index_id
-- »ñÈ¡¸Ã±íµÄ root Ò³ÃæµØÖ·£¬¾Û¼¯Ë÷ÒýµÄ¸ù½Úµã±ØÐëͨ¹ýÏÂÃæ½Å±¾²ÅÄÜÕÒµ½
SELECT c . name , a . type_desc , d . name ,
total_pages , used_pages , data_pages ,
testdb . dbo . f_get_page ( first_page ) first_page_address ,
testdb . dbo . f_get_page ( root_page ) root_address ,
testdb . dbo . f_get_page ( first_iam_page ) IAM_address
FROM sys . system_internals_allocation_units a , sys . partitions b , sys . objects c , sys . indexes d
WHERE a . container_id = b . partition_id and b . object_id = c . object_id
AND d . object_id = b . object_id AND d . index_id = b . index_id
AND c . name in ( testHeapIndex )
-- ÏÂÃæ¸÷¸öÀý×Ó»ñÈ¡Ïà¹ØÒ³ÃæºÍ root Ò³ÃæµÄ½Å±¾»ù±¾Ïàͬ£¬²»ÔÙÖظ´
¶Ñ±íÉϵÄΨһ·Ç¾Û¼¯Ë÷Òý
Ê×ÏȶѱíÊÇÓÉÈô¸ÉÒ¶×ÓÒ³Ãæ×é³ÉµÄ£¬Ï໥֮¼äûÓÐÁ´½Ó¹Øϵ£¬ÍêÈ«¿¿ IAM Ò³Ãæ½øÐйÜÀíºÍά»¤¡£
ÎÒÃÇ¿ÉÒÔ¿´µ½ page(1:90) Ϊ¸ÃΨһ·Ç¾Û¼¯Ë÷ÒýµÄ¸ù½Úµã£¬ÒòΪÊý¾ÝÁ¿ÉÙʵ¼ÊÉÏÖ»ÓÐÒ»¸öË÷Òý½Úµã£¬¼´´óÓÚµÈÓÚ I1 µÄÖ¸ÏòÒ¶×ÓÒ³Ãæ page(1:93) £¬Ð¡ÓÚ I1 µÄÔòÖ¸ÏòÒ¶×ÓÒ³Ãæ page(1:55) Ò³Ãæ¡£
Ò¶×ÓÒ³ÃæÖÐÔò°üÀ¨Ã¿¸öË÷ÒýÌõÄ¿µÄË÷ÒýÖµºÍ¸ÃË÷ÒýÖ¸ÏòµÄ¼Ç¼µÄλÖã¨ÎļþºÅ + Ò³Ãæ + ²å²ÛºÅ£©£¬ÒòΪÕâÊÇΨһË÷ÒýËùÒÔ²»ÐèÒª¶îÍâµÄ×Ö¶ÎÀ´¼Ç¼Öظ´Öµ¡£
¶Ñ±íÉϵķÇΨһ·Ç¾Û¼¯Ë÷Òý
ÎÒÃÇ¿ÉÒÔ¿´µ½ page(1:94) Ϊ¸Ã·ÇΨһ·Ç¾Û¼¯Ë÷ÒýµÄ¸ù½Úµã£¬ÒòΪÊý¾ÝÁ¿ÉÙʵ¼ÊÉÏÖ»ÓÐÒ»¸öË÷Òý½Úµã£¬¼´´óÓÚµÈÓÚ G1 µÄÖ¸ÏòÒ¶×ÓÒ³Ãæ page(1:78) £¬Ð¡ÓÚ G1 µÄÔòÖ¸ÏòÒ¶×ÓÒ³Ãæ page(1:109) Ò³Ãæ¡£
Ò¶×ÓÒ³ÃæÖÐÔò°üÀ¨Ã¿¸öË÷ÒýÌõÄ¿µÄË÷ÒýÖµºÍ¸ÃË÷ÒýÖ¸ÏòµÄ¼Ç¼µÄλÖã¨ÎļþºÅ + Ò³Ãæ + ²å²ÛºÅ£©£»´æ´¢½á¹¹Óë¶Ñ±íÉϵÄΨһ·Ç¾Û¼¯Ë÷ÒýÍêÈ«Ò»Ö£¬ÎÒÃÇ¿ÉÒÔ¿´³öÔڶѱíÖо¡¹ÜË÷ÒýÖµ²»Î¨Ò»£¬µ«Í¨¹ýË÷ÒýÖµ + Ö¸Õë ( ÎļþºÅ + Ò³Ãæ + ²å²ÛºÅ ) µÄ·½Ê½£¬Ò²Äܹ»±£Ö¤¸ÃË÷ÒýÌõÄ¿µÄΨһÐÔ£¬ËùÒÔ²»ÐèÒªÔö¼Ó¶îÍâµÄ¸¨Öú×ֶΡ£
Ψһ¾Û¼¯Ë÷Òý±í
CREATE TABLE testUniqueClusterIndex
(
name CHAR ( 200),
type1 CHAR ( 900),
type2 CHAR ( 900)
)
CREATE UNIQUE CLUSTERED INDEX idx_testUniqueClusterIndex_cluster ON testUniqueClust