December 8, 2005 at 4:39 pm
I found this script creating primary key and index on the same columns.
Is the index needed?
if exists (select * from sysobjects where id = object_id('std_idxq') and sysstat & 0xf = 3)
drop table std_idxq
go
CREATE TABLE std_idxq(
idxq_record_type varchar(1) NOT NULL,
idxq_internal_no varchar(8) NOT NULL,
CONSTRAINT PK_std_idxq
PRIMARY KEY NONCLUSTERED (idxq_record_type,idxq_internal_no)
)
create clustered index std_idxq_ind0 on std_idxq
(idxq_record_type, idxq_internal_no )
Print 'IDXQ Table created'
December 8, 2005 at 4:45 pm
>>Is the index needed?
Every table should have a clustered index, so in that sense, yes.
The solution would seem to be to drop the index and make the Primary Key clustered. Or alternatively, drop the index & find a different set of column(s) that represent a better candidate for clustering. Tough to answer that without knowing data distributions and typical access patterns.
December 8, 2005 at 4:50 pm
I agree, pk should be clustered but should index be created on the same column(s) as pk? if yes why?
December 8, 2005 at 5:56 pm
No, just a waste of resources. Get rid of the duplicate index.
ron
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply