duplicate index?
-
Hi,
I came across the following code (I've changed table and field names) on a production server. It struck me immediately that the PK should be clustered and the other index is unnecessary. Is my analysis correct (100% of the time) or might there be a circumstance where the setup below works better?
By the way, there are other indexes on the table.
Thanks,
David.
CREATE CLUSTERED INDEX [idxMyTableField1] ON [dbo].[MyTable]([Field1]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] ADD
CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
(
[Field1],
[Field2]
 
WITH FILLFACTOR = 90 ON [PRIMARY]GO
-
SQL Server automatically creates a unique index to enforce the uniqueness requirement of the PRIMARY KEY constraint. If a clustered index does not already exist on the table, or a nonclustered index is not explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint.
Please refer BOL :
Creating and Modifying PRIMARY KEY Constraints
Minaz Amin
"More Green More Oxygen !! Plant a tree today"
-
Thanks Minaz,
I think you're missing my point. Clearly from the code there is a non-clustered primary key and a clustered index.
The clustered index covers one field, which is the first of two fields covered by the primary key.
As I see it, there is one index too many. I could make the primary ked clustered, and delete the non-clustered index without any conceivable performance hit.
Hope this is more clear.
-
One point I missed is the impact on the other (non-clustered) indexes. The pointer back to the clustered index will change from being one field (Field1) to two fields (Field1 and Field2). So I guess they would take up some more space - I'm not sure that there would be any (negative) performance implications, though(?)
-
Yeah, I'd drop the clustered index and make the PK clustered. It's as you said, the leading edge to the PK is the same as the clustered index, so one of these isn't necessary. Since one is the PK and one isn't, get rid of the one that isn't the PK. You should see quite a lot of performance benefit since you'll probably eliminate some bookmark lookups, etc., not to mention having to only maintain a single index instead of two.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Cheers, Grant....I'll get my script together!
-
What datatypes are Field1 and Field2? If 2 is a wide type then it's better if it isn't in the cluster. The wider a clustered index key, the wider all the nonclustered indexes.
Wide indexes can lead to reduced performance as more pages have to be read.
Personally, I'd be looking to see if there was a more appropriate place for the cluster
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks, Gail, for your input.
They're both int datatype, so it's not a huge increase.
I think it's the most appropriate place for the cluster, as the table is typically joined on these two fields, and queries usually specify either Field1 and Field2 or just Field1.
-
That will probably be fine then. Just wanted to be sure that Field2 wasn't a char(500) or something like that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply