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
5 votes
3 answers
516 views

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

Link: https://docs.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 ...
user avatar
  • 2,371
1 vote
1 answer
48 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 ...
user avatar
  • 1,913
4 votes
1 answer
191 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 ...
user avatar
  • 1,379
0 votes
2 answers
55 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 (...
user avatar
  • 135
1 vote
1 answer
89 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: ...
user avatar
  • 1,163
0 votes
1 answer
130 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 ...
user avatar
4 votes
1 answer
563 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 ...
user avatar
  • 2,137
3 votes
1 answer
85 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 ...
user avatar
  • 275
1 vote
0 answers
74 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 ...
user avatar
  • 11
4 votes
1 answer
2k 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 ...
user avatar
9 votes
1 answer
773 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 ...
user avatar
2 votes
3 answers
918 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 ...
user avatar
3 votes
1 answer
982 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 ...
user avatar
  • 1,433
6 votes
1 answer
116 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 ...
user avatar
0 votes
1 answer
343 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 ...
user avatar
  • 133
1 vote
1 answer
161 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: ...
user avatar
  • 189
3 votes
3 answers
190 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 ...
user avatar
7 votes
1 answer
933 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 ...
user avatar
0 votes
1 answer
34 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 ...
user avatar
-1 votes
1 answer
142 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 ...
user avatar
1 vote
3 answers
1k 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
387 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 ...
user avatar
2 votes
1 answer
47 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: ...
user avatar
  • 157
1 vote
1 answer
383 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 ...
user avatar
  • 503
5 votes
1 answer
385 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 ...
user avatar
0 votes
1 answer
103 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 ...
user avatar
  • 178
0 votes
1 answer
3k 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 ...
user avatar
1 vote
1 answer
154 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 ...
user avatar
0 votes
1 answer
580 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?
user avatar
  • 218
2 votes
2 answers
718 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, ...
user avatar
  • 132
3 votes
3 answers
1k 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 ...
user avatar
  • 2,222
9 votes
1 answer
6k 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/
user avatar
  • 2,005
1 vote
2 answers
2k 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 ...
user avatar
0 votes
1 answer
467 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 ...
user avatar
1 vote
1 answer
82 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 ...
user avatar
  • 111
2 votes
1 answer
87 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 "...
user avatar
  • 155
1 vote
1 answer
875 views

SQL Server prevents me from changing compatibility level [closed]

I have a SQL Server DB that is in a SQL Server 2008 Express instance that will not let me change the compatibility level beyond SQL Server 2005 (It was originally created in 2005) either through the ...
user avatar
  • 205
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
2k 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 ...
user avatar
  • 163
4 votes
1 answer
202 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 ...
user avatar
  • 4,605
1 vote
2 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 ...
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 ...
user avatar
4 votes
1 answer
608 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 ...
user avatar
  • 563
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 ...
user avatar
  • 11
1 vote
2 answers
453 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 ...
user avatar
  • 11
3 votes
1 answer
261 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) ...
user avatar
  • 4,605
1 vote
2 answers
1k views

Compatibility level 100 on SQL Server 2008 R2 [duplicate]

I'm upgrading my databases to compatibility level 100 on SQL Server 2008 R2. My next step is to check if my stored procedures, functions, views, triggers are still working properly. Running each of ...
user avatar
  • 11
3 votes
1 answer
94 views

Database compatibility level [duplicate]

We are on SQL Server 2008 R2 and have DBs compatibility level set to SQL 2000 or 2005. What are differences in compatibility level across the versions ? Are there any advantages changing it to 2008 ...
user avatar
  • 31
0 votes
1 answer
764 views

Do I need to rebuild indexes when upgrading a DB from 2008 to 2012?

I can't find anything on BOL that indicates I need to rebuild indexes or statistics after the migration and compatibility level upgrade of a database. But I have read a few articles/answers around the ...
user avatar
-1 votes
2 answers
2k views

database Compatibility 90 to 100 changing causes any problem

We are planning to change Compatibility level of databases from 90 to 100. Would this cause any impact on application side? If yes then please guide me, what steps or measures we need to look upon ...
user avatar
  • 1