Patitioning expression

Database
Enthusiast

Patitioning expression

Which of the following partitioning expression has a better performance while SELECT, INSERT operations ? Consider the table has data around couple of Terabytes.

PARTITION BY RANGE_N(LOAD_TIMESTAMP  BETWEEN TIMESTAMP '2015-01-01 00:00:00.000000' AND TIMESTAMP '3499-12-31 23:23:59.999999' EACH INTERVAL '1' DAY );

or

PARTITION BY RANGE_N(CAST(LOAD_TIMESTAMP  AS DATE AT LOCAL)  BETWEEN DATE '2015-01-01' AND '3499-12-31' EACH INTERVAL '1' DAY );

Tags (2)
1 REPLY
Junior Contributor

Re: Patitioning expression

Hi Abhi,

both should be similar, resulting in daily partitions.

But you should limit the upper date to something like 2030 and add an another partition for your "until changed". Otherwise you'll waste a lot of disk space, as you get way more than 65535 partitions and the partition number will be 8 bytes instead of 2.