Default Index on Primary Key

Question

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.

Answer 1

You can easily determine the first part of this for yourself

create table x
(
id int primary key
)

select * from sys.indexes where object_id = object_id('x')

Gives

object_id   name             index_id    type type_desc                                                    is_unique data_space_id ignore_dup_key is_primary_key is_unique_constraint fill_factor is_padded is_disabled is_hypothetical allow_row_locks allow_page_locks
1653580929  PK__x__6383C8BA    1           1    CLUSTERED                                                    1         1             0              1              0                    0           0         0           0               1               1

Edit: There is one other case I should have mentioned

create table t2 (id int not null, cx int)

create clustered index ixc on dbo.t2 (cx asc)

alter table dbo.t2 add constraint pk_t2 primary key (id) 

select * from sys.indexes where object_id = object_id('t2')

Gives

object_id   name                           index_id    type type_desc                      is_unique data_space_id ignore_dup_key is_primary_key is_unique_constraint fill_factor is_padded is_disabled is_hypothetical allow_row_locks allow_page_locks has_filter filter_definition
----------- ------------------------------ ----------- ---- ------------------------------ --------- ------------- -------------- -------------- -------------------- ----------- --------- ----------- --------------- --------------- ---------------- ---------- ------------------------------
34099162    ixc                            1           1    CLUSTERED                      0         1             0              0              0                    0           0         0           0               1               1                0          NULL
34099162    pk_t2                          2           2    NONCLUSTERED                   1         1             0              1              0                    0           0         0           0               1               1                0          NULL

With regard to the second part there is no golden rule it depends on your individual query workload, and what your PK is.

For satisfying individual lookups by primary key a non clustered index will be fine. If you are doing queries against ranges these would be well served by a matching clustered index but a covering non clustered index could also suffice.

You also need to consider the index width of the clustered index in particular as it impacts all your non clustered indexes and effect of inserts on page splits.

I recommend the book SQL Server 2008 Query Performance Tuning Distilled to read more about the issues.

Answer 2

Yes. By default a unique clustered index is created on all primary keys, but you can create a unique nonclustered index instead of you like.

As to what the appropriate choice, I'd say that for 80-90% of the tables you create, you generally want the clustered index to be the primary key, but that's not always the case.

You'd typically make the clustered index something else if you do heavy range scans on that "something else". For example, if you have a synthetic primary key*, but have a date column that you typically query in terms of a range, you'd often want that date column to be the most significant column in your clustered index.

*That's usually done by using an INT IDENTITY column as the PK on the table.