Search code examples
sql-serverindexingnon-clustered-index

Where does nonclustered index physically exists

I know that cluster index exist on the table itself, but where's the noncluster index and is pointer exist? I thought on "temp DB", but maybe i am wrong.

I did not find anything about it on the web.

Can you help me with it?

Solution

  • TempDb is a separate database, used by SQL (and maybe some of your stored procedures) to do intermediate work - hence the Temp in TempDb - so your indexes wouldn't be in there. Non clustered index pages go into the same filegroup as the clustered index pages by default - but you can specify which filegroup they go into on the index declaration. See, for example: Create Index.

    Also, if you want to ferret out where the data is, start with sys.indexes and related system views.