[dba-SQLServer] Unique indexes vs. constraints

Yup, and if you need both then you have a choice of having unique index
or an index and a constraint, or a primary non-clustered index (all
three would yield the same result)



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 12:31 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Unique indexes vs. constraints

So if I didn't need an index, but I did need uniqueness, I would use a
constraint to save on the overhead.  If I wanted to help, say, sorting
on
the unique field, I would use an index.

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:56 AM
Subject: RE: [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
>
> _______________________________________________
> 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