How do you check what version of SQL Server for a database using TSQL?
Sql ServerDatabaseTsqlSql Server Problem Overview
Is there a system stored procedure to get the version #?
Sql Server Solutions
Solution 1 - Sql Server
Try
SELECT @@VERSION
or for SQL Server 2000 and above the following is easier to parse :)
SELECT SERVERPROPERTY('productversion')
, SERVERPROPERTY('productlevel')
, SERVERPROPERTY('edition')
Solution 2 - Sql Server
SELECT @@VERSION
Solution 3 - Sql Server
I know this is an older post but I updated the code found in the link (which is dead as of 2013-12-03) mentioned in the answer posted by Matt Rogish:
DECLARE @ver nvarchar(128)
SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)
SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)
IF ( @ver = '7' )
SELECT 'SQL Server 7'
ELSE IF ( @ver = '8' )
SELECT 'SQL Server 2000'
ELSE IF ( @ver = '9' )
SELECT 'SQL Server 2005'
ELSE IF ( @ver = '10' )
SELECT 'SQL Server 2008/2008 R2'
ELSE IF ( @ver = '11' )
SELECT 'SQL Server 2012'
ELSE IF ( @ver = '12' )
SELECT 'SQL Server 2014'
ELSE IF ( @ver = '13' )
SELECT 'SQL Server 2016'
ELSE IF ( @ver = '14' )
SELECT 'SQL Server 2017'
ELSE
SELECT 'Unsupported SQL Server Version'
Solution 4 - Sql Server
For SQL Server 2000 and above, I prefer the following parsing of Joe's answer:
declare @sqlVers numeric(4,2)
select @sqlVers = left(cast(serverproperty('productversion') as varchar), 4)
Gives results as follows:
Result Server Version 8.00 SQL 2000 9.00 SQL 2005 10.00 SQL 2008 10.50 SQL 2008R2 11.00 SQL 2012 12.00 SQL 2014
Basic list of version numbers here, or exhaustive list from Microsoft here.
Solution 5 - Sql Server
There is another extended Stored Procedure which can be used to see the Version info:
exec [master].sys.[xp_msver]
Solution 6 - Sql Server
CREATE FUNCTION dbo.UFN_GET_SQL_SEVER_VERSION
(
)
RETURNS sysname
AS
BEGIN
DECLARE @ServerVersion sysname, @ProductVersion sysname, @ProductLevel sysname, @Edition sysname;
SELECT @ProductVersion = CONVERT(sysname, SERVERPROPERTY('ProductVersion')),
@ProductLevel = CONVERT(sysname, SERVERPROPERTY('ProductLevel')),
@Edition = CONVERT(sysname, SERVERPROPERTY ('Edition'));
--see: http://support2.microsoft.com/kb/321185
SELECT @ServerVersion =
CASE
WHEN @ProductVersion LIKE '8.00.%' THEN 'Microsoft SQL Server 2000'
WHEN @ProductVersion LIKE '9.00.%' THEN 'Microsoft SQL Server 2005'
WHEN @ProductVersion LIKE '10.00.%' THEN 'Microsoft SQL Server 2008'
WHEN @ProductVersion LIKE '10.50.%' THEN 'Microsoft SQL Server 2008 R2'
WHEN @ProductVersion LIKE '11.0%' THEN 'Microsoft SQL Server 2012'
WHEN @ProductVersion LIKE '12.0%' THEN 'Microsoft SQL Server 2014'
END
RETURN @ServerVersion + N' ('+@ProductLevel + N'), ' + @Edition + ' - ' + @ProductVersion;
END
GO
Solution 7 - Sql Server
Here's a bit of script I use for testing if a server is 2005 or later
declare @isSqlServer2005 bit
select @isSqlServer2005 = case when CONVERT(int, SUBSTRING(CONVERT(varchar(15), SERVERPROPERTY('productversion')), 0, CHARINDEX('.', CONVERT(varchar(15), SERVERPROPERTY('productversion'))))) < 9 then 0 else 1 end
select @isSqlServer2005
Note : updated from original answer (see comment)
Solution 8 - Sql Server
The KB article linked in Joe's post is great for determining which service packs have been installed for any version. Along those same lines, this KB article maps version numbers to specific hotfixes and cumulative updates, but it only applies to SQL05 SP2 and up.
Solution 9 - Sql Server
Try this:
if (SELECT LEFT(CAST(SERVERPROPERTY('productversion') as varchar), 2)) = '10'
BEGIN
Solution 10 - Sql Server
SELECT
@@SERVERNAME AS ServerName,
CASE WHEN LEFT(CAST(serverproperty('productversion') as char), 1) = 9 THEN '2005'
WHEN LEFT(CAST(serverproperty('productversion') as char), 2) = 10 THEN '2008'
WHEN LEFT(CAST(serverproperty('productversion') as char), 2) = 11 THEN '2012'
END AS MajorVersion,
SERVERPROPERTY ('productlevel') AS MinorVersion,
SERVERPROPERTY('productversion') AS FullVersion,
SERVERPROPERTY ('edition') AS Edition
Solution 11 - Sql Server
Getting only the major SQL Server version in a single select:
SELECT SUBSTRING(ver, 1, CHARINDEX('.', ver) - 1)
FROM (SELECT CAST(serverproperty('ProductVersion') AS nvarchar) ver) as t
Returns 8
for SQL 2000, 9
for SQL 2005 and so on (tested up to 2012).
Solution 12 - Sql Server
Try
SELECT @@MICROSOFTVERSION / 0x01000000 AS MajorVersionNumber
For more information see: Querying for version/edition info
Solution 13 - Sql Server
select substring(@@version,0,charindex(convert(varchar,SERVERPROPERTY('productversion')) ,@@version)+len(convert(varchar,SERVERPROPERTY('productversion'))))
Solution 14 - Sql Server
If all you want is the major version for T-SQL reasons, the following gives you the year of the SQL Server version for 2000 or later.
SELECT left(ltrim(replace(@@Version,'Microsoft SQL Server','')),4)
This code gracefully handles the extra spaces and tabs for various versions of SQL Server.
Solution 15 - Sql Server
Try this:
SELECT @@VERSION[server], SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
Solution 16 - Sql Server
Try this:
SELECT
'the sqlserver is ' + substring(@@VERSION, 21, 5) AS [sql version]