Sql-server – Design for table

sql serversql-server-2012

I have a table:

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?

Best Answer

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.