[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
>
>