34,593
社区成员
发帖
与我相关
我的任务
分享
USE MASTER
GO
CREATE TABLE sp_table_pages
(
PagefID tinyint,
PagePID int,
IAMFID tinyint,
IAMPID int,
ObjectID int,
IndexID tinyint,
PartitionNumber tinyint,
PartitionID bigint,
iam_chain_type varchar(30),
PageType tinyint,
IndexLevel tinyint,
NextPageFID tinyint,
NextPagePID int,
PrevPageFID tinyint,
PrevPagePID int,
Primary Key (PageFID,PagePID)
)
TRUNCATE TABLE sp_table_pages
INSERT INTO sp_table_pages EXEC ('dbcc ind (AdventureWorks,[Sales.SalesOrderDetail],-1)')
SELECT * FROM sp_table_pages
CREATE TABLE Clustered_Dupes
(
Col1 char(5) NOT NULL,
Col2 int NOT NULL,
Col3 CHAR(3) NULL,
Col4 CHAR(6) NOT NULL
)
CREATE CLUSTERED INDEX Cl_dupes_col1 ON Clustered_Dupes(col1)
GO
SELECT first,indid,keycnt,name FROM sysindexes
WHERE id = object_id('Clustered_Dupes')
--查找索引中的第一个数据分页
INSERT INTO Clustered_Dupes VALUES ('ABCDE',123,NULL,'CCCC')
GO
TRUNCATE TABLE sp_table_pages
GO
INSERT INTO sp_table_pages EXEC ('DBCC IND(AdventureWorks,Clustered_Dupes,-1)')
GO
SELECT PageFID,PagePID
FROM sp_table_pages
WHERE PageType = 1 and PrevPageFID = 0 and PrevPagePID = 0
GO
--查看分页内容
--开启跟踪标记
DBCC TRACEON(3604)
GO
DBCC PAGE (AdventureWorks,1,20767,1)
/*
Row - Offset
0 (0x0) - 96 (0x60)
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。
*/
INSERT INTO Clustered_Dupes VALUES ('ABCDE',456,null,'DDDD')
INSERT INTO Clustered_Dupes VALUES ('ABCDE',64,null,'EEEE')
--查看数据页面内容
DBCC PAGE(AdventureWorks,1,20767,1)
/*
OFFSET TABLE:
Row - Offset
2 (0x2) - 154 (0x9a)
1 (0x1) - 121 (0x79)
0 (0x0) - 96 (0x60)
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。
*/
--显示聚集索引结点行的结构
CREATE TABLE Clustered_nodupes
(
id int NOT NULL,
str1 char(5) NOT NULL,
str2 char(600) NULL
)
GO
CREATE CLUSTERED INDEX idxCL ON Clustered_Nodupes (str1)
GO
SET NOCOUNT ON
GO
DECLARE @i int
SET @i = 1024
WHILE @i < 13000
BEGIN
INSERT INTO Clustered_Nodupes
SELECT @i,CAST(@i AS CHAR),CAST(@i AS CHAR)
SET @i = @i + 1
END
GO
TRUNCATE TABLE sp_table_pages
INSERT INTO sp_table_pages EXEC ('DBCC IND (AdventureWorks,Clustered_Nodupes,-1)')
SELECT PageFID,PagePID,IndexLevel,PageType
FROM sp_table_pages
WHERE IndexId = 1 and IndexLevel >= 0 and PrevPageFId = 0 and PrevPagePID = 0
--结果
/*
PageFID PagePID IndexLevel PageType
1 20970 0 1
1 20972 1 2
1 21554 2 2
*/
--查看数据页内容
DBCC PAGE (AdventureWorks,1,20970,1)
.........
Row - Offset
79 (0x4f) - 144 (0x90)
78 (0x4e) - 4008 (0xfa8)
77 (0x4d) - 4020 (0xfb4)
76 (0x4c) - 3996 (0xf9c)
75 (0x4b) - 3984 (0xf90)
74 (0x4a) - 3972 (0xf84)
73 (0x49) - 3960 (0xf78)
72 (0x48) - 3948 (0xf6c)
71 (0x47) - 3864 (0xf18)
70 (0x46) - 3924 (0xf54)
69 (0x45) - 3936 (0xf60)
68 (0x44) - 3912 (0xf48)
67 (0x43) - 3900 (0xf3c)
66 (0x42) - 3888 (0xf30)
65 (0x41) - 3876 (0xf24)
64 (0x40) - 132 (0x84)
63 (0x3f) - 3840 (0xf00)
62 (0x3e) - 3852 (0xf0c)
61 (0x3d) - 3828 (0xef4)
60 (0x3c) - 3816 (0xee8)
59 (0x3b) - 3804 (0xedc)
58 (0x3a) - 3792 (0xed0)
57 (0x39) - 3780 (0xec4)
56 (0x38) - 3696 (0xe70)
55 (0x37) - 3756 (0xeac)
54 (0x36) - 3768 (0xeb8)
53 (0x35) - 3744 (0xea0)
52 (0x34) - 3732 (0xe94)
51 (0x33) - 3720 (0xe88)
50 (0x32) - 3708 (0xe7c)
49 (0x31) - 120 (0x78)
48 (0x30) - 3672 (0xe58)
47 (0x2f) - 3684 (0xe64)
46 (0x2e) - 3660 (0xe4c)
45 (0x2d) - 3648 (0xe40)
44 (0x2c) - 3636 (0xe34)
43 (0x2b) - 3624 (0xe28)
42 (0x2a) - 3612 (0xe1c)
41 (0x29) - 3528 (0xdc8)
40 (0x28) - 3588 (0xe04)
39 (0x27) - 3600 (0xe10)
38 (0x26) - 3576 (0xdf8)
37 (0x25) - 3564 (0xdec)
36 (0x24) - 3552 (0xde0)
35 (0x23) - 3540 (0xdd4)
34 (0x22) - 108 (0x6c)
33 (0x21) - 3504 (0xdb0)
32 (0x20) - 3516 (0xdbc)
31 (0x1f) - 3492 (0xda4)
30 (0x1e) - 3480 (0xd98)
29 (0x1d) - 3468 (0xd8c)
28 (0x1c) - 3456 (0xd80)
27 (0x1b) - 3444 (0xd74)
26 (0x1a) - 3132 (0xc3c)
25 (0x19) - 3420 (0xd5c)
24 (0x18) - 3432 (0xd68)
23 (0x17) - 3408 (0xd50)
22 (0x16) - 3396 (0xd44)
21 (0x15) - 3384 (0xd38)
20 (0x14) - 3372 (0xd2c)
19 (0x13) - 3144 (0xc48)
18 (0x12) - 3360 (0xd20)
17 (0x11) - 3348 (0xd14)
16 (0x10) - 3336 (0xd08)
15 (0xf) - 3324 (0xcfc)
14 (0xe) - 3312 (0xcf0)
13 (0xd) - 3300 (0xce4)
12 (0xc) - 3288 (0xcd8)
11 (0xb) - 3276 (0xccc)
10 (0xa) - 3264 (0xcc0)
9 (0x9) - 3252 (0xcb4)
8 (0x8) - 3240 (0xca8)
7 (0x7) - 3228 (0xc9c)
6 (0x6) - 3216 (0xc90)
5 (0x5) - 3204 (0xc84)
4 (0x4) - 3192 (0xc78)
3 (0x3) - 3180 (0xc6c)
2 (0x2) - 3168 (0xc60)
1 (0x1) - 3156 (0xc54)
0 (0x0) - 96 (0x60)
/*