SQL SERVER – Get SQL Server Version and Edition Information – SQL in Sixty Seconds #043 – Video

What do consultants do when they come across any new instance of SQL Server? Well, their very first question is what version of SQL Server is it? The reason is simple – SQL Server is a very vast product and each version of the product have new features released and old features deprecated. Many consultant even remembers service pack and features released in it. In this video I show a quick way to find the SQL Server version and edition.

Well, there are multiple ways to know the version numbers of the SQL Server. In this sixty second video we will see a neat trick where we will quickly find the version number of SQL Server.

Let us see the same concept in following SQL in Sixty Seconds Video:

Related Tips in SQL in Sixty Seconds:

• Get Server Version and Additional Info
• Get All the Information of Database using sys.databases
• Script to Find SQL Server on Network
• Version Information and Additional Information – Extended Stored Procedure xp_msver

What would you like to see in the next SQL in Sixty Seconds video?

Other Ways to Find the SQL Server Version and Edition

@@VERSION is quick, but it returns one long line of text that is hard to use in a script. When I need the details in separate columns, I use SERVERPROPERTY:

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

ProductLevel shows RTM or the service pack, and Edition tells you if you are on Express, Standard, Enterprise or Developer. The first number of ProductVersion maps to the release: 10 is SQL Server 2008 (10.50 is 2008 R2), 11 is 2012, 12 is 2014, 13 is 2016, 14 is 2017, 15 is 2019 and 16 is 2022.

If SQL Server will not start and you cannot run a query at all, open the current ERRORLOG file. Its first lines show the same version and edition details.

I like to keep this query as a snippet and run it first on any server I have not seen before. The answer tells me which features I can use, which updates are missing, and whether the release is still supported. The edition matters as much as the number, since features such as online index rebuilds are not available in every edition.

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