Tuesday 19 April 2011

TSQL : SQL Server Version detection

As part of rollout scripts I am automating the detection of of the SQL Server version.
The current use for this is to decide whether indexes should carry included columns (a 2005+ feature), but I can think of many more.
IF (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER)) >=9
BEGIN
PRINT 'SQL 2005 or greater detected'
END
Other properties such as Edition could be used to determine whether edition specific features e.g Page Compression, Resource Governor (SQL 2008 Enterprise)
SELECT
 CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER) AS MajorVersion
,SERVERPROPERTY('ProductVersion') AS ProductVersion
,CASE LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1)
 WHEN 8 THEN 'SQL 2000'
 WHEN 9 THEN 'SQL 2005'
 WHEN 10 THEN 'SQL 2008'
END AS ProductVersion
,SERVERPROPERTY('Edition') AS Edition
,SERVERPROPERTY('ProductLevel') AS ProductLevel

No comments: