What is the difference in Unique Nonclustered Index Creation methods?
-
What are the differences in creating an Index on multiple columns as a Constraint or as an Index? What reasons can be given for using one over the other (e.g. I'd use the Constraint when....)?
EXAMPLES:
CREATE
UNIQUE NONCLUSTERED INDEX IX_VisitorLastAndFirstName
ON dbo.Visitors(VisitorLastName, VisitorFirstName)
ON [PRIMARY]
ALTER TABLE dbo.Visitors ADD CONSTRAINT
IX_VisitorLastAndFirstName
UNIQUE NONCLUSTERED
(VisitorLastName, VisitorFirstName)
ON [PRIMARY]
-
The constraint is declarative and is portable to other ANSI compliant database platforms.
The index is a physical implementation which may or may not be portable to other RDBMS's.
-
Besides,
You can disable a constraint but you can't disable an index (not yet you can in 2005
)You can rebuild/Defrag/drop indexes but you drop/create constraints
Cheers!
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply