Should I create a unique clustered index or a non-unique clustered index on this SQL 2005 table?
I have a table that stores millions of rows. It looks something like this:
Table_Docs
ID, Bigint (Identity col)
OutputFileID, int
Sequence, int
…(many other fields)
We're in a situation where the developer who designed it made the OutputFileID a clustered index. This is not unique. There can be thousands of records with this ID. It has no advantage for any processes using this table, so we plan to remove them.
The question is what to change it to ... I have two candidates, the ID column is a natural choice. However, we have a process that executes many update commands in this table, and a sequence is used for that. The sequence is not unique. Most records contain only one, but about 20% may have two or more records with the same sequence.
The INSERT application is a VB6 shotgun chunk that throws thousands of insert commands into a table. Inserted values are never in a specific order. So the sequence of one insert might be 12345 and the next one might be 12245. I know this can force SQL to move around a lot of data in order to keep the index ordered. However, the sequence of insertions is usually close to order. All inserts will take place at the end of the clustered table. For example: I have 5 million records with a sequence of 1 to 5 million. The INSERT application will insert sequences at the end of this range at any given time. Data reordering should be minimal (tens of thousands of records maximum).
The UPDATE app is now our .NET star. It makes all UPDATES in the Sequence column. "Update Table_Docs Set Feild1=This, Field2=That…WHERE Sequence =12345"
- hundreds of thousands of them per day. UPDATES completely and completely, randomly, touch all points of the table.
All other processes are just doing SELECT on this (web pages). Regular indexes cover them.
So my question is, is it better ... a unique clustered index on the ID column using an INSERT application or a non-unique clustered index on a Sequence using an UPDATE application?
a source to share
First, I would definitely recommend having a clustered index!
Second, your clustered index should be :
- narrow
- static (will never or hardly ever change)
- unique
- ever-increasing
therefore INT IDENTITY is a very smart choice.
If your clustering key is not unique, SQL Server will add a 4-byte unique identifier to these column values - so your clustering key and with it all nonclustered indexes on that table will be larger and less optimal.
So, in your case, I would choose an ID - it's narrow, static, unique and ever-increasing - couldn't be more optimal! Since Sequence
it is heavily used in UPDATE statements, be sure to add a nonclustered index to it!
See Kimberly Tripp's excellent blog posts on choosing the right clustering key for excellent background information on this topic.
a source to share
Typically, you want your clustered index to be unique. If it is not, SQL Server will actually add a hidden "uniquifier" to it to make it unique, and this adds overhead.
So, you are probably best off using the ID column as your index.
As a side note, using an identity column as the primary key is generally referred to as a surrogate key because it is not an integral part of your data. When you have a unique natural key, that is probably the best choice. In this case, it looks like you won't, so using a unique surrogate key makes sense.
a source to share
The worst part is that the inserts are out of order is the page splitting.
When SQL Server
a new record needs to be inserted into an existing index page and does not find space there, it takes up half of the records from the page and translates them into a new one.
Let's say you have these entries filling the entire page:
1 2 3 4 5 6 7 8 9
and you need to insert 10
. In this case, SQL Server
it will simply start a new page.
However, if you have this:
1 2 3 4 5 6 7 8 11
10
should go up 11
. In this case, will SQL Server
move the records from 6
to 11
to a new page:
6 7 8 9 10 11
The old page, as you can easily see it, will remain half full (only entries from 1
to 6
will be there very much).
This will increase the size of the index.
Let's create two sample tables:
CREATE TABLE perfect (id INT NOT NULL PRIMARY KEY, stuffing VARCHAR(300))
CREATE TABLE almost_perfect (id INT NOT NULL PRIMARY KEY, stuffing VARCHAR(300))
;
WITH q(num) AS
(
SELECT 1
UNION ALL
SELECT num + 1
FROM q
WHERE num < 200000
)
INSERT
INTO perfect
SELECT num, REPLICATE('*', 300)
FROM q
OPTION (MAXRECURSION 0)
;
WITH q(num) AS
(
SELECT 1
UNION ALL
SELECT num + 1
FROM q
WHERE num < 200000
)
INSERT
INTO almost_perfect
SELECT num + CASE num % 5 WHEN 0 THEN 2 WHEN 1 THEN 0 ELSE 1 END, REPLICATE('*', 300)
FROM q
OPTION (MAXRECURSION 0)
EXEC sp_spaceused N'perfect'
EXEC sp_spaceused N'almost_perfect'
perfect 200000 66960 KB 66672 KB 264 KB 24 KB
almost_perfect 200000 128528 KB 128000 KB 496 KB 32 KB
Even assuming that there will be no chance of missing records 20%
, the table becomes twice as large.
On the other hand, having a clustered key in Sequence
would result I/O
in a halving (as this can be done with one clustered index rather than two non-clustered ones).
So, I would take a sample of a subset of your data, insert it into a test table with a clustered index on, Sequence
and measure the resulting table size.
If it is less than twice the size of the same table with index on ID
, I would go for a clustered index on Sequence
(since the final result I/O
will be smaller).
If you choose to create a clustered index on Sequence
, make it ID
nonclustered PRIMARY KEY
and make the clustered index UNIQUE
on Sequence, ID
. It will use the meaningful ID
instead of the opaque uniquiefier.
a source to share