Sql-server – Meaning of “nonclustered located on primary”

clustered-indexindexnonclustered-indexsql server

When I run sp_help (using ALT+F1) for a table, on the indexes, there is a column index_description. and the rows have for example:

  • nonclustered located on PRIMARY (on foreign keys)
  • nonclustered, hypothetical located on PRIMARY (on non clustered indexes I created)
  • clustered, unique, primary key located on PRIMARY

I am more interested in the located on PRIMARY part, What does it mean?

Best Answer

This is the name of the filegroup or partition scheme that the index is created on. This can be specified when creating an index with a second ON clause.

The sp_help procedure calls sp_helpindex which retrieves the name from sys.data_spaces

The primary filegroup contains the primary data file and any other files not specifically assigned to another filegroup. All pages for the system tables are allocated in the primary filegroup.

More info about Files and Filegroups here

Related Question