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.

    nonclusterd Where is this table in SSMS or Azure Data Studio?
  • 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.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    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 Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 3 posts - 1 through 2 (of 2 total)

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