[sql-server-2008] How to check SQL Server version

What are the possible ways to determine the deployed SQL Server version?

I’ve tried to do it using the SQL Server software. I want to do it using a command line SQL statement.

Simply use

SELECT @@VERSION

Sample output

Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
Feb 10 2012 19:39:15 
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

Source: How to check sql server version? (Various ways explained)

Here is what i have done to find the version: just write SELECT @@version and it will give you the version.

declare @sqlVers numeric(4,2)
select @sqlVers = left(cast(serverproperty('productversion') as varchar), 4)

Gives 8.00, 9.00, 10.00 and 10.50 for SQL 2000, 2005, 2008 and 2008R2 respectively.

Also, Try the system extended procedure xp_msver. You can call this stored procedure like

exec master..xp_msver

TL;DR

SQLCMD -S (LOCAL) -E -V 16 -Q "IF(ISNULL(CAST(SERVERPROPERTY('ProductMajorVersion') AS INT),0)<11) RAISERROR('You need SQL 2012 or later!',16,1)"
IF ERRORLEVEL 1 GOTO :ExitFail

This uses SQLCMD (comes with SQL Server) to connect to the local server instance using Windows auth, throw an error if a version check fails and return the @@ERROR as the command line ERRORLEVEL if >= 16 (and the second line goes to the :ExitFail label if the aforementioned ERRORLEVEL is >= 1).

Watchas, Gotchas & More Info

For SQL 2000+ you can use the SERVERPROPERTY to determine a lot of this info.

While SQL 2008+ supports the ProductMajorVersion & ProductMinorVersion properties, ProductVersion has been around since 2000 (remembering that if a property is not supported the function returns NULL).

If you are interested in earlier versions you can use the PARSENAME function to split the ProductVersion (remembering the "parts" are numbered right to left i.e. PARSENAME('a.b.c', 1) returns c).

Also remember that PARSENAME('a.b.c', 4) returns NULL, because SQL 2005 and earlier only used 3 parts in the version number!

So for SQL 2008+ you can simply use:

SELECT
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    CAST(SERVERPROPERTY('ProductMajorVersion')  AS INT) AS ProductMajorVersion,
    CAST(SERVERPROPERTY ('ProductMinorVersion') AS INT) AS ProductMinorVersion;

For SQL 2000-2005 you can use:

SELECT
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS SYSNAME), CASE WHEN SERVERPROPERTY('ProductVersion') IS NULL THEN 3 ELSE 4 END) AS INT) AS ProductVersion_Major,
    CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS SYSNAME), CASE WHEN SERVERPROPERTY('ProductVersion') IS NULL THEN 2 ELSE 3 END) AS INT) AS ProductVersion_Minor,
    CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS SYSNAME), CASE WHEN SERVERPROPERTY('ProductVersion') IS NULL THEN 1 ELSE 2 END) AS INT) AS ProductVersion_Revision,
    CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS SYSNAME), CASE WHEN SERVERPROPERTY('ProductVersion') IS NULL THEN 0 ELSE 1 END) AS INT) AS ProductVersion_Build;

(the PARSENAME(...,0) is a hack to improve readability)

So a check for a SQL 2000+ version would be:

IF (CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS SYSNAME), CASE WHEN SERVERPROPERTY('ProductVersion') IS NULL THEN 3 ELSE 4 END) AS INT) < 10) -- SQL2008
OR (
    (CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS SYSNAME), CASE WHEN SERVERPROPERTY('ProductVersion') IS NULL THEN 3 ELSE 4 END) AS INT) = 10) -- SQL2008
AND (CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS SYSNAME), CASE WHEN SERVERPROPERTY('ProductVersion') IS NULL THEN 2 ELSE 1 END) AS INT) < 5)  -- R2 (this may need to be 50)
   )
    RAISERROR('You need SQL 2008R2 or later!', 16, 1);

This is a lot simpler if you're only only interested in SQL 2008+ because SERVERPROPERTY('ProductMajorVersion') returns NULL for earlier versions, so you can use:

IF (ISNULL(CAST(SERVERPROPERTY('ProductMajorVersion') AS INT), 0) < 11) -- SQL2012
    RAISERROR('You need SQL 2012 or later!', 16, 1);

And you can use the ProductLevel and Edition (or EngineEdition) properties to determine RTM / SPn / CTPn and Dev / Std / Ent / etc respectively.

SELECT
    CAST(SERVERPROPERTY('ProductVersion') AS SYSNAME) AS ProductVersion,
    CAST(SERVERPROPERTY('ProductLevel') AS SYSNAME)   AS ProductLevel,
    CAST(SERVERPROPERTY('Edition') AS SYSNAME)        AS Edition,
    CAST(SERVERPROPERTY('EngineEdition') AS INT)      AS EngineEdition;

FYI the major SQL version numbers are:

• 8 = SQL 2000
• 9 = SQL 2005
• 10 = SQL 2008 (and 10.5 = SQL 2008R2)
• 11 = SQL 2012
• 12 = SQL 2014
• 13 = SQL 2016
• 14 = SQL 2017

And this all works for SQL Azure too!

EDITED: You may also want to check your DB compatibility level since it could be set to a lower compatibility.

IF EXISTS (SELECT * FROM sys.databases WHERE database_id=DB_ID() AND [compatibility_level] < 110)
    RAISERROR('Database compatibility level must be SQL2008R2 or later (110)!', 16, 1)

select charindex(  'Express',@@version)

if this value is 0 is not a express edition