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

Asked for the SQL Server release without opening a properties dialog, which query do you run? Use SERVERPROPERTY to detect SQL Server version and edition, with @@VERSION for a descriptive summary. Keep the engine build, update level, hosting category, and database compatibility level separate.

Detect SQL Server Version with Structured Properties

SERVERPROPERTY returns individually named values that suit an inventory query. ProductVersion contains the complete engine version string. ProductMajorVersion identifies the major release for applicable SQL Server engines. Edition names the installed edition, while EngineEdition identifies the engine category. Retain all of them rather than extracting one fragment from a long banner.

I collect structured properties before parsing display text. I check which server the connection actually reached, especially when a named instance or a connection alias is involved. The installed SSMS version is a client fact. It does not establish the database engine build. Query the connected engine directly. A version report should identify the thing being reported, not merely the newest application visible on your desktop.

SELECT SERVERPROPERTY('ServerName') AS ConnectedServerName,
       SERVERPROPERTY('ProductVersion') AS ProductVersion,
       SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion,
       SERVERPROPERTY('ProductLevel') AS ProductLevel,
       SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel,
       SERVERPROPERTY('ProductUpdateReference') AS ProductUpdateReference,
       SERVERPROPERTY('Edition') AS Edition,
       SERVERPROPERTY('EngineEdition') AS EngineEdition;

Detect SQL Server Version Details in the Banner

@@VERSION supplies a descriptive string with build and platform information. It is convenient for a human reading one result. It is less convenient as a machine-parsed contract because it combines several facts in display text. Use the structured properties for reporting columns and retain the banner as supporting context.

SELECT @@VERSION AS VersionDescription;

Do not claim that a timestamp appearing in that banner records when the owner installed the server. Product build metadata and local installation history are different facts. Likewise, the edition name can include architecture information without becoming a complete operating-system report. To detect SQL Server version reliably, read the actual product values and understand their meaning. A long string feels authoritative, but length is not the same as an inventory schema.

Map Major Releases Without Mislabeling Azure

For SQL Server, major version 17 identifies SQL Server 2025, 16 identifies SQL Server 2022, and 15 identifies SQL Server 2019. Earlier supported release mappings can be added deliberately. Hosting categories must be interpreted before applying a boxed-product release label. Azure SQL Database and Azure SQL Managed Instance have different service and update models.

The next query converts sql_variant properties to the intended scalar types. It labels the Azure categories separately rather than assuming their major number follows the same release mapping. It returns a general fallback for a number outside the explicitly mapped releases. That is more honest than labeling an unfamiliar build as the most recent known product. On my SQL Server 2025 test instance it returned major version 17, engine category 3, and the label SQL Server 2025.

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 version mapping query identifies this Developer instance as SQL Server 2025.

Distinguish Product Level from Update Level

ProductLevel reports values such as RTM or a service-pack designation where applicable. ProductUpdateLevel reports an applicable cumulative-update designation. ProductUpdateReference supplies the related reference identifier. Read these with the complete ProductVersion because an update family alone does not identify every build detail.

A NULL update-level property does not automatically prove an unpatched original release. The property can be inapplicable, and servicing paths differ. A GDR path also deserves separate interpretation. Check the build against current official release information when assessing patch status. The returned fields establish the installed build, while the current release table establishes how it relates to available servicing. I keep that distinction clear because a static script cannot stay permanently current by writing a release name into a CASE expression.

Inspect the Host Where the Engine Runs

sys.dm_os_host_info reports host_platform, host_distribution, host_release, and host_service_pack_level. It describes the engine host rather than the computer running SSMS. Use it when platform context affects the diagnostic question. The required visibility permissions depend on SQL Server version, so handle denied access as a permission issue rather than a missing platform.

SELECT host_platform, host_distribution, host_release, host_service_pack_level
FROM sys.dm_os_host_info;

These fields do not replace a complete Windows inventory or an installation-support check. They establish useful context for the connected engine. Remote administration makes that distinction especially important. A Windows client can connect to an engine hosted elsewhere, and a managed service exposes different host information. Query the engine category first and select supported diagnostics accordingly. No external download or browser session is required for this local version report.

Detect SQL Server Version Apart from Database Context

A feature can depend on database compatibility level as well as engine version. A SQL Server 2025 instance can host a database at an older compatibility level. That does not make the engine SQL Server 2022. It changes supported behavior and optimizer choices for that database.

SELECT name, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();

The common wrong answer reports only ProductVersion and treats it as proof that every new syntax works in every database. Another reports SSMS's About dialog as the server version. Keep the report layered. Would the feature fail because the engine lacks it, the compatibility level restricts it, or the hosting category differs? Those are separate checks. To detect SQL Server version for troubleshooting, include enough context to distinguish them before recommending a change.

Save the query and the date of your diagnostic record outside the article's published content when maintaining an operational inventory. Re-run it when investigating a new failure because servers receive updates and databases change settings. A remembered screenshot should not outrank a fresh property result. Version information has an impressive shelf life right up until the maintenance window.

Follow-Up: Is Compatibility Level the Engine Version?

The interviewer asks next: does compatibility level 160 mean the server runs SQL Server 2022? No. A newer engine can run a database at that compatibility level. Read SERVERPROPERTY for the engine release and sys.databases for the database setting.

This distinction explains why a newer feature can fail on an otherwise current server. Check its documented engine, edition, platform, and compatibility requirements. Do not change compatibility just to make a test pass without reviewing workload behavior. The complete answer combines a runnable inventory, a careful release mapping, and an explicit separation of the facts. That is considerably more useful than memorizing one three-property SELECT.

A version report is not one product label, it is a set of engine, servicing, host, and database facts.

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