CODEWITHSUNDEEP
sql-serverversions
Nermeen Mattar
Nermeen Mattar

Reputation: 19

How to know in which version SQL Server started to support a certain function?

I want to check if the SQL scripts I wrote will run successfully on SQL Server regardless of the SQL Server version (or starting from a certain SQL Server version).

For example: how can I check if the following SQL statement will work in old SQL Server versions?

UPDATE TABLE_A
SET TABLE_A.COL_1= TABLE_B.COL_1 
FROM TABLE_A AS TABLE_A
LEFT JOIN
TABLE_B AS TABLE_B
ON (TABLE_A.COL_ID=TABLE_B.COL_ID);

Upvotes: 1

Views: 857

Answers (1)

Andrea
Andrea

Reputation: 12355

Changing the compatibility level allows you to "simulate" an older/different version of SQL Server. So you can change the compatibility level before running you query to determine if a given query will run correctly with a certain version of SQL Server.

Here is the syntax:

ALTER DATABASE [YourDBname] SET COMPATIBILITY_LEVEL = x

where x is the compatibility level:

  • 140: SQL Server vNext
  • 130: SQL Server 2016
  • 120: SQL Database
  • 120: SQL Server 2014
  • 110: SQL Server 2012
  • 105: SQL Server 2008 R2
  • 100: SQL Server 2008
  • 90: SQL Server 2005
  • 80: SQL Server 2000

See MSDN for more details

For example the string_split() function has been introduced in SQL Server 2016 (compatibility level 130), therefore running a query containing string_split() with a compatibility level lower than 130 would result in an error:

ALTER DATABASE [YourDBname] SET COMPATIBILITY_LEVEL = 130
go
select value from string_split('1_2_3_4','_')

ALTER DATABASE [YourDBname] SET COMPATIBILITY_LEVEL = 120
go
select value from string_split('1_2_3_4','_')

The first query will execute correctly, while the second will give the following error:

Msg 208, Level 16, State 1, Line 9 Invalid object name 'string_split'.

Edit: As pointed out by Damien_The_Unbeliever in comments there are some limitations with these approach. Each version of SQL Server can support only a limited number of compatibility level values.

According to MSDN here are supported compatibility level values for each version (more info here):

+--------------------+--------------------------------------+
|      Product       | Supported Compatibility Level Values |
+--------------------+--------------------------------------+
| SQL Server vNext   | 140, 130, 120, 110, 100              |
| SQL Server 2016    | 130, 120, 110, 100                   |
| SQL Database       | 130, 120, 110, 100                   |
| SQL Server 2014    | 120, 110, 100                        |
| SQL Server 2012    | 110, 100, 90                         |
| SQL Server 2008 R2 | 100, 90, 80                          |
| SQL Server 2008    | 100, 90, 80                          |
| SQL Server 2005    | 90, 80                               |
| SQL Server 2000    | 80                                   |
+--------------------+--------------------------------------+

According to this table with the latest versions of SQL Server (2016, 2014) you can go back till SQL Server 2008, but you can't go back to SQL Server 2005 or 2000

Upvotes: 1

Related Questions