September 10, 2008 at 11:19 am
Hi,
There are many sites explaining the difference between unique constraint and unique index. In fact they are saying it is same.
But I think it is not same. I have found some differences as explained below...
--Create Table
CREATE TABLE Test
(
idTest INT
,TestName VARCHAR(200)
,TestDescription VARCHAR(MAX)
)
CREATE UNIQUE CLUSTERED INDEX idx_Test ON Test (idTest ASC)
--Observe the result from following query
sp_help 'Test'
Now let's drop index and create constraint
DROP INDEX idx_Test ON Test
ALTER TABLE Test ADD CONSTRAINT idx_Test UNIQUE CLUSTERED (idTest ASC)
--Observe the result again from following query
sp_help 'Test'
The constraint key is created additionaly. Now I'm not sure which method is preferred?
Refer MSDN Link as well http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=191117&SiteID=1
Regards - JL
September 10, 2008 at 11:24 am
The difference is that the constraint is Ainsi standard and makes more portable code.
As a personal prefference I also like to see the constraints straight in the table DDL instead of below it in the indexes.
Other than that, it makes absolutely no difference.
September 10, 2008 at 12:07 pm
just my 2ct.
You could also consider a constraint to be meta data for your database. This way it needs to be part of your system.
Having it as a constraint will also generate an index for you. (unlike FK constraints)
Having it defined just as an index might have your dba - in a minor moment - trow it away or modify it (add / remove columns) or just consider it as a "later added on feature", not critical to your system.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 10, 2008 at 12:28 pm
Good points, I've always be the only DBA in the environement... with the rule don't touch the freaking DB.
The only exceptions is that 2 power users can create views to find specific info when the search tools provided don't work for them... it's not ideal but it worked so far (yes I have active working DB backups :))... never had to use them in 4 years.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply