Sample Header Ad - 728x90

Calculating min/max values of a columnstore table with sys.column_store_segments

0 votes
1 answer
141 views
I need to fetch the max and min values of an arbitrary column on a table. The table has a clustered columnstore-index, billions of rows, and hundreds of columns. I'd like to leverage that fact that every columnstore segment already has its min and max value recorded in the sys.column_store_segments table's min_data_id and max_data_id columns. It shouldn't be necessary to access the actual table, as the columnstore segment metadata has sufficient information. But it appears that the min_data_id and max_data_id columns cannot be trusted to contain the max and min values because they may also contain references into a dictionary instead. Is there any way that I can determine whether min_data_id and max_data_id are real values vs dictionary references? If they are references, is it possible for me to access the underlying dictionary? When I try using MIN and MAX, the queries can take minutes to complete, making me think that metadata table is not being used. References: https://learn.microsoft.com/en-us/archive/technet-wiki/5651.understanding-segment-elimination https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-column-store-segments-transact-sql?view=sql-server-ver16
Asked by Derek (15 rep)
Mar 27, 2024, 07:45 PM
Last activity: Aug 6, 2024, 07:50 AM