I want to convert a rowstore table to a columnstore table by creating a clustered columnstore index. There are three columns in the table: id,time, and value.
The table is ordered by id and time before creating columnstore index; however, after creating columnstore index, the row order is messed up. I thought it might be due to the parallelism and added the
maxdop = 1 option, but that didn't fix the problem. Can anyone help me with this?
Here is the code creating tables and indexs:
-- creating rowstore table drop table if exists tab1_rstore select id, time, value into tab1_rstore from tab0 order by id_loan, period option(maxdop 1) -- creating clustered index on rowstore table create clustered index idx on tab1_rstore (id,time) -- creating columnstore table select * into tab1_cstore from tab1_rstore option(maxdop 1) -- comparing the first two rows from these two tables select top 2 * from tab1_rstore select top 2 * from tab1_cstore
The screenshot of the query results:
-- creating clustered columnstore index create clustered columnstore index idx on tab1_cstore with (maxdop = 1) -- comparing the top two rows again select top 2 * from tab1_rstore select top 2 * from tab1_cstore
The screenshot of the query results with columnstore index:
My understanding is that the order of rows is determined by the compression algorithm and there is nothing we can do about it, see the limitation and restriction in the document here with the following quote:
Cannot include the ASC or DESC keywords for sorting the index. Columnstore indexes are ordered according to the compression algorithms. Sorting would eliminate many of the performance benefits.
I'm using SQL Server 2016 Developer Edition on Windows 10 64bit.