Actually, you do not need a Clustered Index nor a Primary Key to be created, since Unique Indexes and Non-Unique Indexes can handle the work. SQL Server has supported a Clustered Index since at least version 1.1, but the Primary Key was just a "concept" that programmers enforced by defining a unique index.
But it seems that both Primary Keys and Clustered Indexes are valuable concepts in the majority of databases.
Let us look at the SQL Server documentation to see the partial descriptions of some indexing options as show below.
Clustered Index: https://msdn.microsoft.com/en-us/library/ms190457.aspx
- Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition.
- There can be only one clustered index per table
Primary Key: https://msdn.microsoft.com/en-us/library/ms190457.aspx
A table can contain only one PRIMARY KEY constraint.
All columns defined within a PRIMARY KEY constraint must be defined as NOT NULL.
The Primary Key can be created as a Clustered Index (the default if there is no Clustered Index) or a Non-Clustered Index.
Unique Index: https://msdn.microsoft.com/en-us/library/ms187019.aspx
When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default.
You can specify a UNIQUE Clustered Index if a Clustered Index does not already exist for the table.
This means that your question about Clustered Indexes and Primary Keys is really about some of the following issues. Please note that not every table benefits from the same indexing plan.
When would I benefit from the Primary Key being separate from the Clustered Index?
Perhaps when the Clustered Index is Wide (for example, 5 columns of textual information, but the Primary Key is small (INT or BIGINT), such as you seem to be describing.
- A wide Clustered Index would allow you to quickly select rows from the index for a subset of queries that provide serial answers from the Clustered Index (also known as the Table). For example, a 5-column Clustered Index would support scanning the columns C1, C2, C3, C4, C5 or C1, C2, C3, C4 and so on down to C1.
- Note: If the rows were large, this might give you some speed benefits on selecting the serial set of rows, especially if other columns in the table are regularly included in the result set.
- In that case you can use the Primary Key for referential integrity in order to supply the needed value as a Foreign Key to constrain rows in other tables. The PK is small and is thus the FK is a small hit on the size of the referenced table(s).
- However, note that any index created on a table that has a Clustered Index will include all of the cluster columns in the other indexes you create on this table. A wide Clustered Index would expand the size of all of the non-clustered indexes on that table.
Should you make the Primary Key alone be the Clustered Index?
If you have a small Primary Key (INT or BIGINT) and it is the Clustered Index, the overhead of the cluster columns is relatively small. Although the Clustered Primary Key in this case will also exist in every index on this table, it is a smaller price to pay than the Wide Cluster discussed above.
This Primary Key Clustered Index will usually not directly offer an easy path to serially selecting many rows.
Now that you have created a Clustered Primary Key, what about those other columns you were once planning to include in the Clustered Index?
Create a Unique (or a Non-Unique) index as needed to index that wide search criteria of columns C1, C2, C3, C4, C5. The values in this “Imitation Clustered” Index can serve as a faster search path for those 5 columns. If there is a non-indexed column or two that are regularly selected as well, they can be included in the index with
INCLUDE (Doctor_Name, Diagnosis_Synopsis).
Although I find simple Clustered Indexes and Primary Keys useful there are some good reasons for thinking through whether to use them in a table or in a database.
Do you need a Clustered Index at all?
If you create indexes (Unique Indexes and Non-Unique Indexes) and defining the Primary Key without the overhead of being a Clustered Index, you might find that the narrower indexes provide you what you need for your queries.
There are some useful behaviors in Clustered Indexes and Primary Keys, but remember that it is really the indexes that matter the most. Design the indexing strategy to take into account the realities of your application. Maybe the
OneBigTable needs to have a different indexing strategy from what you use for most of the tables.
Without a Clustered Index your data will be stored as a heap with the Row Identifier (RID) which is not a good search mechanism at all. But, as mentioned previously, you can create unique and non-unique indexes to handle your queries.
Which now takes you to considering Heaps:
Heaps and Indexes: https://msdn.microsoft.com/en-us/library/hh213609.aspx
- When a table is stored as a heap, individual rows are identified by reference to a row identifier (RID) consisting of the file number, data page number, and slot on the page. The row id is a small and efficient structure. (But it is not an index.)
- Sometimes data architects use heaps when data is always accessed through nonclustered indexes and the RID is smaller than a clustered index key.
But if you also have some 'hot spots' in a big data set, you can also look into another type of index:
Filtered Index: https://msdn.microsoft.com/en-us/library/cc280372.aspx
A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.
Filtered indexes have a number of restrictions which are outlined in the link to filtered indexes.
However, if you are interested in thinking about that possibility of skipping Primary Keys and Clustered Indexes altogether, you might read Markus Winand's post linked below. He demonstrates his reasons, with some code samples, to suggest that it might be a good idea at times to forego using those features.
But it all finally comes back to understanding your application and designing the code, the tables, the indexes, and so forth to fit the job you are doing.