SHARE
TWEET

T-SQL script to test Uniquifier size

SqlQuantumLeap Apr 21st, 2016 (edited) 26 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SET NOCOUNT ON;
  2.  
  3. -- DROP TABLE #NonUnique;
  4. CREATE TABLE #NonUnique (Col1 INT NOT NULL, Col2 NVARCHAR(10) NULL);
  5. INSERT INTO #NonUnique (Col1, Col2) VALUES (33, N'A'); -- 0x21
  6. INSERT INTO #NonUnique (Col1, Col2) VALUES (33, N'B'); -- 0x21
  7. INSERT INTO #NonUnique (Col1, Col2) VALUES (33, N'C'); -- 0x21
  8. INSERT INTO #NonUnique (Col1, Col2) VALUES (34, N'D'); -- 0x22
  9. GO
  10. CREATE CLUSTERED INDEX #NU ON #NonUnique (Col1);
  11. GO
  12. CREATE NONCLUSTERED INDEX #NUnc ON #NonUnique (Col2) WITH (FILLFACTOR = 100);
  13. GO
  14.  
  15. DECLARE @DatabaseID INT = DB_ID(N'tempdb'),
  16.         @ObjectID INT = OBJECT_ID(N'tempdb..#NonUnique'),
  17.         @FileID INT,
  18.         @ClusteredIndexPageID INT,
  19.         @NonClusteredIndexPageID INT;
  20.  
  21. SELECT  @FileID = dpa.allocated_page_file_id,
  22.         @ClusteredIndexPageID = CASE WHEN dpa.index_id = 1 THEN dpa.allocated_page_page_id ELSE @ClusteredIndexPageID END,
  23.         @NonClusteredIndexPageID = CASE WHEN dpa.index_id = 2 THEN dpa.allocated_page_page_id ELSE @NonClusteredIndexPageID END
  24. FROM sys.dm_db_database_page_allocations(@DatabaseID, @ObjectID, NULL, NULL, 'DETAILED') dpa
  25. WHERE is_iam_page = 0
  26. --AND   [page_type] = 1; -- DATA_PAGE
  27.  
  28. SELECT @DatabaseID AS [DatabaseID],
  29.        @ObjectID AS [ObjectID],
  30.        @FileID AS [FileID],
  31.        @ClusteredIndexPageID AS [ClusteredIndexPageID],
  32.        @NonClusteredIndexPageID AS [NonClusteredIndexPageID];
  33.  
  34. /* CHECK THE CLUSTERED INDEX */
  35. DBCC PAGE (@DatabaseID, @FileID, @ClusteredIndexPageID, 3) WITH TABLERESULTS;
  36. ----- Row #1: Col1 = 33 (unique); Uniquifier = 0 bytes; Total bytes for row = 19
  37. -- Slot 0 Offset 0x60 Length 19 Slot 0 Column 0 Offset 0x0 Length 4 Length (physical) 0 UNIQUIFIER  0
  38. -- Slot 0 Offset 0x60 Length 19 Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 Col1    33
  39.  
  40. ----- Row #2: Col1 = 33 (1st duplicate); Uniquifier = 4 bytes; Total bytes for row = 23
  41. -- Slot 1 Offset 0x73 Length 23 Slot 1 Column 0 Offset 0x11 Length 4 Length (physical) 4    UNIQUIFIER  1
  42. -- Slot 1 Offset 0x73 Length 23 Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4 Col1    33
  43.  
  44. ----- Row #3: Col1 = 33 (2nd duplicate); Uniquifier = 4 bytes; Total bytes for row = 23
  45. -- Slot 2 Offset 0x8a Length 23 Slot 2 Column 0 Offset 0x11 Length 4 Length (physical) 4    UNIQUIFIER  2
  46. -- Slot 2 Offset 0x8a Length 23 Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4 Col1    33
  47.  
  48. ----- Row #4: Col1 = 34 (unique); Uniquifier = 0 bytes; Total bytes for row = 19
  49. -- Slot 3 Offset 0xa1 Length 19 Slot 3 Column 0 Offset 0x0 Length 4 Length (physical) 0 UNIQUIFIER  0
  50. -- Slot 3 Offset 0xa1 Length 19 Slot 3 Column 1 Offset 0x4 Length 4 Length (physical) 4 Col1    34
  51.  
  52.  
  53. /* CHECK THE NON-CLUSTERED INDEX */
  54. DBCC PAGE (@DatabaseID, @FileID, @NonClusteredIndexPageID, 3) WITH TABLERESULTS;
  55. -- Result Set #2 (easier to see this particular info) is shown below.
  56. -- "KeyHashValue" column has been removed.
  57. -- Notice rows 0 and 3, the "unique" rows for Col1, are 14 bytes while the other two rows are 20 bytes each.
  58.  
  59. /*
  60. FileId    PageId    Row  Level     Col2 (key)     Col1 (key)     UNIQUIFIER (key)    Row Size
  61. 1         312       0    0         A              33             0                   14
  62. 1         312       1    0         B              33             1                   20
  63. 1         312       2    0         C              33             2                   20
  64. 1         312       3    0         D              34             0                   14
  65. */
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top