In SQL Server, should I create an index for an identity column, or is it created automatically?

sql-server indexing identity
willvv picture willvv · Dec 6, 2010 · Viewed 17.3k times · Source

I believe when I create an identity column it gets indexed automatically, but I'm not 100% sure.

Should I create an index for an identity column, or is it created automatically?

Answer

Sam Saffron picture Sam Saffron · Dec 6, 2010 
create table test (Id int identity)
go
sp_help test

The object 'test' does not have any indexes, or you do not have permissions.

No constraints are defined on object 'test', or you do not have permissions.

As a general practice you would create a unique index on your identity column, this speeds up lookups.

Usually you would like your identity columns to be 'clustered indexes' as well (Id int identity primary key is the shortcut notation), meaning table is layed out on disk in the same order your identity column is. This optimizes for inserts, as the page being inserted into tends to be in memory. In some cases, when you are doing ranged lookups very frequently on other data in the table, you may consider clustering other columns instead, as SQL Server only allows you one clustered index per table.

Related questions

What do Clustered and Non clustered index actually mean?

I have a limited exposure to DB and have only used DB as an application programmer. I want to know about Clustered and Non clustered indexes. I googled and what I found was : A clustered index is a special type …

Read More
List of all index & index columns in SQL Server DB

How do I get a list of all index & index columns in SQL Server 2005+? The closest I could get is: select s.name, t.name, i.name, c.name from sys.tables t inner join sys.schemas s on …

Read More
Creating an index on a table variable

Can you create an index on a table variable in SQL Server 2000? i.e. DECLARE @TEMPTABLE TABLE ( [ID] [int] NOT NULL PRIMARY KEY ,[Name] [nvarchar] (255) COLLATE DATABASE_DEFAULT NULL ) Can I create an index on Name?

Read More