How to identify SQL Server 2008 or higher

Question

I need to determine programmatically if the database supports geography data type and spatial indexes. These features were introduced in 2008. I also need to determine if the CLR is enabled, as these functions rely on it. What is the most reliable way to do this?

Answer 1

SQL Server 2008 - 10.x

You can use SERVERPROPERTY in SQL and query sys.configurations

SELECT PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS varchar(20)), 3) AS MajorVersion, value_in_use FROM sys.configurations WHERE name = 'clr enabled'; 

Edit: Added CAST

Answer 2

analyze the following:

 select @@VERSION SELECT * FROM sys.configurations WHERE name = 'clr enabled' SELECT compatibility_level from sys.databases where name=db_name() 

as:

 select CASE WHEN LEFT(@@VERSION,25)='Microsoft SQL Server 2008' THEN 'Yes' ELSE 'NO' END AS OnSQLServer2008 ,CASE value WHEN 0 THEN 'No' ELSE 'Yes' END AS [clr_enabled] ,(SELECT CASE compatibility_level WHEN 100 then 'Yes' ELSE 'No' END from sys.databases where name=db_name()) AS SQLServer2008CompatibilityMode FROM sys.configurations WHERE name = 'clr enabled' 

output:

 OnSQLServer2008 clr_enabled SQLServer2008CompatibilityMode --------------- ----------- ------------------------------ Yes No No (1 row(s) affected) 

Answer 3

You can use SELECT @@VERSION , which returns a rather verbose string.

It’s easier to look at the database compatibility level with

 select compatibility_level from sys.databases where name=db_name() 

This returns a numeric value. Common values ​​such as:

80 = SQL Server 2000

90 = SQL Server 2005

100 = SQL Server 2008

This gives an additional advantage in verifying that the database on the server is at the required level, and not only on the server itself on which a particular version of the system is installed.

Answer 4

Use the query below

 SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion, SERVERPROPERTY('ProductLevel') AS ProductLevel, SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('EngineEdition') AS EngineEdition; 

The following are versions of the Version.