November 14, 2013 at 7:11 am
recently took over a new database, and found that every single table that has a clustered primary key ALSO has a identical nonclustered unque constraint on the same table.
ie
CREATE TABLE [dbo].[ScanNetDw] (
[ScanNetDwID] INT IDENTITY(1,1) NOT NULL,
[OtherColumns] CHAR(3) NOT NULL,
CONSTRAINT [PK_ScanNetDw] PRIMARY KEY CLUSTERED (ScanNetDwID asc),
CONSTRAINT [IX_ScanNetDw_ID] UNIQUE NONCLUSTERED (ScanNetDwID asc))
now i believe i've read that an additional index on the same column as the PK might make sense in certain cases, because the size of the index could be smaller and thus more valuable in some queries, but that seems to be not true when i run exec sp_MSIndexSpace ScanNetDw: the non clustered index is larger in this example.
so is this someones bad habit that i should clean up after, or does it make sense to anyone else out there?
Index ID Index Name Size (KB) Comments
-------- ---------------------------------------------- ----------- ----------------------------
1 PK_ScanNetDw 1032 Size excludes actual data.
2 IX_ScanNetDw_ID 18344 (None)
3 IX_ScanNetDw_acroloctdatlastmrn 114872 (None)
4 _WA_Sys_00000003_3B95D2F1 0 (None)
5 _WA_Sys_00000006_3B95D2F1 0 (None)
6 _WA_Sys_00000008_3B95D2F1 0 (None)
7 _WA_Sys_00000009_3B95D2F1 0 (None)
8 _WA_Sys_00000004_3B95D2F1 0 (None)
9 _WA_Sys_0000000B_3B95D2F1 0 (None)
10 _WA_Sys_0000000C_3B95D2F1 0 (None)
11 _WA_Sys_00000007_3B95D2F1 0 (None)
Lowell
November 14, 2013 at 7:26 am
I don't know, Lowell. It doesn't make much sense to me to have a nonclustered index on the same field as your clustered index unless the nonclustered index contains some other fields as well. If your nonclustered has other columns included, it could prove useful in some queries, but since all nonclustered indexes inherit the value from the clustered index anyway, why have the clustered field as the key field in the nonclustered index?
Granted, I certainly don't know everything there is to know about indexing, so there may be something I'm missing here. However, if every single table is defined this way, I think the odds are that it is as you have surmised (someone's habit) and was not done intentionally during a round of performance tuning.
November 14, 2013 at 7:28 am
It doesn't make much sense usually. There are edge cases where the narrow nonclustered index can be useful because it's narrower and hence faster to use, but that requires specific query forms and a very latency-sensitive system to really be worthwhile.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2013 at 7:33 am
Ed Wagner (11/14/2013)
If your nonclustered has other columns included, it could prove useful in some queries, but since all nonclustered indexes inherit the value from the clustered index anyway, why have the clustered field as the key field in the nonclustered index?
There are good and valid reasons to explicitly include the clustered index key in nonclustered indexes where it's needed, but that is in addition to other columns, not in a situation like this.
Being a unique constraint, it can't even have include columns, nor does it have other key columns and it can't be filtered, so very limited use in general
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2013 at 7:38 am
GilaMonster (11/14/2013)
Ed Wagner (11/14/2013)
If your nonclustered has other columns included, it could prove useful in some queries, but since all nonclustered indexes inherit the value from the clustered index anyway, why have the clustered field as the key field in the nonclustered index?There are good and valid reasons to explicitly include the clustered index key in nonclustered indexes where it's needed, but that is in addition to other columns, not in a situation like this.
Being a unique constraint, it can't even have include columns, nor does it have other key columns and it can't be filtered, so very limited use in general
that's along the lines of what i thought: an index on the column that features includes might make sense, but the additional unique cosntraint just doesn't make sense to me...who wants to i double up on really wanting the PK to be unique?
Lowell
November 14, 2013 at 11:07 am
Just a pot shot:
It might be driven by the developer who want to make sure the key is unique but wasn't able to define a UNIQUE PRIMARY KEY (for good reasons, though 😉 ). Therefore, the second index might have been added "just to be sure...".
Another pot shot regarding the index size: check the fragmentation level for both indexes. Maybe there's a frequent maintenance of the clustered index but none at all for the nonclustered. Just guessing though...
November 14, 2013 at 11:22 am
LutzM (11/14/2013)
Just a pot shot:It might be driven by the developer who want to make sure the key is unique but wasn't able to define a UNIQUE PRIMARY KEY (for good reasons, though 😉 ).
Primary keys are always unique. Adding a unique constraint to exactly the same column as the pk is defined on isn't going to make it more unique. 😉
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2013 at 11:48 am
GilaMonster (11/14/2013)
LutzM (11/14/2013)
Just a pot shot:It might be driven by the developer who want to make sure the key is unique but wasn't able to define a UNIQUE PRIMARY KEY (for good reasons, though 😉 ).
Primary keys are always unique. Adding a unique constraint to exactly the same column as the pk is defined on isn't going to make it more unique. 😉
All I'm saying is the there might be the chance of a developer not knowing enough about indexing and being unable to define a primary key as unique ("Sql Server won't let me define the PRIMARY KEY as UNIQUE, so I had to add it separately!").
It also might be a developer that used to work as an engineer before (like I did) -> we've been told to always consider a "safety factor" when performing calculations e.g. how thick the walls should need to be to hold the next floor (and the roof).
Adding the second index would be a "safety factor 2" 🙂
November 14, 2013 at 11:49 am
Ah, see what you mean.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply