How to determine SQL Server 2008 or above

Question

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

Answer 1

SQL Server 2008 is 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

parse the following:

select @@VERSION

SELECT * FROM sys.configurations WHERE name = 'clr enabled'

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

like:

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 fairly verbose string.

Easier is to look at the DB compatibility level using

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

This returns a numeric. Frequent values are such as:

80 = SQL Server 2000

90 = SQL Server 2005

100 = SQL Server 2008

This has the added benefit of checking that the database on the server is at the required level, not just that the server itself it running a particular system version.

Answer 4

Use the below mentioned query

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

For the bellow mentioned Versions to find out.