I have a table:

A int, 
B int, 
H int, 
DataValue float

All of my queries do aggregate operations, with A and some other columns (several combinations) in the WHERE clause (A is always in).

What type of table design (partition/not; PK(Identity) OR PK(A, B, ..., H); rowStore/columnStore indexes) should I apply?


Partition is Enterprise Edition only, hence quite expensive. It makes large tables (>1TB) easier to work with, but it my book, it does not necessarily make queries run faster.

You need to describe the contents on the A column a bit better, to determine if the column should be part of the Primary Key, and also if it is always WHERE A=42 or WHERE A in (1,2,3,42)

IMHO the Column A needs to be NOT NULL if you want it as primary key.

Kimberly Tripp has some good blog posts on selecting the Clustered Key: for instance this one.

  • Column A has ~10 distinct values and it's always "WHERE A=8" (or other integer). – user2820173 Dec 1 '15 at 13:26
  • @user2820173; then SQL Server can jump right into the index, if the index has Column A as the first column in the PK. No real savings on Partitioning by Column A. We partition by a DateTime column, where each partition is one month wide. – Henrik Staun Poulsen Dec 2 '15 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.