Version and edition are two of the most basic facts about a SQL Server instance, and two of the easiest to get wrong when you’re relying on memory or an out-of-date wiki page. Whether a feature is available, whether a security patch has actually landed, and how many cores or how much memory the edition will actually use, all depend on getting this right.
This script returns the instance’s version, edition, patch level, and clustering status in a single query, no digging through SSMS dialogs required.
Why Version and Edition Matters
Edition determines hard limits: Standard Edition caps usable memory and core count well below what Enterprise allows, and features like Always On Availability Groups (beyond basic 2-node), online index rebuilds, and Query Store hints differ by edition. Version and patch level determine which security fixes and bug fixes are actually applied, not just which ones should be. Check the result against the SQL Server builds reference to see how far behind an instance actually is.
- Confirms the edition matches what was licensed and what the workload actually needs
- Confirms the instance is patched to a supported, secure build
- Surfaces whether the instance is clustered, relevant for any failover or maintenance planning
- The starting point for any inventory, audit, or migration assessment
When to Run This Script
- Routine SQL Server health checks
- Building or refreshing a server inventory
- Before planning a migration or upgrade
- Auditing a server or estate you’ve just inherited
The Script
Run the following script against your SQL Server instance.
/*
Script Name : Get-VersionAndEdition
Category : configuration-and-environment
Purpose : Display core instance version, edition, cluster status, and patch level.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-version-and-edition/)
Requires : Public (no special permissions required)
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT
SERVERPROPERTY('MachineName') AS machine_name,
SERVERPROPERTY('ServerName') AS server_name,
SERVERPROPERTY('InstanceName') AS instance_name,
SERVERPROPERTY('ProductVersion') AS product_version,
SERVERPROPERTY('ProductLevel') AS product_level,
SERVERPROPERTY('Edition') AS edition,
SERVERPROPERTY('BuildClrVersion') AS clr_version,
SERVERPROPERTY('IsClustered') AS is_clustered,
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS physical_hostname;
The script reads a handful of SERVERPROPERTY values and returns them as a single row: machine name, instance name, product version, patch level, edition, CLR version, cluster status, and physical hostname.
How To Run From The Repo
Clone DBA Tools, initialize and run the script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Check version, edition, and patch level:
.\run.ps1 Get-VersionAndEdition
# To run against a remote sql server:
.\run.ps1 Get-VersionAndEdition -ServerInstance SQLSERVER01
This script lives in the repo at:
Example Output
This instance is running build 17.0.4045.5, RTM, Enterprise Developer Edition, standalone (not clustered).
Understanding the Results
- product_version / product_level — the build number and patch level (RTM, SP, or CU). Cross-check the build number against Microsoft’s official build reference to confirm exactly which cumulative update is applied.
- edition — determines hard limits on memory, cores, and feature availability. Standard Edition caps usable memory well below Enterprise, and several HA/DR and performance features are Enterprise-only.
- is_clustered —
1means the instance is a Failover Cluster Instance. Relevant for planning maintenance windows and understanding failover behaviour. - instance_name — blank/NULL means this is the default instance; otherwise it’s a named instance sharing the machine with others.
Best Practices
- Keep an inventory of version and edition across every instance in your estate; don’t rely on memory or an out-of-date spreadsheet.
- Re-run this after every patch cycle to confirm the update actually applied, rather than trusting the installer’s success message alone.
- Cross-check
product_versionagainst Microsoft’s build reference when investigating whether a specific CVE or bug fix is present.
Related Scripts
You may also find these scripts useful:
- Server Inventory (hub)
- Database Inventory
- Databases
- Database Snapshot Inventory
- Database Summary
- Job Inventory
- Linked Servers
- Login Inventory
- OS and Hardware Info
- Patch Level
- DBA Scripts: The Complete Guide, the map across every script on this site
Frequently Asked Questions
How do I know which cumulative update is actually installed?
product_version gives the exact build number. Cross-reference it against Microsoft’s SQL Server build version reference page to identify the specific CU or security update.
Does edition affect performance, not just features?
Yes, indirectly. Standard Edition’s memory and core caps mean a workload that would perform well on Enterprise hardware can be memory- or CPU-constrained on the same hardware under Standard Edition’s limits.
Summary
Version and edition sound like trivia, but they’re the foundation every other check builds on: what features are available, what’s patched, what the licensing actually allows. Getting this wrong, or not knowing it at all, undermines confidence in everything else you find during a health check.
Run this script as the starting point for any inventory, audit, or migration assessment, and re-run it after every patch cycle to confirm the update actually took.
Leave a Reply