February 18, 2013 at 3:43 pm
For my own interest I have been looking at DBCC IND and DBCC Page.
Regarding a non clustered index page I can see the NC key data and the PK key data but not the INCLUDED column data.
No issues, I was just curious as to where this data is stored and if I should be seeing this data from the DBCC Page command.
I am using this so far.
DBCC IND ('test_db', 'test_table',10); -- 10 is the index id of the non clustered index with included columns
DBCC TRACEON(3604)
DBCC PAGE('test_db',1,1022685,3)
thanks
February 18, 2013 at 3:52 pm
Include columns are stored in the pages at the leaf level of the nonclustered index. Hence if you are looking at a leaf level page of an index that has an include column, DBCC Page will show the included data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2013 at 4:43 pm
Thanks for the reply.
For some reason SP_HELPINDEX2 was showing me the index had included fields when in fact the index did not, hence the reason I could not see the data with DBCC Page.
I re-tested on an index that definitely has included fields and as you say I can see the data no problem.
I dropped and re-created the index and the issue went away so maybe I need to see if I have an older version of SP_HELPINDEX2!
Out of interest, is it possible to see what data page (PageID) holds a particular bit of data?
For example I am not sure if I can find out that the NC key OrderID 1234 resides on PageID 4567 so I can view it with DBCC Page?
No issues just having a play.
February 19, 2013 at 1:43 am
Not directly. You could navigate the index levels like SQL does when looking for data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply