Yesterday I wrote about how we can install SQL Server 2014. Right after the blog post was live, I received a question from the developer that he has installed SQL Server 2014 and attached a database file from previous version of SQL Server. Right after attaching database, he was not able to work with the latest features of Cardinality Estimation. As soon as he sent me email I realize what has happened exactly. When he attached database, the database compatibility was set to still of the earlier version of SQL Server. To use most of the latest features of SQL Server 2014, one has to change the compatibility level of the database to the latest version (i.e. 120).

Here are two different ways how we can change the compatibility of database to SQL Server 2014′s version.

1) Using Management Studio

For this method first to go database and right click over it. Now select properties.

On this screen user can change the compatibility level to 120.

2) Using T-SQL Script.

You can execute following script and change the compatibility settings to 120.

USE [master]
GO
ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 120
GO

 

Well, it is that easy :-)

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL