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.
73
questions
-1
votes
1
answer
69
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 ...
- 121
-1
votes
1
answer
162
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 [...
- 15.7k
0
votes
0
answers
36
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 ...
5
votes
4
answers
3k
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 ...
- 3,350
1
vote
1
answer
133
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 ...
- 1,913
4
votes
1
answer
393
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 ...
- 1,898
1
vote
2
answers
119
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 (...
- 145
1
vote
1
answer
151
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:
...
- 1,243
0
votes
1
answer
308
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 ...
4
votes
1
answer
913
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 ...
- 2,169
3
votes
1
answer
199
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 ...
- 275
1
vote
0
answers
88
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 ...
- 11
4
votes
1
answer
4k
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 ...
- 151
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 ...
- 191
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 ...
- 259
3
votes
1
answer
1k
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 ...
- 1,453
6
votes
1
answer
137
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 ...
0
votes
1
answer
521
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 ...
- 133
1
vote
1
answer
165
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:
...
- 189
3
votes
3
answers
231
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 ...
- 2,023
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 ...
- 73
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 ...
- 5
-1
votes
1
answer
161
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 ...
- 1,330
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 ...
user129291
6
votes
3
answers
422
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 ...
- 113
2
votes
1
answer
126
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:
...
- 157
1
vote
1
answer
425
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 ...
- 533
5
votes
1
answer
539
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 ...
- 91
0
votes
1
answer
122
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 ...
- 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 ...
- 5,302
1
vote
1
answer
159
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 ...
- 41
0
votes
1
answer
704
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?
- 218
2
votes
2
answers
817
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, ...
- 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 ...
- 2,308
9
votes
1
answer
9k
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/
- 2,072
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 ...
- 25
0
votes
1
answer
536
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 ...
1
vote
1
answer
92
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 ...
- 111
2
votes
1
answer
104
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 "...
- 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 ...
user98113
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 ...
- 163
4
votes
1
answer
235
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 ...
- 4,727
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 ...
- 575
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 ...
- 285
4
votes
1
answer
670
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 ...
- 593
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 ...
- 11
1
vote
2
answers
498
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 ...
- 11
3
votes
1
answer
274
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)
...
- 4,727
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 ...
- 11
3
votes
1
answer
99
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 ...
- 31