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 only takes a minute to sign up.
Sign up to join this community
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:
I am more interested in the located on PRIMARY part, What does it mean?
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.
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.
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.
