Telling Which Platform You Run On With SERVERPROPERTY EngineEdition

A useful diagnostic script needs to know which engine it has reached before asking for platform-specific objects. EngineEdition identifies the engine family, while the edition and version properties supply the rest of that context.

Collect EngineEdition With the Other Properties

SERVERPROPERTY returns information about the current engine. EngineEdition is a numeric family identifier. Edition provides a descriptive edition name, ProductVersion provides the build string, and ProductMajorVersion provides the major engine version.

Those properties answer different questions. An Enterprise-family number does not establish that an installation holds an Enterprise production license. Developer and evaluation editions can belong to that family while carrying different usage rights.

I collect the raw number beside the descriptive name. A label-only report hides the mapping that created it. Keeping both makes an unfamiliar value visible instead of forcing it into the closest familiar category.

SELECT SERVERPROPERTY('ServerName') AS ConnectedServer,
       CONVERT(int, SERVERPROPERTY('EngineEdition')) AS EngineEdition,
       CONVERT(nvarchar(128), SERVERPROPERTY('Edition')) AS EditionName,
       CONVERT(nvarchar(128), SERVERPROPERTY('ProductVersion')) AS ProductVersion,
       CONVERT(nvarchar(128), SERVERPROPERTY('ProductMajorVersion')) AS ProductMajorVersion;

Run this on the connection the script actually uses. A property captured from an administrative instance does not describe another connection opened later. Platform decisions need to follow the target connection, not the operator's workstation.

Record the result without claiming it was tested elsewhere. These queries can inspect your local installation. The meanings of other engine-family values come from the documented property mapping, not from imagined cloud executions.

Translate EngineEdition Values Carefully

The CASE expression below names common SQL Server and Azure SQL families. It keeps an explicit unknown branch. Other documented families receive neutral descriptions so the script does not assume the same diagnostic surface applies to them.

DECLARE @Engine int = CONVERT(int, SERVERPROPERTY('EngineEdition'));
SELECT @Engine AS EngineEdition,
       CASE @Engine
           WHEN 1 THEN N'Legacy desktop family'
           WHEN 2 THEN N'Standard family'
           WHEN 3 THEN N'Enterprise family'
           WHEN 4 THEN N'Express family'
           WHEN 5 THEN N'Azure SQL Database'
           WHEN 6 THEN N'Dedicated analytics engine family'
           WHEN 8 THEN N'Azure SQL Managed Instance'
           WHEN 9 THEN N'Edge engine family'
           WHEN 11 THEN N'Serverless analytics engine family'
           WHEN 12 THEN N'Managed SQL database engine family'
           ELSE N'Unrecognized engine family'
       END AS EngineFamily,
       SERVERPROPERTY('Edition') AS EditionName;

Values 2, 3, and 4 describe familiar installed SQL Server families. Value 5 identifies Azure SQL Database, while value 8 identifies Azure SQL Managed Instance. Those two managed platforms have different instance-level capabilities.

SQL Server 2025 includes Standard Developer and Enterprise Developer editions. Their family values follow Standard and Enterprise respectively. My local SQL Server 2025 instance reports Enterprise Developer Edition with family value 3. Read Edition alongside EngineEdition before treating a family number as a specific installed edition.

Keep the mapping under review as supported platforms evolve. An ELSE branch should stop unsupported operations gracefully, not guess that every future engine is equivalent to a boxed installation. Display the raw value so the gap is actionable.

Inspect Your Local Result Without Inventing Others

Use the first query on the local Developer instance before building a branching script. Read the returned edition name to determine which Developer family it represents. Do not hard-code an expected family from an older installation's result.

The exact ProductVersion string identifies the installed build. ProductMajorVersion is useful for coarse version checks, but it does not establish that every update or feature is present. Review edition and database compatibility separately where the feature requires them.

A server report should name the collection target and time. The same machine can host several instances with different editions and builds. A Windows computer name alone is therefore insufficient identification for a reusable diagnostic result.

An engine-family number is a helpful sign on the door. It does not tell you which rooms your login can enter. Platform support and permissions both affect whether a diagnostic statement can run.

Guard Agent History at Runtime

Agent history is available on suitable installed SQL Server editions and Azure SQL Managed Instance. It is not the same available surface on Azure SQL Database or Express. Branch before executing the msdb query.

Use dynamic SQL for a platform-specific statement so an unsupported static reference is not bound as part of the common script. Still check permissions and object availability within the selected platform branch.

DECLARE @Engine int = CONVERT(int, SERVERPROPERTY('EngineEdition'));
IF @Engine IN (2,3,8)
BEGIN
    EXEC sys.sp_executesql N'
        SELECT TOP (20) j.name AS JobName, h.step_id,
               h.run_status, h.run_date, h.run_time, h.message
        FROM msdb.dbo.sysjobhistory AS h
        JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
        ORDER BY h.instance_id DESC;';
END
ELSE
BEGIN
    SELECT N'Agent history is outside this script''s supported platform branch.' AS DiagnosticMessage;
END;

This branch does not prove the Agent service is running or that the caller can read every job. It selects a supported family for further inspection. A permission failure needs its own clear diagnostic message rather than a false unsupported-platform label.

Azure SQL Managed Instance belongs in the Agent-capable branch even though it is managed. This is why testing only whether an engine is installed locally is too broad a shortcut. Guard the actual capability you need.

Check Database Compatibility Separately

Database compatibility affects optimizer behavior and the availability of some language features. It is stored in sys.databases rather than encoded in the engine family number. A supported engine can host a database under an older compatibility level.

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

I keep that database check beside any feature-specific query. A regular-expression example needing level 170 and a query needing a particular engine feature are different prerequisites. Raising compatibility is a reviewed database change, not a fallback inside a read-only diagnostic.

When a property returns null or a value outside the mapping, retain that result and stop the unsupported branch. Do not silently replace it with your workstation's expected value. The script should make uncertainty visible.

Keep Capability Checks Narrow

Which particular operation does the next step need? Check that operation's documented platform support, version requirements, and permissions. EngineEdition starts the decision, but a single numeric value cannot stand in for every prerequisite.

A reusable script should return useful common information before skipping an unsupported optional step. Explain the skipped step in plain words and preserve the raw properties. That lets the reader distinguish a deliberate branch from a failed query.

Keep documented mappings separate from locally observed output during review. Test each branch on the platforms you actually have before declaring broad coverage. A CASE expression can list more platforms than your test environment can verify.

Engine family also does not describe the physical computer underneath the connection. SQL Server installed on a Windows virtual machine still reports its SQL Server edition family. Hosting that machine in a cloud location does not turn it into Azure SQL Database.

Use separate inventory records for the host and its allocated resources. A familiar engine number says nothing about available processors, storage latency, or memory limits. Keep those questions in the hardware check. This distinction prevents a deployment script from guessing platform capabilities from the address of the server.

Related reading on this blog: Different Methods to Know COMPATIBILITY LEVEL of a Database and Identify Version of SQL Server from Backup File.

EngineEdition is not a license certificate, it is an engine-family clue that belongs beside edition, version, and capability checks.

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