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?

score 5 · Accepted Answer · answered Jan 25 '12 at 16:49

5

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

answered Jan 25 '12 at 16:49

gbn

422,506
82
585
676

Msg 8116, Level 16, State 1, Line 1 Argument data type sql_variant is invalid for argument 1 of parsename function. – KM. Jan 25 '12 at 16:50
`SERVERPROPERTY('ProductVersion')` shows what is installed, not the compatibility mode being run. The software relying on `SERVERPROPERTY('ProductVersion')` will fail if running in a old compatibility mode that does not support the Geography data type. – KM. Jan 25 '12 at 17:03
@JackAllan: I can't test this, but msdn says "Running value currently in effect for this option" – gbn Jan 25 '12 at 18:02

KM. · Answer 2 · 2012-01-25T16:54:45.020

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)

edited Jan 25 '12 at 16:54

answered Jan 25 '12 at 16:45

KM.

101,727
34
178
212

Was just looking if there is something that returns a more simple result that @@VERSION - it gives a messy string such as "Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) Apr 22 2011 19:23:43 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)" – Jon Egerton Jan 25 '12 at 16:47
Yes parsing @@version I thought of but as Jon says this could be a bit messy is there a more reliable way to determine the version? – Jack Allan Jan 25 '12 at 16:51
@Jon Egerton, @@VERSION is not ideal, so I added in compatibility mode check. It is more accurate than @@VERSION. If database is 2008, but running in 2005 mode then Geography data type will not exist. – KM. Jan 25 '12 at 16:55
What happens when you upgrade to SQL 2012? If you just look for `==2008` the code will break and I'm pretty sure the upgrade advisor won't warn you. – Code Magician Jan 25 '12 at 17:00
@M_M, this is just a simple example, you can always use `>= 100` type logic on the compatibility_level. – KM. Jan 25 '12 at 17:06

score 0 · Answer 3 · answered Jan 25 '12 at 16:50

0

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.

answered Jan 25 '12 at 16:50

Jon Egerton

40,401
11
97
129

Is compatibility_level required to be 100 for teh new types? – gbn Jan 25 '12 at 16:54
I read somewhere (on one of the dozens of blogs) that, while it was available, it wasn't a good idea to use less than 100. Just been looking for the item (2 years later!!) and can't put my hand on it. There can be replication issues between levels but I thought there were other more concrete things than that. – Jon Egerton Jan 25 '12 at 18:17

score 0 · Answer 4 · answered Jan 25 '12 at 17:19

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.

enter image description here

How to determine SQL Server 2008 or above

4 Answers4