CODEWITHSUNDEEP
sqlsql-serverindexingdatabase-partitioning
Макс Рогожин
Макс Рогожин

Reputation: 28

SQL Server partitioning and index strategy

I'm looking for a partitioning solution. And, obviously, I'm interested in the performance side of it. Means, not to make it too bad while benefiting maintenance time. My question is related to one of the tables that I have. And it looks similar to

Id bigint IDENTITY PRIMARY KEY CLUSTERED,
DatetimeFiled,
/*
    a lot of other fields
*/

According to the data structure and usage, it's suitable to split the table into partitions by the DatetimeFiled (classic), because I have filters by date on this table. But I do have filters by Id as well. Moreover, I have JOINs that use the Id field as a predicate, which now benefits from its uniqueness (https://www.brentozar.com/archive/2015/08/performance-benefits-of-unique-indexes/).

So, I decided to use Id ,DatetimeFiled as UNIQUE CLUSTERED INDEX.

But I doubt will it still benefit JOINs by Id field?
And is it ok to use that kind of field order, because I saw that partitioned field is often in the first place?

Upvotes: 0

Views: 453

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88852

Using a trailing clustered index column as the partition column is a common and useful approach. You can find rows by Id by seeking the Clustered Index in each partition, and you can find rows by DatetimeFiled through partition elimination.

Upvotes: 1

Related Questions