Implications of changing compatibility mode in SQL Server from 100 to 110

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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).