November 5, 2020 at 8:54 am
Hi everyone. I asked this question previously on other forum, but didn't get satisfied answer. I hope I could find the answer here. So, I have an instance which runs on SQL Server 2017 Developer Edition instance. I restored database ContosoRetailDW which had compatibility level 100. After restore operation, I run the following query:
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactOnlineSales ON dbo.FactOnlineSales
The columnstore index was successfully created.
My question is related to the compat level. As far as I know, the columnstore index concept itself was came out in SQL Server 2012. Moreover, the clustered columnstore index was released in SQL Server 2014. How come database with compat level 100 support clustered columnstore index? It seems that database still inherits features of the current instance
November 5, 2020 at 2:09 pm
Every db on an instance is upgraded internally to the current SQL version. A backward compatibility level only affects certain things, not absolutely everything. Thus, yes, it's possible that a future feature might be usable at a lower compat level.
You'll sometimes also see something similar when querying one database from another. Say you have db_current, at the current version level, and db_prior, at a lower compat level. If you are in db_current, some newer things tend to work even if you are joining to db_prior. But if your current db is db_prior, then those newer things are not available, even if you are joining to db_current.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply