Which sql index to use on my table for best query performance

Question

I have the following table. My stored procedure always uses IitemId and Created date range. Where ItemId = ... and Created > ... and Created < .... What would be the best design for performance.

I have a non-clustered index on ItemId

CREATE TABLE [dbo].[LV] (
[Id]       UNIQUEIDENTIFIER NOT NULL,
[ItemId]   UNIQUEIDENTIFIER NOT NULL,
[C1]  NVARCHAR (7)     NOT NULL,
[C2] NVARCHAR (7)     NOT NULL,
[C3]  NVARCHAR (2)     NOT NULL,
[Created]  DATETIME2 (7)    NOT NULL,
CONSTRAINT [PK_LV] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_LV_Items_ItemId] FOREIGN KEY ([ItemId]) REFERENCES [dbo].[Items] ([Id]) ON DELETE CASCADE
);


GO
CREATE NONCLUSTERED INDEX [IX_LV_ItemId]
ON [dbo].[LV]([ItemId] ASC);

Should I add indexes to ItemId and Created? Non-clustered or clustered?

It is not related to your question, but it is performance related. It is really, [really bad practice to use a non-sequential unique identifier as your clustering key.](https://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/). — GarethD, Jan 22 '19 at 14:37
Or more reading - [More considerations for the clustering key – the clustered index debate continues!](https://www.sqlskills.com/blogs/kimberly/more-considerations-for-the-clustering-key-the-clustered-index-debate-continues/) — GarethD, Jan 22 '19 at 14:42

score 0 · Answer 1 · answered Jan 22 '19 at 14:28

0

If your only performance concern is that stored procedure, then yes, you should make the clustered index on ItemId and Created.

answered Jan 22 '19 at 14:28

Tab Alleman

31,483
7
36
52

2

Except that ItemId is a uniqueidentifier so clustering on that is going to lead to horrific fragmentation. – Sean Lange Jan 22 '19 at 14:39
1

@SeanLange nothing a nightly maintenance plan can't handle, unless this table's data is getting modified hundreds of thousands of times a day... – Tab Alleman Jan 22 '19 at 14:42
GarethD's linked article up in the comments has a lot of good information, which can help make a decision to whether the OP should continue with a clustered GUID PK or not. It was a great read! – HardCode Jan 22 '19 at 15:36

score 0 · Answer 2 · answered Jan 22 '19 at 14:33

0

I agree with Tab's answer (yes, cluster it), but would add that to tighten your design, you might look a little deeper and consider making this a primary key, or if you can't, why not. There's a nice writeup on the logic behind this on this Stack Overflow Post

answered Jan 22 '19 at 14:33

markaaronky

1,231
12
29

1

The table posted has a primary key on the **Id** column. – Sean Lange Jan 22 '19 at 14:41

score 0 · Accepted Answer · answered Jan 22 '19 at 14:49

For the query you have in mind, you want a composite index on (itemId, created).

This works because the condition on itemId is equality, so the inequality will use the second key in the index.

A clustered index could help, depending on the nature of the data. If an item is only stored one or two or three times in the table, then a clustered index will probably not be of much use. If an item is stored many times, then the rows for the item will be spread through the table and a clustered index would help.

There are even caveats to that. If the table is used frequently and is fully loaded into the data pages, then a clustered index would help but not be as beneficial as when the table is too big to fit into available memory.

Which sql index to use on my table for best query performance

3 Answers3