What SQL Server Version Am I running?

While working with SQL Server, often a question comes to my mind, is what version of SQL Server am I running. How do I know if it has the latest service pack or an outdated one? These queries become more important when you are working on a remote server and want to know the versions are other server properties.

Find version of SQL Server 2000 and above

Get the product version of the server using the below query.

SELECT SERVERPROPERTY('ProductVersion') 'Product Version'

Output

Product Version
8.00.533

If the output has a value starting with the number 8 it indicates the version is SQL Server 2000. A value starting with number 9 indicates SQL Server 2005, and a number starting with 10 indicates SQL Server 2008. A value that start with number 12, indicates SQL Server version 2014.

Similarly, get other details like product level, SQL Server Edition, Server Name etc.

SELECT SERVERPROPERTY('EngineEdition') AS 'Engine Edition'

SELECT SERVERPROPERTY('Edition') AS Edition

SELECT SERVERPROPERTY('ProductLevel') AS 'Product Level'

Name of the Server

SELECT SERVERPROPERTY('ServerName') AS 'Name of the Server'

During installation, the SQL Server Setup sets server name as the computer name you are working on. However, you can change the server name using its inbuilt procedure named sp_addserver. You need to restart SQL Server to check the name new server name.

Using T-SQL @@VERSION

SELECT @@VERSION 'SQL Server Version'

This T-SQL command returns the installed SQL Server version along with built date, operating system, the service pact etc.

Output (SQL Server 2008)

Microsoft SQL Server 2008 (SP2)
- 10.0.4000.0 (Intel X86) Sep 16 2010 20:09:22
Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 5.1
<X86> (Build 2600: Service Pack 3)

Output (SQL Server 2014)

Microsoft SQL Server 2014
- 12.0.2000.8 (X64) Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.2
<X64> (Build 9200: ) (Hypervisor)

← PreviousNext →