What is the most effective declaration for the PRIMARY KEY CLUSTERED in SQL table

Andreas ss 726

Hello,

I wonder something about the PRIMARY KEY CLUSTERED for a ceratain scenario.
I am not sure what would be the Fastest way to return the information I want.

I will describe what I want to return from a table (See Attached image of table)

The table exist of 3000 rows. The table only exists of Dates within 1 month. So it is 30 or 31 days. DateTimes like: 2010-01-03 17:45:00
_DayNr column tells which day it is in the date. 3 for 2010-01-03 17:45:00
It exists 1570 _featureNbr for EACH _DateTime. So for example:
2010-01-03 17:45:00 have 1570 _FeatureNbr and so on.

Now is my task this. I want to return ALL _FeatureNbr for each of the 3000
DateTimes like: 2010-01-03 17:45:00.

So I wonder what would be the most effective declaration for the PRIMARY KEY CLUSTERED?

Shouldn't I just use _DateTime as the first one and _FeatureNbr as the second one?
CONSTRAINT [PK_table20101] PRIMARY KEY CLUSTERED ([_DateTime] ASC, [_FeatureNbr]),

Or is it faster to use _DayNr first like this?
CONSTRAINT [PK_table20101] PRIMARY KEY CLUSTERED ([_DayNr] ASC, [_DateTime] ASC, [_FeatureNbr]),

Or should I do this in a different way. I am not 100% sure in what way I should think when declaring
those most effectively?

Thank you!

Ben Miller (DBAduck) 956 Reputation points

2020-10-19T00:22:44.223+00:00

Are the _DateTime values unique in the table?
Andreas ss 726 Reputation points

2020-10-19T01:12:43.093+00:00

Yes, the _DateTime values are always 100% unique.

Accepted answer

Ben Miller (DBAduck) 956 Reputation points

2020-10-19T14:44:44.22+00:00

I would recommend that you put in place and test the clustered index (_DayNr, _DateTime) and preferably it be Unique (could be made the primary key).

Not necessary to put the _FeatureNbr in the index as 3 keys deep will probably not be helpful in this case.

Ensure that you TEST this to ensure that you get what you expect for speed and results. If not then you can change things pretty simply as you have pretty small tables (relatively).
Please sign in to rate this answer.
Andreas ss 726 Reputation points

2020-10-19T14:51:29.443+00:00

Ben, that is great. I think I got a better understanding of the Clustered Key now. I was bit confused before my original post but you have helped me clear this out much better now. I will go for (_DayNr, _DateTime) then and as you say, TEST and try it out to see speed differences if changing the key. This gave me a good theoretical start then. Thank you for all your help!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

2 additional answers

Tom Phillips 17,731 Reputation points

2020-10-19T12:28:22.267+00:00

Do not confuse the primary key with the clustered index, they can be different. SQL Server as a default makes the PK clustered, however there is no requirement for it to be that way.

The PK should be the way you uniquely identify the records in your table.
The clustered index should be the most common way you search for records in the table.
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Ben Miller (DBAduck) 956 Reputation points

2020-10-19T02:49:14.543+00:00

Then you can make the Clustered Primary Key on the _DateTime column so when you query for the datetime value and want to get all the _FeatureNbr, it will be able to since you are seeking on the Clustered Key.
Please sign in to rate this answer.
Andreas ss 726 Reputation points

2020-10-19T07:57:43.073+00:00

I see, what would happen if I clustered it like this instead: PRIMARY KEY CLUSTERED ([_DayNr] ASC, [_DateTime] ASC, [_FeatureNbr]). What I wonder there is that, there will be 30 _DayNr since it is 30 days in a month and then there will be about 3000 _DateTime in a month. My wondering there, will not the seek for _DateTime go faster because it first will find the correct _DayNr and seeks within 30 indexes only and then when it has found that index, the engine will search for the correct _DateTime within about 100 indexes for that particular _DayNr (Total search: 30 + 100 = 130 indexes). Instead of just finding the correct _DateTime within: (Total search: 3000 indexes)? I am trying to understand if that is true and my thinking there is correct and this is a correct way to find out how to cluster the Primary Key?

