Unique constraint index

[Mansoor Azam]

When I add a unique key constraint to column in SQL 6.5 why does it also
create an index. e.g. In the table subaccounts I added a unique key
constraint for the column login and SQL creates an index with the name
UQ_SubAccounts_2__19 (UKC).

Does this also mean that there is no need to create an index for this
column?

thx

Mansoor

[David Portas]

An index is created automatically for a unique constraint. The index is
necessary to allow SQLServer to verify uniqueness (otherwise a table-scan
would be needed for each update of the table). There is no need to create
another index on the same column(s) that already have a unique constraint.

--
David Portas
SQL Server MVP
--

[Dan Guzman]

To expand on David's response, SQL Server creates unique indexes to support
PRIMARY KEY and UNIQUE constraints. These can be created as
clustered/non-clustered like regular indexes and SQL Server can use these
for query optimization. UNIQUE constraints may be used instead of unique
indexes. Personally, I always use UNIQUE constraints instead of unique
indexes because uniqueness is a data characteristic.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Mansoor Azam" <ma******@shoa.net> wrote in message
news:c6************@ID-31123.news.uni-berlin.de...

When I add a unique key constraint to column in SQL 6.5 why does it also
create an index. e.g. In the table subaccounts I added a unique key
constraint for the column login and SQL creates an index with the name
UQ_SubAccounts_2__19 (UKC).

Does this also mean that there is no need to create an index for this
column?

thx

Mansoor