Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Is Unique:Yes/No or Type:UNIQUE Key

Author  Topic 

mindtrap
Starting Member

10 Posts

Posted - 2011-07-10 : 17:53:07

SQL server editor gives an interface for creating UNIQUE Key indexes but something makes me confuse in index creation dialog it say is unique? yes/no type question and under that let me choose unique key or index.
So if its unique why do I want it to create as index instead of unique key and if its a unique key why do i want to select is unique attribute as No. Both of them can be perform in interface and I did not understand the difference between is unique and Unique key.

Thanks for comments and sorry for my pidgeon english try to explain the best way i can. All comments will be appreciated.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-10 : 18:23:16
What version of SQL Server are you using? When I try this in SQL 2008, the dialog presented lets me:

1. Select a name for the index
2. Select whether the index is clustered or non-clustered,
3. Select whether unique or non-unique,
4. Select the index key columns.

May be I didn't quite understand what you are asking...
Go to Top of Page

mindtrap
Starting Member

10 Posts

Posted - 2011-07-10 : 18:54:33
Ok let me explain when I open table in design view in table design toolbar there is a button "manage index and keys" after clicked it and push add button for adding a new index to the table.
It gives some options those listed under general tab including Is Unique and Type questions
Is unique can only be answered by selecting Yes or No and Type can be indicate as Index or Unique Key
The thing make me confuse whats the difference in selecting
Is Unique:Yes
Type: Index
AND
Is Unique:Yes
Type:Unique Key
Feel like both of those options serving same purpose
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-07-10 : 19:13:21
Yup, they are.

A unique constraint is enforced (in all the current versions) with a unique index. There are some subtle differences around naming, and on SQL 2008 you can filter a unique index but not a unique constraint.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-10 : 19:19:01
Documentation here seems to say so. In sys.indexes, both look the same except, is_unique_constraint is true for one and false for the other.
Go to Top of Page

mindtrap
Starting Member

10 Posts

Posted - 2011-07-10 : 19:58:31
Thanks both of you for spending your valuable time now understood the difference.
Go to Top of Page
   

- Advertisement -