0

On my SQL Server 2019 machine, I have a database that is running on the lowest supported compatibility level (I believe that's equivalent to SQL Server 2008). I want to switch it to the very highest. Do I need to check for deprecated features first?

This feels like it should be a duplicate, but I've not managed to find anything mentioning both topics.

J. Mini
  • 1,161
  • 8
  • 32

2 Answers2

3

Not exactly, deprecated features are planned to stop working but discontinued features actually do not work in the later version.

Data migration assistant will be very helpful & also the comment by Martin Smith:

You need to be aware that some code might break due to subsequent breaking changes. e.g. SELECT {d '2023-01-01'} + CAST('08:00:00.000' AS TIME) will execute without errors on a 2008 compat database. Or there are other breaking or behavioural changes that you might encounter when raising compat level

https://learn.microsoft.com/en-us/sql/database-engine/discontinued-database-engine-functionality-in-sql-server?view=sql-server-ver16

Paul White
  • 94,921
  • 30
  • 437
  • 687
Stephen Morris - Mo64
  • 4,656
  • 1
  • 10
  • 18
2

Beware of the inligning UDF.... Sometime because of the Intelligent Query Processing, that transform UDF into SQL code integrated to the query, the behaviour changes and an error could be diplayed (very rare)...

The workaround in this case is to add the "WITH INLINE = OFF" option...

Another trouble is the change made for the cardinality estimator in the 2012 version. Also workaround exists for that most common problem...

SQLpro
  • 550
  • 2
  • 8