How to determine which version of SQL Server is running using T-SQL

Question

I need help using T-SQL to figure-out the version of SQL Server running and execute different code sets based on weather SQL Server 2000 or Sql Server 2008 is running.

Solution 1

SELECT @@VERSION?

Or one of the SERVERPROPERTY options?

Solution 2

 SELECT SERVERPROPERTY('productversion')
       , SERVERPROPERTY ('productlevel')
       , SERVERPROPERTY ('edition')

Solution 3

@@VERSION / SERVERPROPERTY

But you should also check

exec sp_dbcmptlevel 'dbname'

To ensure a certain feature works at the database's compatibility level.

Solution 4

Just query the database - there is a @@VERSION property:

SELECT @@VERSION

Returns version, processor architecture, build date, and operating system for the current installation of SQL Server.

As mentioned on the page, since all of this data is returned in one varchar, you can use the SERVERPROPERTY function to retrieve only the version:

SELECT SERVERPROPERTY('ProductVersion')

Solution 5

SELECT SERVERPROPERTY('productversion')

The digits before the first period will give you the major version: 10 = 2008, 9 = 2005, 8 = 2000.

View more solutions

Comments

[Joe Stefanelli]

That's an ugly string to deal with programatically.

[ZygD]

Good thinking about sp_dbcmptlevel. This matters more in some cases