|My original question was, "What should I name the clustered index column which isn't the primary key?" but then I started putting in why I was doing it, and changed the subject to include 'is this a good idea?'
I have a table and the primary key is a GUID, which is generated from vendor code so I cannot do what I would normally do: create an int for the clustered index and use that as the pk.
My idea is to create the table like this:
CREATE TABLE [dbo].[table_name](
[need_a_good_name_here] [int] IDENTITY(-1,-1) NOT NULL, -- Adding this field
[guid] [char](36) NOT NULL PRIMARY KEY NONCLUSTERED,
) ON [PRIMARY]
CREATE CLUSTERED INDEX Ix_Table_Name ON [dbo].[table_name] ([need_a_good_name_here] ASC)Feel free to comment on the following:
1. I don't want the guid to be the clustered index because there will be inserts.
There were 3,000 inserts the first month, and I'm expanding from one category to
four five. No way to know the distribution of inserts (except 1st shift / business days) and I don't know the frequency of the other categories.
Fill factor == 100, not sure I can get it changed.
2. I have the identity starting at -1 and decreasing by 1 to make it obvious that it isn't the pk.
Don't know how much this will help, but I do what I can.
3. I don't like the names ix_need_a_good_name_here, need_a_good_name_here_ix, need_a_good_name_here_id, because if I saw them I would assume that was the primary key. Suggestions?
4. Should I just not worry about splits?
5. Remember my mention of vendor code... I can't use SQL Server NEWSEQUENTIALID() which would make #1 a non-issue.
All feedback appreciated!
modified 24-Apr-18 17:23pm.