Given a simple row-based table without a PK but with a row-based clustered index like so:
create clustered index (CX_PropertyValue) ON (dbo).(EntityValue) ((PropertyId), (Value))
Then I wish to add a column store index that is segmented in the same order as the clustered index above:
create nonclustered columnstore index CS_IX_EntityValue on dbo.PropertyValue(
PropertyId, Value
)
with (drop_existing = on, maxdop = 1); -- maxdop=1 to preserve the order by property
MaxDop hint to preserve order came from: here
Then the following query was used to report the min/max data_id for the PropertyId column and it the full range was reported on each of the 7 segments:
-- (Warning: This query is not joined quite right in that it may report the wrong table column
-- name but the min/max data itself makes it obvious which column is PropertyId for this case).
select top 20000000000
s.Name as SchemaName,
t.Name as TableName,
c.name as ColumnName,
c.column_id as ColumnId,
cs.segment_id as SegmentId,
cs.min_data_id as MinValue,
cs.max_data_id as MaxValue
from sys.schemas s
join sys.tables t
on t.schema_id = s.schema_id
join sys.columns c
on c.object_id = t.object_id
join sys.partitions as p
on p.object_id = t.object_id
join sys.column_store_segments cs
on cs.hobt_id = p.hobt_id
and cs.column_id = c.column_id
order by s.Name, t.Name, c.Name, cs.Segment_Id
I tried making the clustered index unique which did slightly affect the reported ranges but still was not monotonically increasing.
Any ideas?
Here is a Link that accomplished the segmentation in this manner but I don’t see any difference.
