clustered constraint vs clustered index
-
What is the difference between a unique clustered index and a unique clustered constraint?
-
I've asked excactly the same question here before! But NOBODY ANSWERED! So, please tell us, Gurus!
/Tomi
-
A constraint is added to the column definition like this:
CREATE TABLE table_name (
abc int NOT NULL
CONSTRAINT constraint_name UNIQUE CLUSTERED
)
An index is created separately from the table definition like this:
CREATE CLUSTERED UNIQUE INDEX index_name ON table_name( abc )
I think both ways have a similar affect but the second one is cleaner. It provides more flexibility. For example, it allows you to put an index on a given filegroup if you want to.
-
Oh I see. But which one is faster?
/Tomi
-
I think there is no performance difference because a unique constraint is implemented in MS SQL Server through a unique index creation. In the same manner, a PRIMARY KEY constraint creates a unique index automatically.
But with the first approach you cannot specify a filegroup which could lead to performance implications.
Edited by - mromm on 03/04/2003 6:43:02 PM
-
Actually a PK creates a Unique COnstraint. MS also suggests under "Constraints" in SQL BOL the following
quote:
UNIQUE constraints enforce the uniqueness of the values in a set of columns.No two rows in the table are allowed to have the same not null values for the columns in a UNIQUE constraint. Primary keys also enforce uniqueness, but primary keys do not allow null values. A UNIQUE constraint is preferred over a unique index.
Also, read "Using Unique Indexes" in SQL BOL and the again suggest Constraint over Index but they do state a Unique Constraint creates a unique index as well. So performance wise they are equal.
And read "Indexes" in BOL making note of
quote:
Tip Although a unique index will help locate information, for the best performance results it is recommended that you use primary key or unique constraints instead. For more information about these constraints, see Primary Key Constraints and Unique Constraints.Now beyond that MS is very vague on the subject as to how they differ. But based on Cosntraint as the concept behind a UNIQEU CONSTRAINT and the fact it creates a UNIQUE INDEX and they state use UNIQUE INDEX if it is uniqueness of the data itself (you will see in the areas I suggested read) and that MS states for performance use a CONSTRAINT I believe the key is here.
A Constraint is checked before the data is INSERTED or UPDATED so a UNIQUE CONSTRAINT will validate the data before the INSERT/UPDATE occurrs. The UNIUE INDEX then most likely check the data at the time of the INSERT/UPDATE thus you have a transaction that has proceeded futher than a CONSTRAINT would have allowed, the data is actually insert/updated and rolledback with a UNIQUE INDEX where the CONSTRAINT will prevent entirely, no need to rollback.
Ok lets see what reall happens. Here is my test
Test Table
CREATE TABLE [Table12] (
[ai] [int] NOT NULL
) ON [PRIMARY]
GO
Both items ran 3 tests of a 399 item table, server restarted between each test.
Unique Constraint on test item took moment to return error
Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'IX_Table12'. Cannot insert duplicate key in object 'Table12'.
The statement has been terminated.
Unique Index took 3 seconds to error
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'Table12' with unique index 'IX_Table12'.
The statement has been terminated.
So does look like that is right. The Constraint performed faster as would be expected based on my theory of them.
Anyone else want to test and give their results if they are different please feel free. Just keep in mind that caching of data does occurr so clear the cache and I find the best way is to have a cold setup by stopping and restarting SQL.
-
Oops should have included that the difference is seen more at Insert and Update. Selects and such perform exactly the same.
-
Thats a good explanation and a good bit of testing.
Andy
-
Wow! Antares, you really know what you're talking about!
Some questions. Does the Index Tuning Wizard tune constraints? And do I have an old version of BOL? I can't find this: "A UNIQUE constraint is preferred over a unique index" written anywhere! I even printed out over ten pages four months ago to learn about indexes and constraints! And that particular line would have been very useful.
/Tomi
-
In SQL 7 BOL I find it under Index as "constraints" and "overview" under that. It will be the 3rd Bullet item.
In SQL 2000 I find it under the same except when you click "overview" and additional popup will occurr pick "Constraints" which location is "SQL Server Architecture" and under the bold section "Classes of Constraints" it is the 3rd bullet".
Checked 2nd and 3rd revision of SQL 2000 BOL, did not have 1st revision installed to know for sure but wording is roughly same as SQL 7 so I assume is there like this or like SQL 7s.
As for Index Tuning Wizard I did not test to see if it creates Unique Constraints or Unique Indexes. I would assume if MS did a proper job on their tool it should if it even takes into consideration Unique. However, I almost bet it doesn't assume Unique when no index is available on a column and probably doesn't take into account Unique on existing indexes as this assumption could lead to potential issues for a user later on if the items are not truely unique.
-
quote:
A constraint is added to the column definition like this:CREATE TABLE table_name (
abc int NOT NULL
CONSTRAINT constraint_name UNIQUE CLUSTERED
)
An index is created separately from the table definition like this:
CREATE CLUSTERED UNIQUE INDEX index_name ON table_name( abc )
I think both ways have a similar affect but the second one is cleaner. It provides more flexibility. For example, it allows you to put an index on a given filegroup if you want to.
Be careful here. Creating a unique index or clustered index on a different file group than the table effectively moves the table to a new file group.
-
Ron, I realize that about clustered indexes. I only meant there is the option but, of course, you use it only with non-clustered indexes.
-
You only have to be concerned with making an index Clustered. You can have Unique indexes that are non-clustered.
The reason is a Clustered index sorts the data in the table acroding to the field(s) in the Clustered index. The table is also actually part of the Clustered index and developes root/leaf page(s) once the table grows beyond a single page.
-
I have found the two methods to be statistically equal - but prefer the INDEX over the CONSTRAINT for no arguable reason
Keep in mind one more thing if performance is the issue... Early checking is much more efficient.
For example: In my test with 10000 entries already in the table and attempting to insert 1000 duplicates
Code set A
INSERT INTO TestTable VALUES ( @dupcode ) --different for each loop
IF @@ERROR <> 0
SET @errCnt = @errCnt + 1
and Code set B
IF ( SELECT COUNT(*) FROM TestTable WHERE TestCode = @dupCode ) = 0
INSERT INTO TestTable VALUES ( @dupcode )
ELSE
SET @errCnt = @errCnt + 1
Code set A required an average of 1.7 seconds while Code set B averaged 0.06!!!
I realize that code set B should still perform the error check after the insert, but remember that you can greatly improve performance by doing initial checks in your stored procedure rather than just leaving it all up to SQL.
Guarddata-
-
Hey GuardData can you be a bit more descriptive on your testing and also give final result comparisons?
And which is A and which is B?
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply