3

I have a large table with 30 columns in MSSQL 2014. The data will be inserted in bulks several times a day and the data should be kept for a month.

The purpose of it is to select aggregated queries. So it seems that Clustered Columnstore index is perfect for this. Except that one column is varchar and holds URL values that usually don't repeat. Will it work well with Columnstore? I worked with it , but only on data that hold numbers or repeating strings (like domain).

UPDATE 1. For purpose of importing and purging the table will be partitioned. 2. I don't have the exact DDL right now, because we are at the design stage. Thanks

  • Can we see the DDL of the table? Is the table partitioned? Is partitioning a possibility? In SQL Server 2016 you could have a non-clustered CSI that didn't include the VARCHAR column if upgrading is a possibility. – James Anderson Aug 25 '16 at 10:39
  • Yes, the table will be partitioned by date which will help me keeping the range to 1 month and the for the bulk insert – Liya Tansky Aug 25 '16 at 13:00
  • Sorry, but I don't have the exact DDL, because we are in the planning stage – Liya Tansky Aug 25 '16 at 13:04
  • Will the bulk loads and purging be done via partition switching? Will these be the only updates/deletes to this table? – James Anderson Aug 25 '16 at 13:57
  • Yes, this table will be loaded from log files (originated in Hadoop) and will be used for reporting only. So Partitioning seems to answer it's needs. – Liya Tansky Aug 25 '16 at 14:04
1

You may find that a clustered columnstore index on your table works well but I would really want to avoid having that VARCHAR column in a columnstore index. One of the problems this can cause is dictionary pressure.

As you are looking to partition the table and alter data via partition switching I would suggest building a non-clustered columnstore index on your table that does not include the VARCHAR column that contains URLs. This will make the table read-only but that doesn't stop you loading data in and out via partition switching.

The way I have done this in the past is to bulk load the data into a staging table, do any post load processing there and then switch the whole table into an empty partition in the main table. It can be fiddly but very effective.

The other option is to use SQL Server 2016 which allows updateable non clustered columnstore indexes.

  • So there is a name for my problem :) So you suggest to NOT use CCI and use instead NCCI on partitioned table? (By the way thanks for investigating the case) – Liya Tansky Aug 25 '16 at 14:26
  • Yeah just to avoid that VARCHAR. SQL 2016 makes it easier with updateable NCCIs – James Anderson Aug 25 '16 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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