Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SET NOCOUNT ON;
- -- DROP TABLE #NonUnique;
- CREATE TABLE #NonUnique (Col1 INT NOT NULL, Col2 NVARCHAR(10) NULL);
- INSERT INTO #NonUnique (Col1, Col2) VALUES (33, N'A'); -- 0x21
- INSERT INTO #NonUnique (Col1, Col2) VALUES (33, N'B'); -- 0x21
- INSERT INTO #NonUnique (Col1, Col2) VALUES (33, N'C'); -- 0x21
- INSERT INTO #NonUnique (Col1, Col2) VALUES (34, N'D'); -- 0x22
- GO
- CREATE CLUSTERED INDEX #NU ON #NonUnique (Col1);
- GO
- CREATE NONCLUSTERED INDEX #NUnc ON #NonUnique (Col2) WITH (FILLFACTOR = 100);
- GO
- DECLARE @DatabaseID INT = DB_ID(N'tempdb'),
- @ObjectID INT = OBJECT_ID(N'tempdb..#NonUnique'),
- @FileID INT,
- @ClusteredIndexPageID INT,
- @NonClusteredIndexPageID INT;
- SELECT @FileID = dpa.allocated_page_file_id,
- @ClusteredIndexPageID = CASE WHEN dpa.index_id = 1 THEN dpa.allocated_page_page_id ELSE @ClusteredIndexPageID END,
- @NonClusteredIndexPageID = CASE WHEN dpa.index_id = 2 THEN dpa.allocated_page_page_id ELSE @NonClusteredIndexPageID END
- FROM sys.dm_db_database_page_allocations(@DatabaseID, @ObjectID, NULL, NULL, 'DETAILED') dpa
- WHERE is_iam_page = 0
- --AND [page_type] = 1; -- DATA_PAGE
- SELECT @DatabaseID AS [DatabaseID],
- @ObjectID AS [ObjectID],
- @FileID AS [FileID],
- @ClusteredIndexPageID AS [ClusteredIndexPageID],
- @NonClusteredIndexPageID AS [NonClusteredIndexPageID];
- /* CHECK THE CLUSTERED INDEX */
- DBCC PAGE (@DatabaseID, @FileID, @ClusteredIndexPageID, 3) WITH TABLERESULTS;
- ----- Row #1: Col1 = 33 (unique); Uniquifier = 0 bytes; Total bytes for row = 19
- -- Slot 0 Offset 0x60 Length 19 Slot 0 Column 0 Offset 0x0 Length 4 Length (physical) 0 UNIQUIFIER 0
- -- Slot 0 Offset 0x60 Length 19 Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 Col1 33
- ----- Row #2: Col1 = 33 (1st duplicate); Uniquifier = 4 bytes; Total bytes for row = 23
- -- Slot 1 Offset 0x73 Length 23 Slot 1 Column 0 Offset 0x11 Length 4 Length (physical) 4 UNIQUIFIER 1
- -- Slot 1 Offset 0x73 Length 23 Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4 Col1 33
- ----- Row #3: Col1 = 33 (2nd duplicate); Uniquifier = 4 bytes; Total bytes for row = 23
- -- Slot 2 Offset 0x8a Length 23 Slot 2 Column 0 Offset 0x11 Length 4 Length (physical) 4 UNIQUIFIER 2
- -- Slot 2 Offset 0x8a Length 23 Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4 Col1 33
- ----- Row #4: Col1 = 34 (unique); Uniquifier = 0 bytes; Total bytes for row = 19
- -- Slot 3 Offset 0xa1 Length 19 Slot 3 Column 0 Offset 0x0 Length 4 Length (physical) 0 UNIQUIFIER 0
- -- Slot 3 Offset 0xa1 Length 19 Slot 3 Column 1 Offset 0x4 Length 4 Length (physical) 4 Col1 34
- /* CHECK THE NON-CLUSTERED INDEX */
- DBCC PAGE (@DatabaseID, @FileID, @NonClusteredIndexPageID, 3) WITH TABLERESULTS;
- -- Result Set #2 (easier to see this particular info) is shown below.
- -- "KeyHashValue" column has been removed.
- -- Notice rows 0 and 3, the "unique" rows for Col1, are 14 bytes while the other two rows are 20 bytes each.
- /*
- FileId PageId Row Level Col2 (key) Col1 (key) UNIQUIFIER (key) Row Size
- 1 312 0 0 A 33 0 14
- 1 312 1 0 B 33 1 20
- 1 312 2 0 C 33 2 20
- 1 312 3 0 D 34 0 14
- */
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement