RE: Should this index be clustered?

SSC Guru

Points: 61136

November 14, 2017 at 4:36 pm

#1968106

Please forgive me if I'm misunderstanding what you said but I don't get this:

It's currently a heap with a NC Primary Key, but I feel that it should be clustered to prevent duplication of data.

This will fail:
create table dbo.x (id int primary key nonclustered); insert dbo.x values (1),(1);

This will not:
create table dbo.y (id int not null); create clustered index cl_y on dbo.y(id); insert dbo.y values (1),(1);

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001