Default Index on Primary Key

user_230780 0
Viewed 23999

Does SQL Server build an index on primary keys by default? If yes what kind of index? If no what kind of index is appropriate for selections by primary key?

I use SQL Server 2008 R2

Thank you.

primary-key indexing sql-server
6 Answers

To be direct, SQL does create an index on the PRIMARY KEY (PK) keyword. That index is a Unique, Clustered Index.

sqlvogel brings up an important point in his commoent above. You can only have one "CLUSTERED" index. If you already have one prior to declaring a PK then your key will be NONCLUSTERED. This is a little more detail than the default answer to this question. It should also be noted that PK's can not have NULL values.

Note, however that that index can vary depending on prior constraints or index on the table. Additionally, you can declare the details of this index upon creation depending on how you write out the code:

< table_constraint > ::= [ CONSTRAINT constraint_name ] 
{ [ { PRIMARY KEY | UNIQUE } 
    [ CLUSTERED | NONCLUSTERED ] 
    { ( column [ ASC | DESC ] [ ,...n ] ) } 
    [ WITH FILLFACTOR = fillfactor ] 
    [ ON { filegroup | DEFAULT } ] 
]

Example:

CREATE TABLE MyTable
(
Id INT NOT NULL,
ForeignKeyId INT REFERENCES OtherTable(Id) NOT NULL,
Name VARCHAR(50) NOT NULL,
Comments VARCHAR(500) NULL,

PRIMARY KEY NONCLUSTERED (Id, ForeignKeyId)
)
user_12472360
Related