Is there a way to view what is actually in a non-clustered index?

Question

Is there a way to view what is actually in a non-clustered index?

jshahan

SSCarpal Tunnel

Points: 4622
More actions
September 20, 2012 at 7:31 am

#261826

My understanding is that if I run
SELECT * FROM <tablename>
on a table with a clustered index, the results and the clustered index are one and the same. In other words, I am viewing the clustered index.
I would like to know if I can actually view the contents of the non-clustered indexes on a table. So far I haven’t found a function or query to do so. Does any mechanism for this exist?
Thanks, all. 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There's no real way to see, because there's no reason you'd need to. A nonclustered index contains just the columns specified for the index, for every row in the table.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 2

Thanks, Gail. Coming from you, I can take it to the bank that there is no practical reason to need to see the contents of a non-clustered index therefore there is no method to do so.

For what it is worth, I was trying to find a way to make the non-clustered concept a bit less abstract for programmers that are new to SQL.

But in addition to the contents of the column(s), doesn't the index also contain a pointer to the page containing the records?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Index key columns, index include columns and either the clustered index key or a RID (depending what the base table is)

You could use something like DBCC Page to pull up a raw index page if you really wanted...

As for less abstract, I use the index at the back of a book (like a nice large SQL book) to demonstrate. The page numbers are the clustered index, the index at the back (by keyword) is a nonclustered index.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 4

I just did a little research into DBCC Page.

I think I'll stick with the book metaphor 🙂

GSquared SSC Guru Points: 260824 More actions · Answer 5

I've been using the book metaphore for over a decade, and it works so long as you actually have a book to show them with. I run into people all the time who aren't comfortable with glossaries and indexes. Not enough to visualize it mentally, anyway.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon