Where should the partitioning column go in the primary key on SQL Server?
Posted
by Bialecki
on Stack Overflow
See other posts from Stack Overflow
or by Bialecki
Published on 2010-03-16T21:43:43Z
Indexed on
2010/03/16
22:11 UTC
Read the original article
Hit count: 308
Using SQL Server 2005 and 2008.
I've got a potentially very large table (potentially hundreds of millions of rows) consisting of the following columns:
CREATE TABLE (
date SMALLDATETIME,
id BIGINT,
value FLOAT
)
which is being partitioned on column date in daily partitions. The question then is should the primary key be on date, id or value, id?
I can imagine that SQL Server is smart enough to know that it's already partitioning on date and therefore, if I'm always querying for whole chunks of days, then I can have it second in the primary key. Or I can imagine that SQL Server will need that column to be first in the primary key to get the benefit of partitioning.
Can anyone lend some insight into which way the table should be keyed?
© Stack Overflow or respective owner
