Can someone explain where non - clustered index is stored?
-
I am learning non-clustered and clustered indexing. When reading this article https://www.sqlshack.com/what-is-the-difference-between-clustered-and-non-clustered-indexes-in-sql-server/#:~:text=Clustered%20indexes%20only%20sort%20tables,involve%20any%20extra%20lookup%20step.
came across this line by the author
Non-clustered indexes are stored in a separate place from the actual table claiming more storage space.
Question is, where is this table? I am looking at my Azure Data Studio and am scratching my head looking into Indexes folder to see if I can find such table as described in the article.
Where is this table in SSMS or Azure Data Studio?- This topic was modified 1 year, 12 months ago by yourjobismine.
-
Clustered and non-clustered indexes are all stored in the data file (MDF/NDF files). What you may not know is that the Leaf Level of the Clustered Index IS the actual data (except for LOBs or Row Overflow data if they get large enough).
A non-clustered index contains a copy of some of that data in the form of index keys and the Leaf Level of indexes is a copy of some data even if it doesn't contain an INCLUDE). That's what the author meant by a "separate place". Each non-clustered index has it's own pages separate from the Clustered Index.
Non-Clustered indexes also contain the index key(s) that point to where the actual data is in the Clustered Index (or HEAP, if there isn't a Clustered Index).
With the advent of "INCLUDE" a long time ago, you can actually make a "covering non-clustered index" that has all the data in it that you might need to "cover" one or more indexes. Although it's not the "actual" data, it is a maintained copy of the data the same as what the clustered index has. Since it's usually much more narrow that the full rows stored in the Clustered Index, it can be quite fast. Just remember that it IS a duplication of data.
As for a "folder", there isn't one. The data is stored in the MDF/NDF files. In SSMS (I know nothing of "Azure" IDE's), you can press the {f8} key and the "Object Explorer" will appear as if the data were stored in "folders". I believe that Azure Studio will do the same... I just don't know if the {f8} key will open that or not. At the very least, there should be a menu entry that you can click through to get there... probably under a top level menu item label "View" or some such.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
ADS has an explorer window. You can see the objects and their properties there.
Jeff has laid out most of the interesting bits of storage of nonclustered indexes. Here's a bit more. Between the two, you should have a bunch of what you need.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Where is this table in SSMS or Azure Data Studio?Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply