SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Should this index be clustered?


Should this index be clustered?

Author
Message
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80986 Visits: 20753
I have a table that is heavily used which inserts and deletes rows frequently during the day. This is just a placeholder to identify what the system should work on, so it just has a large key. It's currently a heap with a NC Primary Key, but I feel that it should be clustered to prevent duplication of data. The people that created this, thought that it would create too much contention and fragmentation, but in my opinion this would just be replicated on the index.
Here's the current structure of the table (with the names changed). Am I right? What or how should I test to give a certain answer?

CREATE TABLE SomeWorkTable(
MaintTableID int NOT NULL,
SomeCode char(1) NOT NULL ,
MilestoneID int NOT NULL DEFAULT 0,
Loaded bit NULL,
RuleID int NOT NULL DEFAULT 1,
SomeOtherID int NOT NULL DEFAULT 0,
);

GO
ALTER TABLE SomeWorkTable ADD CONSTRAINT PK_SomeWorkTable PRIMARY KEY NONCLUSTERED(MaintTableID, SomeCode, MilestoneID, RuleID, SomeOtherID);
GO



Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (187K reputation)SSC Guru (187K reputation)SSC Guru (187K reputation)SSC Guru (187K reputation)SSC Guru (187K reputation)SSC Guru (187K reputation)SSC Guru (187K reputation)SSC Guru (187K reputation)

Group: General Forum Members
Points: 187137 Visits: 39780
Luis Cazares - Tuesday, November 14, 2017 10:51 AM
I have a table that is heavily used which inserts and deletes rows frequently during the day. This is just a placeholder to identify what the system should work on, so it just has a large key. It's currently a heap with a NC Primary Key, but I feel that it should be clustered to prevent duplication of data. The people that created this, thought that it would create too much contention and fragmentation, but in my opinion this would just be replicated on the index.
Here's the current structure of the table (with the names changed). Am I right? What or how should I test to give a certain answer?

CREATE TABLE SomeWorkTable(
MaintTableID int NOT NULL,
SomeCode char(1) NOT NULL ,
MilestoneID int NOT NULL DEFAULT 0,
Loaded bit NULL,
RuleID int NOT NULL DEFAULT 1,
SomeOtherID int NOT NULL DEFAULT 0,
);

GO
ALTER TABLE SomeWorkTable ADD CONSTRAINT PK_SomeWorkTable PRIMARY KEY NONCLUSTERED(MaintTableID, SomeCode, MilestoneID, RuleID, SomeOtherID);
GO


Not sure what you mean by duplication of data, unless you mean that the nonclustered index is duplicating all the data from the table. My question would be how are the inserts and deletes being done? Is this table like a queue, first in first out? If so, I would make the index a clustered index and do it such that new data is inserted at the end and old data pulled from the front, just like a queue.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80986 Visits: 20753
Lynn Pettis - Tuesday, November 14, 2017 12:00 PM


Not sure what you mean by duplication of data, unless you mean that the nonclustered index is duplicating all the data from the table. My question would be how are the inserts and deletes being done? Is this table like a queue, first in first out? If so, I would make the index a clustered index and do it such that new data is inserted at the end and old data pulled from the front, just like a queue.

Yes, that's what I meant. Each row is written once on the table and then basically copied on the nonclustered index (except for the bit column).
The inserts are being done either by batch loads or whenever the system performs an action that affects the data from tables referenced by MainTableID and SomeOtherID. The deletes are being done after the row has been evaluated and assigned to the correct location.
This is not a FIFO, as there are some way to establish precedence based mainly on the MilestoneID. Ideally, everything should be deleted before the next batch load comes which would contain thousands of new rows.



Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Alan Burstein
Alan Burstein
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27045 Visits: 8424
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);


-- Alan Burstein


Helpful links:

Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw

Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K

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

Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (187K reputation)SSC Guru (187K reputation)SSC Guru (187K reputation)SSC Guru (187K reputation)SSC Guru (187K reputation)SSC Guru (187K reputation)SSC Guru (187K reputation)SSC Guru (187K reputation)

Group: General Forum Members
Points: 187137 Visits: 39780
Alan.B - Tuesday, November 14, 2017 4:36 PM
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);


Pretty sure he was talking about making the primary key (all the columns of the table in this case) a clustered index instead of a nonclustered index.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80986 Visits: 20753
Lynn Pettis - Tuesday, November 14, 2017 4:39 PM
Alan.B - Tuesday, November 14, 2017 4:36 PM
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);


Pretty sure he was talking about making the primary key (all the columns of the table in this case) a clustered index instead of a nonclustered index.


Yes, I should have made myself clearer. I was talking about duplicating the data by storing each row in the table and the non clustered index instead of only once using a clustered index.
The primary key needs to be kept in place, but I want to make sure that making it clustered instead of non-clustered is the right choice.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)

Group: General Forum Members
Points: 81554 Visits: 20465
Here's a reminder of the issues you may experience if you're performing deletes from your heap table:

https://www.red-gate.com/simple-talk/sql/t-sql-programming/delete-operation-sql-server-heaps/



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80986 Visits: 20753
ChrisM@Work - Wednesday, November 15, 2017 7:01 AM
Here's a reminder of the issues you may experience if you're performing deletes from your heap table:

https://www.red-gate.com/simple-talk/sql/t-sql-programming/delete-operation-sql-server-heaps/



Thank you, Chris.
This will be really useful when advocating the use of clustered indexes


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)

Group: General Forum Members
Points: 81554 Visits: 20465
Luis Cazares - Wednesday, November 15, 2017 7:41 AM
ChrisM@Work - Wednesday, November 15, 2017 7:01 AM
Here's a reminder of the issues you may experience if you're performing deletes from your heap table:

https://www.red-gate.com/simple-talk/sql/t-sql-programming/delete-operation-sql-server-heaps/



Thank you, Chris.
This will be really useful when advocating the use of clustered indexes


You're welcome Luis. It's easily forgotten and occasionally causes runaway table bloat.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
MadAdmin
MadAdmin
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5383 Visits: 2000
If doing a lot of deletes, then cluster the table.
When deleting from a heap, the empty pages dont get deallocated.
So your table grows forever.

Catch-all queries done right
Gail Shaw's Performance Blog
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search