[dba-SQLServer] Unique indexes vs. constraints

UNIQUE INDEX is an index overloaded with characteristics of a unique
constraint.  I guess the true difference is that the presence of unique
constraint does not affect the performance, while the presence of an
index does, regardless of whether it's unique or not.



Robert Djabarov
SQL Server & UDB
Sr. SQL Server Administrator
Phone: (210)  913-3148
Pager: (210) 753-3148
9800 Fredericksburg Rd. San Antonio, TX  78288
www.usaa.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mark
Whittinghill
Sent: Monday, March 08, 2004 11:45 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Unique indexes vs. constraints

To clarify,

Is there any difference between
ALTER TABLE MyTable
ADDCONSTRAINT MyConstraint UNIQUE (MyField)

and

CREATE UNIQUE INDEX MyIndex
   ON MyTable(MyField)

or do these both produce unique nonclustered indexes?  I'm confused
because
in Access, you just set an index.  In table design view in Enterprise
Manager, you have the choice of setting an index or a constraint, but if
you
right click the table in EM and choose manage indexes then examine the
SQL,
it looks the same between an index and a constraint.

Mark Whittinghill
Symphony Information Services
763-391-7400
mwhittinghill at symphonyinfo.com
----- Original Message ----- 
From: "Djabarov, Robert" <Robert.Djabarov at usaa.com>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Monday, March 08, 2004 11:05 AM
Subject: RE: [dba-SQLServer] Unique indexes vs. constraints


> Hmmm, I always thought that there were only 2 types of indexes, -
> clustered and non-clustered.  Primary Key constraint also ensures
> uniqueness, just like unique constraint.
>
>
>
> Robert Djabarov
> SQL Server & UDB
> Sr. SQL Server Administrator
> Phone: (210)  913-3148
> Pager: (210) 753-3148
> 9800 Fredericksburg Rd. San Antonio, TX  78288
> www.usaa.com
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mark
> Whittinghill
> Sent: Monday, March 08, 2004 9:46 AM
> To: dba-sqlserver at databaseadvisors.com
> Subject: [dba-SQLServer] Unique indexes vs. constraints
>
> What's the value of using a unique constraint vs. a unique index?  Is
it
> that you would use an index if you also wanted to sort or search on
that
> field, and a constraint if you just wanted to enforce uniqueness
without
> the
> overhead of an index?  Or is there more to it than that?
>
>
> Mark Whittinghill
> Symphony Information Services
> 763-391-7400
> mwhittinghill at symphonyinfo.com
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com