tsqlt- Not working with Non Clustered Columnstore Index

Question

I am trying to create a unit test on a procedure which touches a non-clustered columnstore table.

I create a fake table and then tried both dropping and disabling the CStore index before inserting test data but when I try to run the test, it always errors out saying "INSERT statement failed because data cannot be updated in a table that has a nonclustered columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, and then rebuilding the columnstore index after INSERT has completed"

This is the concerned code snippet within the tsqlt procedure

EXEC [tSQLt].[FakeTable] @TableName = N'CommonDM.AccountBalances';

ALTER INDEX [IX_CS_FinanceDM_AccountBalances] ON [CommonDM].[AccountBalances] DISABLE

INSERT INTO commondm.AccountBalances
        ( MK_DatesID_TradeDate ,
          MK_CurrenciesID_CurrencyCode ,
          MK_UCRAccountsID_AccountID ,
          EndOfDayAccountBalanceEUR ,
          ClosingRateEURDKK ,
          ClosingRateEURAC ,
          DW_BatchID ,
          EndOfDayAccountBalanceAC ,
          RevaluationDKK ,
          RevaluationEUR ,
          MK_BusinessLinesID_BusinessLineID ,
          MK_UCRCounterpartsID_CounterpartID
        )
VALUES  ( 20150325 , -- MK_DatesID_TradeDate - int
          3 , -- MK_CurrenciesID_CurrencyCode - int
          25891201 , -- MK_UCRAccountsID_AccountID - int
          -3577.82776605942, -- EndOfDayAccountBalanceEUR - float
          7.46875 , -- ClosingRateEURDKK - float
          4.02910395425365 , -- ClosingRateEURAC - float
          3152289, -- DW_BatchID - int
          -14415.4399998685 , -- EndOfDayAccountBalanceAC - float
          88.6549559991928 , -- RevaluationDKK - float
          14.8043530611986 , -- RevaluationEUR - float
          2 , -- MK_BusinessLinesID_BusinessLineID - int
          31512216  -- MK_UCRCounterpartsID_CounterpartID - int
        )

ALTER INDEX [IX_CS_FinanceDM_AccountBalances] ON [CommonDM].[AccountBalances] REBUILD

Maybe try this: Set a GO after each ALTER and INSERT statement. This is just a wild guess on my part though. — TT.
Adding "GO" won't work because the code segment supplied will be from a tSQLt test i.e. a stored procedure. Can I ask why you need the columnstore index to exist for this test - what impact do you expect it to have on the test outcome? If none, then do you need it at all on the faked table? — datacentricity
The procedure that I am writing the unit test for, queries data from CommonDM.AccountBalances which is a non- clustered columnstore table. I thus, need to fake that table and make test entries. And I can't make test entries unless I drop/disable the index. How else can I make entries to this fake table (derived from the non clustered columnstore table)? If I do not disable, I can't have test data — Saugat Mukherjee
I admit I haven't yet had the need to use FakeTable on a table with columnstore indexes but unless that index has a material impact on the behavior of the procedure under test, can you not just fake the table and insert the test data without adding the columnstore index to the faked table at all? The other thing looking at the order in which you are running your code, once you have faked the table it won't have any indexes. By default FakeTable temporarily creates a copy of the table with no constraints and where all columns allow null so the CS index won't even exist on the faked table — datacentricity
Actually, I've just had a thought... I wonder if this is similar to when we use FakeTable to mock a view, I normally find I need to write the insert as dynamic SQL using EXEC or sp_executesql to get past SQL Server's own validation. Haven't got time to test this theory right now tho, sorry. — datacentricity

Martijn van der Jagt Martijn van der Jagt · Accepted Answer · 2016-05-19T07:03:56

Sorry to be late with an answer to your question. I only now read the question. The answer may be relevant for other visitors.

Fake tables in tSQLt are normal tables, created for testing purposes without the constraints of all foreign keys. So Columnstore indexes will perform normal.

However, in SQL server 2008/2012, you can not modify data in a table with a columstore index.

So you either have to actually DROP and RECREATE indexes, or you may want to create a partitioning scheme for the table. To insert data you load the data in a second (index aligned) table without the columnstore index (DROPped or DISABLEd). Then you add the columnstore index (CREATE or REBUILD), after which you can switch the newly created partition in an empty partition of the targetted table. To modify data you first switch the respective partition out to the second table and DROP or DISABLE the index.

tsqlt- Not working with Non Clustered Columnstore Index

1 Answers