Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 7 down vote accepted

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

share|improve this answer

In SQL Server it’s possible to store data in more than one physical file on the file system.

This is typically done for larger databases where separate data files are stored on different drives or even on different physical machines.

ON PRIMARY means that table is stored in main database file.

Other options would be to create more file groups and explicitly assign these to different filegroups.

share|improve this answer

Additional info: by default a database is created with only a PRIMARY filegroup, and even if you add filegroups later, PRIMARY is the default for new objects unless you also change that setting (you can change the default, and a lot of people do) or override it explicitly when you create the object. So in a lot of cases the ON PRIMARY bit is extraneous and inconsequential.

The reason you might not want to use the PRIMARY filegroup for user objects is for things like piecemeal restore when recovering from a disaster - you can get the database online quicker if you restore the PRIMARY filegroup and it's as small as possible, then you can restore your actual user data filegroup by filegroup, with the most important coming online first. Another reason might be that the initial data file for the PRIMARY filegroup is on a slow(er) drive, and you want to expand the database by adding new files on fast(er) disks, well if you put those in their own filegroup and make that the default, all of your data goes to the fast(er) disks and only system/metadata stuff happens in the PRIMARY filegroup on the slow disk.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.