3 ways to find SQL Server version with T-SQL

Versión en castellano

In a perfect world all our facilities have the same versions of the applications, database engines and auxiliary libraries, but this world is a utopia, and we sometimes have to find out what version of the database engine is working or what dll version is installed.

In the case of SQL Server I have discovered 3 ways to find the version from T-SQL, with any of the 3 options will get the full version, but in most cases only need the "major release". For example if you want to execute features of the new versions, or ones that are obsolete in the latest version but still work in older versions.

Let’s go:

@@VERSION

Is a system variable, so is useful for any version of SQL Server, but from the 2005 version returns a nvarchar instead of varchar from previous versions.

declare @version char(2)

select @version = substring(@@version, charindex('- ', @@version) + 2, charindex('.',@@version) - charindex('- ', @@version)-2)

SERVERPROPERTY(‘ProductVersion’)

This option is only available after SQL Server 2000 and returns a value of type sqlvariant, so you have to convert it to varchar or nvarchar to get the main version.

declare @productversion varchar(50)

declare @version char(2)

select @productversion = CAST(SERVERPROPERTY('productversion') AS VARCHAR(50))

set @version = SUBSTRING(@productversion,1,CHARINDEX('.',@productversion)-1)

xp_msver ‘ProductVersion’

Perhaps the most complicated, because it returns a result set like this:

Index Name Internal_Value Character_Value
------ -------------------------------- -------------- ----------------------
2 ProductVersion 589824 9.00.1406.00

(1 row(s) affected)

But you can also get the version with something like this:

CREATE TABLE #VersionTable(
      [Index] int,
      Name varchar(30),
      Internal_Value int,
      Character_Value varchar(50))
GO

INSERT INTO #VersionTable
EXEC master..xp_msver 'ProductVersion'

DECLARE @Version char(2)

SELECT @Version = (
SELECT SUBSTRING(Character_Value,1,CHARINDEX('.', Character_Value)-1)
FROM #VersionTable)

drop table #VersionTable

With any of the 3 options we get the following in @Version:

‘7’ -> SQL Server 7
‘8’ -> SQL Server 2000 y MSDE
‘9’ -> SQL Server 2005
‘10’ -> SQL Server 2008

Happy coding ;)