I have a table in an Azure Synapse database. This table has a clustered columnstore index. It has a datetime column. The minimum value in this column is 2001-03-27 00:00:00 and the maximum is 2022-12-31 00:00:00.

When I look at sys.pdw_nodes_column_store_segments (docs). This column has encoding_type "2 = VALUE_HASH_BASED - non-string/binary column with common values in dictionary" for all segments.

The smallest min_data_id and largest max_data_id are 158806415769600 and 192947110805504 respectively.

How do the values in the data_id columns relate to the source data in the table?

1 Answer 1

    @dt1 datetime = '2001-03-27 00:00:00',
    @dt2 datetime = '2022-12-31 00:00:00';

    dt1 = CONVERT(bigint, CONVERT(binary(8), @dt1)),
    dt2 = CONVERT(bigint, CONVERT(binary(8), @dt2));
dt1 dt2
158806415769601 192947110805504

The first four bytes are the number of days since '1900-01-01'.
The second four bytes are the number of 1/300s intervals since midnight.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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