Ben Miller (DBAduck) 956 Reputation points

2020-10-19T12:29:27.993+00:00

OK, so I must not understand what or how you will query this table.

The Primary Key (Clustered) should be unique and the clustered key goes into ALL non-clustered indexes so having a multi-key clustered index will affect space and speed of the table.

If you want to take advantage of the Primary Key for the seeks and can do that with 1 key, that is good. If this table will have no more than one month of dates in it, then you can make a design decision to have a multi column Clustered key and only ever have one index on the table if it satisfies the query.

So what is the WHERE clause you will use against this table? That will help determine what you would use. If the _DayNr column is not used in the where clause then you may not want that in the first key column. Help me understand how you will query the table.

Andreas ss 726 Reputation points

2020-10-19T13:56:49.84+00:00

I will try to describe better what exists in the table. First I must say that I will have perheps 25000 1 month
tables like this later on and 1 month table will take up 45000 rows later on also. The thing is that I need this search
query to be as fast as possible because it will be critial in the end because I will do enormous amount of searches.
So I try to understand what design that is the best. The idéa is using 1 month tables makes faster searches. I also calculated
for a scenario, that if I would use only 1 table for all data. That could mean 2 Billion rows which seems wrong.

Ben Miller (DBAduck) 956 Reputation points

2020-10-19T13:59:48.24+00:00

OK, let's set you up for success. What version and edition of SQL? (2019, 2017, 2016, etc) and (Standard or Enterprise edition)?

Andreas ss 726 Reputation points

2020-10-19T14:01:30.073+00:00

The goal is to return all _FeatureNbr for a specific date and time like: 2010-03-05 18:00:00. It exists date and times
with 15 minutes interval 24 hours a day which means: 24 h * 4 rows/hour * 30 days = 2880 rows in table.
We know _DayNr each date has like in above date, we have the _DayNr which is 5. My idéa is if we can use this column to make search faster with this query: SELECT _FeatureNbr FROM table1Month WHERE _DayNr = 5 AND _DateTime = '2010-03-05 18:00:00'
(Total search: 30 days + 96 rows/day = 126 indexes). Compared to if we would do only on the date like this:
SELECT _FeatureNbr FROM table1Month WHERE _DateTime = '2010-03-05 18:00:00' (Total search: 2880 indexes).
What would go faster? I also wonder at the same time what you mentioned: "having a multi-key clustered index will affect space and speed of the table"
How much more space or/and less speed would it mean to include the _DayNr like this? I use TINYINT (1 byte) for this _DayNr column.

Andreas ss 726 Reputation points

2020-10-19T14:24:29.197+00:00

I use SQL Server Express 2019. I would need Enterprise soon I beleive because the database will take up 100 GB+ of memory. I have 64 cores in the computer.

Ben Miller (DBAduck) 956 Reputation points

2020-10-19T14:34:05.41+00:00

I will explain better. The clustered index IS the table, so adding more columns to the Clustered Key does not impact space in that index. It is when you add another index which in this case it doesn't look like you will need it.

So if you are looking to use _DayNr as the where clause to keep things simple, then I would say that you can make the Clustered Key (_DayNr, _DateTime)

Andreas ss 726 Reputation points

2020-10-19T14:41:29.56+00:00

Yes, then I think I understand. So it is a good idéa for this scenario to use Clustered Key (_DayNr, _DateTime). I think I must just ask. I shouldn´t write it like this (_DayNr, _DateTime, _FeatureNbr). With other words the last _FeatureNbr is just unnecessary?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

What is the most effective declaration for the PRIMARY KEY CLUSTERED in SQL table

2 additional answers

Your answer