By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,247 Members | 1,517 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,247 IT Pros & Developers. It's quick & easy.

Unique constraint index

P: n/a
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

Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
--
Jul 20 '05 #2

P: n/a
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

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.