Saturday, January 19, 2013

How to Get pageId for Each Record

This many not be something you can use in your application. This will be helpful during demonstrations of SQL Serve internals.

This sample code will tells you which row is in which page. Highlighted section has undocumented function.

  1: USE tempdb
  2: GO
  3: 
  4: CREATE table tblLoc
  5: (ID BIGINT IDENTITY,
  6: fld1 CHAR(1000) DEFAULT 'ABC',
  7: DATE1 Date DEFAULT GETDATE()
  8: )
  9: 
 10: INSERT INTO tblLOC
 11: 
 12: VALUES (DEFAULT,DEFAULT)
 13: GO 10000
 14: 
 15: SELECT * FROM tblLOC T
 16: CROSS APPLY fn_PhysLocCracker  (%%PHYSLOC%%) pl
 17: 

Result of this is,


image


SlotId is the record id of the page.

No comments:

Post a Comment