SQL Server 2014 Cardinality Estimator for databases created in lower version

Hi Friends,

In our previous blog we have seen how we can use new SQL Server 2014 cardinality Estimator for newly created databases under SQL Server 2014.Link for that blog is mentioned below;

Today we will see about how we can use New Cardinality Estimator for databases created under previous version of SQL Server. The logic is very simple here, from previous post we know that New Cardinality estimator can be used if database is having compatibility level 120. That means if you have migrated your database from previous version of SQL Server to SQL Server 2014 and you want to use New Cardinality Estimator then you have to change the compatibility level of that database to 120. Let me show you this thing practically.

I have created one new database under SQL Server 2012. I have restored the backup of this database to SQL server 2014 instance. As of now the compatibility level of this database is 110. You can check this by using below query:

Select compatibility_level from sys.databases where name='CETEST'


Now from Query Plan you can see that as of now this database is using Old version of Cardinality Estimator. [Here it will not show you version details]

SELECT TOP 1 * FROM [xtTest]




Now if you want to use new cardinality estimator then change the compatibility level of that database by using below query:

USE [master]
Select compatibility_level from sys.databases where name='CETEST'


After changing compatibility level, now you can check which version of cardinality estimator is in use now:

SELECT TOP 1 * FROM [xtTest]

Now run the above query along with actual execution plan. Right click on left most operators in plan and click on properties:


From above property window, it is clear that we can use new cardinality estimator for databases created in lower versions by restoring them to SQL Server 2014 and changing compatibility mode to 120.




Prince Rastogi

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook


About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published.