How Old Version of SQL Server Running on Server? – Interview Question of the Week #183

Question: How do I find out which SQL Server version is running, and how old its build is?

Answer: Run SELECT @@VERSION. It returns the Database Engine version, build number, build date and edition in one string.

This came up during a Comprehensive Database Performance Health Check. I assumed everyone knew the command, but it was a useful reminder that the small checks matter. I run it early in a consulting engagement so I know which engine and patch level I am investigating.

SELECT @@VERSION;

That date is the engine build date. It is not the date the customer installed SQL Server, the last time the service started, or the date the patch was applied. The build number and CU or servicing branch tell you more about what is installed than the major release name alone.

If you need separate columns for an inventory, use SERVERPROPERTY instead of parsing the long string:

SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion,
       SERVERPROPERTY('ProductLevel') AS ProductLevel,
       SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel,
       SERVERPROPERTY('Edition') AS Edition;

A property can return NULL when it is not applicable or is unsupported on that engine. Compare the resulting build with Microsoft’s version history for the relevant release. Keep the Database Engine version separate from the SSMS version and a database’s compatibility level.

Related reading:

• Identify SQL Server version from a backup file
• Project versions in the SSIS catalog
• Get server version and additional information

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.