Identity Column and Index

Does identity column need to be indexed? For a very large table, if we do
not need the identity column to do selection, and the only purpose of the
identity column is be part of the primary key (clustered), do we need an
extra index for the identity column only? We are using sql server 2005.
Thanks,
Lijun
If there is already a primary key on the identity, then you don't need
another index. The PK automatically creates one.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
news:OyhEUeAUIHA.1204@.TK2MSFTNGP03.phx.gbl...
Does identity column need to be indexed? For a very large table, if we do
not need the identity column to do selection, and the only purpose of the
identity column is be part of the primary key (clustered), do we need an
extra index for the identity column only? We are using sql server 2005.
Thanks,
Lijun
|||Lijun
Determining whether or not a column needs an index does not depend on
whether the column has the identity property or not.
As you know, if the identity column is part of the primary key, it will be
part of an index. Keep in mind that there is nothing about the identity
property
that enforces uniqueness. If you need the identity column itself to be
unique, you should consider a unique constraint on that column.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Lijun Zhang" <sonyzhang00@.yahoo.com> wrote in message
news:OyhEUeAUIHA.1204@.TK2MSFTNGP03.phx.gbl...
> Does identity column need to be indexed? For a very large table, if we do
> not need the identity column to do selection, and the only purpose of the
> identity column is be part of the primary key (clustered), do we need an
> extra index for the identity column only? We are using sql server 2005.
> Thanks,
> Lijun
>