Interview Question of the Week #028 – T-SQL Script to Detect SQL Server Version and Property

I asked nearly twenty interview candidates how to find the SQL Server version with T-SQL. Only five could write the query, even with the internet available. The answer can start with three SERVERPROPERTY calls. The interesting follow-up is whether the candidate knows which server the result describes.

What is the SQL Server version and edition?

Run the query on the connection you want to inspect. This keeps the original answer’s product version, product level, and edition, and adds the connected server name and engine category.

SELECT SERVERPROPERTY('ServerName') AS ConnectedServer,
       SERVERPROPERTY('ProductVersion') AS ProductVersion,
       SERVERPROPERTY('ProductLevel') AS ProductLevel,
       SERVERPROPERTY('Edition') AS Edition,
       SERVERPROPERTY('EngineEdition') AS EngineEdition;

ProductVersion is the complete engine build. ProductLevel can describe a release level such as RTM; it alone does not establish the latest cumulative update. Edition describes the installed edition. These are properties of the connected database engine, not the SSMS application on your desk.

Translate a major version carefully

When a report needs a familiar release label, check the engine category before mapping a major version to a boxed SQL Server release. The following query was run on the SQL Server 2025 test instance; its result is shown directly below it.

DECLARE @Major int = TRY_CONVERT(int, SERVERPROPERTY('ProductMajorVersion'));
DECLARE @Engine int = TRY_CONVERT(int, SERVERPROPERTY('EngineEdition'));

SELECT @Major AS MajorVersion, @Engine AS EngineCategory,
       CASE WHEN @Engine = 5 THEN N'Azure SQL Database'
            WHEN @Engine = 8 THEN N'Azure SQL Managed Instance'
            WHEN @Major = 17 THEN N'SQL Server 2025'
            WHEN @Major = 16 THEN N'SQL Server 2022'
            WHEN @Major = 15 THEN N'SQL Server 2019'
            ELSE N'Check the documented release mapping'
       END AS ReleaseLabel;

The label is a convenience, not a permanent patch-status check. Compare the full build with current release information when servicing level matters. Database compatibility level is another separate setting; it does not change the engine’s product version. My interview answer remains short: query the server you connected to, then interpret each property for the question you were actually asked.

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