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.

回答1:

Following are possible ways to see the version:

Method 1: Connect to the instance of SQL Server, and then run the following query:

Select @@version

An example of the output of this query is as follows:

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 
10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Express 
Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

Method 2: Connect to the server by using Object Explorer in SQL Server Management Studio. After Object Explorer is connected, it will show the version information in parentheses, together with the user name that is used to connect to the specific instance of SQL Server.

Method 3: Look at the first few lines of the Errorlog file for that instance. By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files. The entries may resemble the following:

2011-03-27 22:31:33.50 Server      Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)                 Mar 29 2009 10:11:52                 Copyright (c) 1988-2008 Microsoft Corporation                Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

As you can see, this entry gives all the necessary information about the product, such as version, product level, 64-bit versus 32-bit, the edition of SQL Server, and the OS version on which SQL Server is running.

Method 4: Connect to the instance of SQL Server, and then run the following query:

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

Note This query works with any instance of SQL Server 2000 or of a later version

回答2:

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

回答3:

select charindex(  'Express',@@version)

if this value is 0 is not a express edition

回答4:

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)