[dba-SQLServer] Unique indexes vs. constraints

> "SQL Server automatically creates a UNIQUE index to enforce the
> uniqueness requirement of the UNIQUE constraint. Therefore, if an
> attempt to insert a duplicate row is made, SQL Server returns an error
> message that says the UNIQUE constraint has been violated and does not
> add the row to the table. Unless a clustered index is explicitly
> specified, a unique, nonclustered index is created by default to enforce
> the UNIQUE constraint."

Ok, I can understand this.


> "Specifying a unique index makes sense only when uniqueness is a
> characteristic of the data itself. If uniqueness must be enforced to
> ensure data integrity, create a UNIQUE or PRIMARY KEY constraint on the
> column rather than a unique index. For example, if you plan to query
> frequently on the Social Security number (ssn) column in the employee
> table (in which the primary key is emp_id), and you want to ensure that
> Social Security numbers are unique, create a UNIQUE constraint on ssn.
> If the user enters the same Social Security number for more than one
> employee, an error is displayed."

But didn't they just say that the unique index is created automatically?
And what's the difference between "uniqueness is a characteristic of the
data itself" and "uniqueness must be enforced to ensure data integrity?"

Mark Whittinghill
Symphony Information Services
763-391-7400
mwhittinghill at symphonyinfo.com
----- Original Message ----- 
From: "Stoker, Kenneth E" <Kenneth.Stoker at pnl.gov>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Monday, March 08, 2004 12:20 PM
Subject: RE: [dba-SQLServer] Unique indexes vs. constraints


> In BOL, the following statement is made about unique constraints:
>
> "SQL Server automatically creates a UNIQUE index to enforce the
> uniqueness requirement of the UNIQUE constraint. Therefore, if an
> attempt to insert a duplicate row is made, SQL Server returns an error
> message that says the UNIQUE constraint has been violated and does not
> add the row to the table. Unless a clustered index is explicitly
> specified, a unique, nonclustered index is created by default to enforce
> the UNIQUE constraint."
>
> Now, under unique indexes, I am not sure why it make the following
> statement about using PRIMARY KEY or UNIQUE constraint instead of UNIQUE
> index:
>
> "Specifying a unique index makes sense only when uniqueness is a
> characteristic of the data itself. If uniqueness must be enforced to
> ensure data integrity, create a UNIQUE or PRIMARY KEY constraint on the
> column rather than a unique index. For example, if you plan to query
> frequently on the Social Security number (ssn) column in the employee
> table (in which the primary key is emp_id), and you want to ensure that
> Social Security numbers are unique, create a UNIQUE constraint on ssn.
> If the user enters the same Social Security number for more than one
> employee, an error is displayed."
>
> Hope this clarifies.
>
>
> Ken Stoker
> Technology Commercialization
> Information Systems Administrator
> PH: (509) 375-3758
> FAX: (509) 375-6731
> E-mail:  Kenneth.Stoker at pnl.gov
>
>
> -----Original Message-----
> From: Mark Whittinghill [mailto:mwhittinghill at symphonyinfo.com]
> Sent: Monday, March 08, 2004 7: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
>
>
>