Recognising the SQL Server Version

Something I often need to do is recognise which SQL Server version I am working on. For example to separate SQL 2000 compatible code from SQL Server 2005 compatible code. I'll show you some of the options to get the version number... Starting from the ugliest to the most elegant... The first option is calling the extended procedure xp_msver

exec xp_msver 'ProductVersion'

Which give you this result:

Index	Name	Internal_Value	Character_Value
2	ProductVersion	524288	8.00.2039

xp_msver can give you quite a large amount of version information about your system, but it is not very pretty to use in your code. There is also no option to get a simple version number. Another option is to call @@version

select @@version

Which gives you something that looks like:

Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

This is of course not very practical when you are programming. So another option is fetching the version number from the server properties.

select serverproperty('productversion')

And the result looks a bit like this:

9.00.3054.00

This is already more practical. But still difficult to use in your programs. My favorite option is using the @@microsoftversion function. This function returns a

select @@microsoftversion / 0x01000000 

And the result of this query is a very simple:

9

The following query shows you the possible values for the recent SQL Server versions (I don't have a SQL 6 - 7 test environment anymore, so I don't know if it works on those.)

SELECT CASE @@microsoftversion/ 0x01000000 
         WHEN 8 THEN 'sql 2000'
         WHEN 9 THEN 'sql 2005'
         WHEN 10 THEN 'sql 2008'
       END

Although this was a simple topic, I hope you found it interesting.