Skip to main content
OverflowAI is here! AI power for your Stack Overflow for Teams knowledge community. Learn more

Questions tagged [compatibility-level]

Compatibility level is a SQL Server feature on the database level that can be used to make a database behave like it would be running on an older version of SQL Server than it actually is.

Filter by
Sorted by
Tagged with
0 votes
1 answer
118 views

Why is COMPATIBILITY_LEVEL: 160 using Clustered Index Scan?

Disclaimer: I'm a developer dwelling in databases, so my knowledge is limited We recently upgraded our MSSQL Server from 2012 to 2022 and in that regard also updated the COMPATIBILITY_LEVEL from 110/...
Dumpen's user avatar
  • 109
0 votes
2 answers
110 views

Do deprecated features matter when changing compatibility level?

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. ...
J. Mini's user avatar
  • 401
-1 votes
1 answer
583 views

Sudden query problem with higher compatibilty levels on SQL 2022

We recently migrated a db from a SQL2016 server to a SQL2022 server. When we migrated the database we changed the SQL compatibility level to SQL2022 (160). Life was good. Then suddenly something ...
zu1b's user avatar
  • 121
-1 votes
1 answer
346 views

how to get database settings such as LEGACY_CARDINALITY_ESTIMATION and INTERLEAVED_EXECUTION_TVF

