16

I have a database Mydatabase created in SQL Server 2008 R2. I have upgraded to SQL Server 2012.

I was trying to execute below query to calculate percentile

select Distinct [KEY],PERCENTILE_CONT(0.25)  within group(order by EachPrice)
OVER(Partition By [KEY]) As Q1,PERCENTILE_CONT(0.50)  within group(order by EachPrice)
OVER(Partition By [KEY]) As Q2,
PERCENTILE_CONT(0.75)  within group(order by EachPrice)
OVER(Partition By [KEY]) As Q3,
PERCENTILE_CONT(1)  within group(order by EachPrice)
OVER(Partition By [KEY]) As Q4
from Mydatabase

but I am getting an error stating that

Msg 10762, Level 15, State 1, Line 1
The PERCENTILE_CONT function is not allowed in the current compatibility mode. It is only allowed in 110 mode or higher.

  1. Can I change compatibility mode to 110?
  2. What are the implications of changing compatibility mode from 100 to 110?

Please advice

migrated from stackoverflow.com Mar 7 '14 at 20:20

This question came from our site for professional and enthusiast programmers.

  • OMFG! "SQL Server 2014 includes substantial improvements to the component that creates and optimized query plans." But` I haven't been benefiting from that at all for the past 3 years because my compatibility_level is still at 100. Fantastic! – Simon_Weaver Apr 3 '17 at 3:09
9

Have a look at the following link:

ALTER DATABASE Compatibility Level

Scroll down and you will see the section "Differences Between Lower Compatibility Levels and Level 110" and identify if any of these items will affect you or not. If not, then just change the level to 110.

5

There is also the Upgrade Advisor which will help you identify anything in your DB which might cause problems when changing the compatibility level (basically a semi-automated way of doing what @steoleary has suggested which reduces the chances of you missing anything).

  • Data Migration Assistant has now replaced Upgrade Advisor, apparently. – MGOwen Apr 12 '17 at 5:37
  • Yeah this was for 2012 so not surprised they've changed the name and/or functionality. I've been out of the loop on upgrading DBs for quite some time as I rarely have much to do with DBs these days - not even sure what the latest version is! – Steve Pettifer Apr 12 '17 at 8:45
3

Microsoft Data Migration Assistant helps you quickly and easily find any issues that may prevent or complicate an upgrade.

If it finds no issues (or if it does, and you've addressed them) you can simply upgrade by running these commands, one at a time:

USE master
go
ALTER DATABASE [yourdatabasesname]  SET SINGLE_USER     WITH ROLLBACK IMMEDIATE
go
ALTER DATABASE [yourdatabasesname]  SET COMPATIBILITY_LEVEL = 110    -- 130=SQL Server 2016, 120=2014, 110=2012
go
ALTER DATABASE [yourdatabasesname]  SET MULTI_USER
go

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.