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,
LijunLijun
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
>|||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|||The identity mechanism itself does not require an index. The column will
"know" what value to assign next.
So if there is no other reason to create the index (used in WHERE
clause, used in JOINs, etc.), then it is best to not waste an index on
the identity column.
--
Gert-Jan
Lijun Zhang wrote:
> 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