I am moving some database finally to compatibility level 150 using the script below USE [master] GO ALTER DATABASE [DB1] SET COMPATIBILITY_LEVEL = 150 GO USE [...
Marcello Miorelli's user avatar
0 votes
0 answers
43 views

Backward compatibility for filestream databases

I have a database on a filestream enabled instance and with file system tables on SQL Server 2019. I am able to generate scripts for schema and data permitting restore on earlier SQL Server versions ...
franklwambo19's user avatar
5 votes
4 answers
5k views

Does upgrading the database compatibility level post migration result in any breaking results?

Link: https://learn.microsoft.com/en-us/sql/database-engine/install-windows/change-the-database-compatibility-mode-and-use-the-query-store?view=sql-server-ver15 I am migrating sql server from 2016 to ...
variable's user avatar
  • 3,634
1 vote
1 answer
186 views

Referencing a system function from a DB with a lower compatibility level

I need to be able to parse a string array with STRING_SPLIT on a SQL 2016 instance. The vendor has required the one user DB we have access to be set with a compatibility level of 100 while the system ...
MattyZDBA's user avatar
  • 1,933
4 votes
1 answer
508 views

Changing SET options within cursor - Different behavior based on compatibility level

I have found a difference in behavior on a piece of code between database compatibility levels and wondered what the cause of this is. Below is a simple example that iterates through a tally table and ...
SE1986's user avatar
  • 1,970
1 vote
2 answers
146 views

Compatibility levels and reported engine versions

I'm seeking a bit of clarification around SQL Azure versions and upgrades. Upon trying to use some specific functionality it came to my attention that the compatibility level of the database was 100 (...
slugster's user avatar
  • 145
1 vote
1 answer
156 views

Could database with compat level 100 support columnstore index?

I have an instance which runs on SQL Server 2017 Developer Edition instance. I restored database ContosoRetailDW which had compatibility level 100. After restore operation, I run the following query: ...
Rauf Asadov's user avatar
  • 1,273
0 votes
1 answer
497 views

Can I change database compatibility level from 80 to 90 or higher with log shipping?

I have a database on SQL 2008 R2 with compatibility level 80 and I want to change it to 90 or 100. I assumed that after changing, I may find a regression in our application's performance. Many backups ...
Ayman Farouk's user avatar
4 votes
1 answer
1k views

Compatibility mode change does not reflect on secondaries

I was of belief that changing compatibility mode on primary database in database mirroring would make the change for mirror database. But it proved to be wrong. I queried using sys.databases on ...
BeginnerDBA's user avatar
  • 2,170
3 votes
1 answer
272 views

Is there a way to only allow SQL Server 2014 syntax/functions to be used in SQL Server 2019?

We're developing against SQL Server 2019 currently, but we have a need to deploy the same set of SQL scripts to a SQL Server 2014 database. For example, in SQL Server 2019 a built-in function called ...
TtT23's user avatar
  • 275
1 vote
0 answers
90 views

Incorrect Estimates (CE Model 130) on uniqueidentifier columns

We want to change our application from compatibility level 100 to 130. As part of the tests, we came across a curious problem regarding estimates on uniqueidentifier columns. The select is very ...
Lucien's user avatar
  • 11
4 votes
1 answer
5k views

Changing to compatibility level 150 makes SQL Server consume all available memory

We're moving an existing DB to a new SQL Server 2019 instance. If we change the compatibility level to 150, nothing works. Task manager shows that sql server service is consuming all available ...
Luis Abreu's user avatar
9 votes
1 answer
1k views

Is there a way to easily validate that a SQL Script is compatible with a specific SQL Server version?

I am currently working on a project that needs to operate in different SQL environments. Most of my developers have SQL Server 2017. The application runs in SQL Azure, and the client has recently ...
Joe Brinkman's user avatar
2 votes
2 answers
1k views

How do you get the default compatibility level in Azure SQL Database from T-SQL?

In on-prem SQL Server you can check the server version and multiply by 10 to get the maximum compatibility level supported by the server, or check the compatibility level of the model database to get ...
Mark Raymond's user avatar
3 votes
1 answer
2k views

Ambiguous column error on single table

I have the following code that is generated by an unsupported app SELECT ID, TYPE, ID FROM my_table t1 ORDER BY TYPE, ID I can't change this, now it used to work fine on SQL2008R2 or below on DB's ...
Paul's user avatar
  • 1,453
6 votes
1 answer
146 views

Issue with variable length string between compatibility level 80 and 100

I was getting tons of issues related with a specific scenario on my current enterprise. We have a SQL Server 2008 and we want to move to SQL Server 2016, so we are moving the compatibility from 80 to ...
Pablo Guzman Labra's user avatar
0 votes
1 answer
638 views

Changing compatibility level from 2008 to 2016

I have a database with hundreds of stored procedure, views, triggers, function, ... The database Compatibility Level is 2008 (100) and we would like to move it to 2016 (130). The SQL Server version ...
Lenny32's user avatar
  • 133
1 vote
1 answer
166 views

Could anyone please help me understand MSSQL Compatibility Level?

I have a task to upgrade a database from SQL Server 2005 to SQL Server 2012. So I took backup of the old database and simply restored it onto SQL Server 2012 (SP3). Executed query on New database: ...
Irfan's user avatar
  • 189
3 votes
3 answers
251 views

Backing up a SQL 2008 DB, then restoring, gives a SQL 2005 (compatibility level 90) DB

We have four different databases on a SQL Server 2008 (not SP2 - don't ask me why) database server that have an odd problem. When listed from sys.databases, these databases report that their ...
Laughing Vergil's user avatar
7 votes
1 answer
1k views

Database Compatibility Requirement for Transactional Replication

Does the subscriber "within two version requirement" for transactional replication from this MSDN article apply to the SQL Server Instance, Database Compatibility Level, or Both? For example, would ...
jhase511's user avatar
0 votes
1 answer
35 views

SQL 2005 SP2 Database on SQL 2012 Instance

I've an SQL Server 2005 SP2 instance that contain old databases that are not compatible with other SP of SQL Server 2005. Those databases are on physical server that don't have any space to backup ...
Guidjome's user avatar
-1 votes
1 answer
172 views

Restoring AdventureWorks2014 Database to SQL Server 2017

I restored AdventureWorks2014 database from SQL Server 2014 to SQL Server 2017. (I restored it twice, I changed the name on the 2nd restore, of course) It works fine. Now I tried to change the ...
Edgar Allan Bayron's user avatar
1 vote
3 answers
2k views

SQL Server Upgrading Compatibility Level from 2005 to 2012

We have old database in SQLServer 2012 Enterprise in Compatibility mode: 90 - SQL Server 2005 . What are the consequence of changing database compatibility mode from 90 into 110-SQL Server 2012? Many ...
user avatar
6 votes
3 answers
428 views

How to Get Median Value From Row in SQL Server 2008 R2 Without Using Pivot and Unpivot Table

Create Table Script create table temp ( id int identity(1,1), a decimal(6,2), b decimal(6,2), c decimal(6,2), d decimal(6,2), e decimal(6,2), f decimal(6,2), g ...
Isai Selvan's user avatar
2 votes
1 answer
260 views

Can changes to cardinality optimization be made without taking users offline?

I have an old SQL Server database that has been migrated a few times since < 2005. Some queries are running very slowly. I have been advised that the following changes could help performance: ...
m-smith's user avatar
  • 157
1 vote
1 answer
434 views

SQL Server Migrate Database - Set Compatibility Level

We are planning on migrating from SQL Server 2008R2 to SQL Server 2014 (or 2016). One 3rd party application is not supported after version 2008R2. Is it OK to migrate this application database to ...
Kevin's user avatar
  • 533
5 votes
1 answer
622 views

Do SQL Server Compatibility Levels Introduce Old Security Vulnerabilities

Can anyone help settle what I thought was a simple question. A fellow DBA friend and I were talking about compatibility levels in general, and their relation to security specifically. He posited ...
SurfRodeoDBA's user avatar
0 votes
1 answer
126 views

SQL Server 2012 (compability level 110) and SQL Server Native Client 10

I've upgraded my SQL Server 2008R2 to SQL 2012. I will set the compability level to 110 (2012) to all databases. But I'm wondering if it will create some problems for clients that connect to this ...
liotims's user avatar
  • 178
0 votes
1 answer
4k views

"Cannot open database version 706. Upgrade the database to the latest version" - when trying to use availability database for read-only access

I have Availability Group with below config: replica-01: SQL Server 2012 (primary) replica-02: SQL Server 2012 (secondary synchronous) replica-03: SQL Server 2016 (secondary asynchronous) replica-01 ...
Aleksey Vitsko's user avatar
1 vote
1 answer
161 views

why will compatibility mode change amount of storage iops

We recently upgraded from SQL Server 2012 to SQL Server 2016. We noticed an unusual amount of IOps on our Dev and Integration servers, but not on production. The only difference is that production ...
siberiaal's user avatar
0 votes
1 answer
774 views

Should I upgrade compatibility level of MS Report Server database

Recently upgraded from SQL Server 2008 to SQL Server 2012. The MS Report Server databases did not automatically upgrade compatibility level. Should I force the upgrade?
Ram's user avatar
  • 218
2 votes
2 answers
879 views

Do SQL service packs effect SQL Compatibility mode?

We will soon be upgrading from a SQL 2008 R2 instance to a SQL 2016 instance. One of our databases has an application which our supplier says is compatible up to SQL 2012 SP1, but not SQL 2012 SP2, ...
Porkster's user avatar
  • 132
3 votes
3 answers
2k views

Generate backwards compatible CREATE DATABASE script with all objects?

I'm working on SQL Server 2016, and I want to deploy a database I have created to Sql Server 2012 SP1. I found I was unable to create a backup in v2016 and restore to v2012. So instead in the v2016 ...
Zach Smith's user avatar
  • 2,360
9 votes
1 answer
11k views

pgAdmin and PostgreSQL compatibility matrix [duplicate]

Has anyone ever put together a version compatibility matrix for pgAdmin versions and PostgreSQL versions? https://www.pgadmin.org/download/windows.php https://www.postgresql.org/download/windows/
JustBeingHelpful's user avatar
1 vote
2 answers
3k views

Change compatibility Level on 80 to 100

We are about move all databases from a SQL server 2008r2 (Standard edition) to a 2016 standard edition server. Many databases have its compatibility level set at 80, wich is not supported on the 2016 ...
nunopacheco's user avatar
0 votes
1 answer
567 views

Can i alter a database to single user which is configured in log shipping

We are using a production database (SQL Server 2012) and database compatibility mode is 90 (SQL Server 2005). Can I alter the database to single user mode and change to 110? What will be the impact ...
user108254's user avatar
1 vote
1 answer
98 views

What changes SQL Server Compatibility Level changes?

We had a database and lots of functionality that worked fine in SQL Server 2008 R2. We recently upgraded both our development and production servers to SQL Server 2014. Unbeknownst to us, our ...
Pulsehead's user avatar
  • 111
2 votes
1 answer
114 views

SQL 2012: Executed generated DB script in SQL 2008 R2

I generated a script (using the wizard from SSMS 2012) to produce a SQL 2008 R2 compatible database script. I ran this script in a SSMS 2008 R2 instance and i got a bunch (30) of these possibly "...
Raidenlee's user avatar
  • 153
2 votes
2 answers
2k views

Error with sys.dm_db_index_physical_stats

I am executing a select statement to retrieve fragmentation percentage of indexes and facing below error. SELECT A.NAME,B.NAME,C.avg_fragmentation_in_percent FROM SYS.TABLES A INNER ...
user avatar
5 votes
1 answer
3k views

Reverting Compatibility Mode 120 to 100

We recently upgraded the compatibility level (CL) of our production databases from 100 to 120. Within a couple of days, users started complaining of increased wait times and general slowness. It was ...
Tim J's user avatar
  • 163
4 votes
1 answer
247 views

Howto query all files, their fill level and additional drive info with one query?

I need a query that puts together the following results: one row per database file across all databases additional columns with information about the locigal drive the file is on information about ...
Magier's user avatar
  • 4,787
1 vote
3 answers
2k views

Converting old SQL 2000 Non-Ansi code to SQL 2012 compatible

We have a legacy application that is only used for a read only reference. It is using SQL Server 2000. We want to migrate its database to SQL version 2012, however we can't upgrade the software and ...
Roni Vered's user avatar
0 votes
1 answer
2k views

phpmyadmin to SQL Server

I have scripts generated from a MySQL database using phpmyadmin to create database and insert data. The scripts were exported using compatible with MS SQL. Needless to say they are not compatible at ...
user5495269's user avatar
4 votes
1 answer
712 views

What is Collation Compatibility_60_406_30001 in SQL Server

We have an SQL Server 2014 (recently upgraded from 2008 R2) instance where one of the databases has some strange settings for collation: In the Properties window for the database we have the ...
GHauan's user avatar
  • 603
1 vote
3 answers
2k views

Changing Compatibility level from 90 to 120 in sql 2014 migration

I have SQL 2008R2 server to migrate to SQL2014 Server. A side by side upgrade is what we are thinking for few databases in SQL 2008R2 which are having compatibility level of 90 or 80. Can I migrate ...
Ramey's user avatar
  • 11
1 vote
2 answers
535 views

SQL Server compatibility level

I inherited a database server with SQL Server 2012 Enterprise Edition. All the databases (except for the system databases) are at the 90 compatibility level. How do I justify updating them to 110 ...
Mary W's user avatar
  • 11
3 votes
1 answer
280 views

Translate query using APPLY to run under compatibility level 80

I want to use the following query: SELECT SUBSTRING(qt.TEXT, ( qs.statement_start_offset / 2 ) + 1, ( ( CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ...
Magier's user avatar
  • 4,787