Columnstore indexes and compatibility

Question

Columnstore indexes and compatibility

peter.cox

Ten Centuries

Points: 1067
More actions
February 11, 2020 at 11:07 am

#3724653

Hi
I know, back in the day, the CSIs on SQL 2012 stopped the table being updated/inserted etc
I know this also changed in SQL 2016
Now, we have a SQL 2016 server, with a massive DB in 2012 compatibility (don't ask...)
Can I set up a CSI, and it will act as a 2016 CSI? First impressions seem to be imply it can - I seem to be able to insert/update the table etc. But I can't find anything to confirm this online....

Any one got a definitive proof it's okay on 2016, even in 2012 mode?

many thanks
February 26, 2020 at 4:05 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 1

I can't find anything saying one way or the other in Microsoft's documentation or on the real source of information, Niko's blog. I'd say if it tests out, it works. Test it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

frederico_fonseca SSCoach Points: 16017 More actions · Answer 2

in 2016 they work fine - I have a DB where most of of the tables have a CCI's and no issues with them.

but in compat 2012 .... test it - easy to do with a small test table

This reply was modified 4 years, 2 months ago by frederico_fonseca.

peter.cox Ten Centuries Points: 1067 More actions · Answer 3

Cheers Grant

Testing appears to be working, so I'm going for it. Just wondered if anyone else had tried.

I may ask a loaded question at one of your SQL Bits sessions...

@Frederico - SQL 2016 works fine, when compatibility is 130. It's more when the compatibility is 110.

This reply was modified 4 years, 2 months ago by peter.cox.
This reply was modified 4 years, 2 months ago by peter.cox.

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 4

peter.cox wrote:

Cheers Grant
Testing appears to be working, so I'm going for it. Just wondered if anyone else had tried.
I may ask a loaded question at one your SQL Bits sessions...
@Frederico - SQL 2016 works fine, when compatibility is 130. It's more when the compatibility is 110.

Oh, boy!

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Grant Fritchey wrote:

peter.cox wrote:
Cheers Grant
Testing appears to be working, so I'm going for it. Just wondered if anyone else had tried.
I may ask a loaded question at one your SQL Bits sessions...
@Frederico - SQL 2016 works fine, when compatibility is 130. It's more when the compatibility is 110.
Oh, boy!

Shouldn't be a problem for a former bubble-head. If someone asks a loaded question, heh... give them a load for an answer. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)