Every patch conversation I have starts with the same question: which build are you on? The SQL Server build number answers that exactly, and it takes about a minute to learn how to read all four parts of it.
The Four Parts of a SQL Server Build Number
A version such as 16.0.4003.1 has four parts. Read them from left to right: major, minor, build, and revision. The major part identifies the product generation. The minor part belongs to that generation. The build and revision identify a particular servicing state. The example is only a format illustration. Do not assign a cumulative update from those digits without checking Microsoft’s build list.
Almost every instance I am asked to look at arrives with a ticket that says fully patched. I stopped trusting that sentence years ago. The SQL Server build number is what is actually installed, and it is right there to read.
I start with the version string because it is precise. A friendly label such as SQL Server 2022 tells me the generation, but it does not tell me which fixes are present. A ticket saying “fully patched” tells me even less. Ask for the exact version from each instance involved in the problem. Save the capture date too. Servers change between the first call and the investigation.
The name displayed by a management tool can point to a listener or an alias. Run the query on the connection you are actually using. Record the machine and instance names with the version. That small habit prevents a surprisingly common mistake: checking the wrong replica and then arguing with the patch team.
SELECT
@@SERVERNAME AS RegisteredName,
SERVERPROPERTY('MachineName') AS MachineName,
SERVERPROPERTY('InstanceName') AS InstanceName,
SERVERPROPERTY('ProductVersion') AS ProductVersion;Ask SQL Server for Its Own Labels
The version alone is useful. The related SERVERPROPERTY values make the answer easier to read. ProductLevel tells you whether the base is RTM or a service pack on releases that used service packs. ProductUpdateLevel reports a CU label when applicable. ProductBuildType identifies a GDR or an on demand build when SQL Server exposes that label. A NULL value does not prove that the server has no patches. It can mean the property does not apply to that build.
I collect these fields together. Copying only the first line of @@VERSION is tempting, but the label can hide the exact branch you need. Keep both the human label and the numeric version in an incident record. The numeric version is the anchor. The labels help another DBA understand it without hunting for documentation.
Run the query under a normal account with access to the instance. No production table changes are needed. This is a safe first check during an outage. It also belongs in your recurring inventory job.
SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel,
SERVERPROPERTY('ProductBuildType') AS ProductBuildType,
SERVERPROPERTY('Edition') AS Edition;Map the SQL Server Build Number to a CU
Do not divide the build digits and guess the CU. Find the exact product version in Microsoft’s SQL Server build history. Match the major release first, then match the full build string. The entry identifies the CU, GDR, or other servicing release and points to the related KB information. Read the release note for the exact update before scheduling work.
I keep the build history page open in a tab during any patch planning call. It saves the moment where two people argue about which CU a number means, and neither of them has looked it up.
A build list is a lookup table, not a target by itself. The newest row can be an update for a different release, a security branch, or a component that your instance does not use. Confirm the edition and installed components. Confirm the package applies to the instance you intend to patch. SQL Server Setup also patches shared features, which gives you another reason to review the package scope.
I paste the full version into the maintenance ticket. I also write down the named CU or GDR after the lookup. The ticket can then survive a change of shift without forcing the next person to repeat the detective work.
Why Matching CU Labels Can Hide a Difference
Two servers can both show the same ProductUpdateLevel and still report different full product versions. A later security update can be applied on top of a CU baseline. An on demand fix can also change the build. Those servers share the CU baseline, but they do not have identical bits. Compare ProductVersion and ProductBuildType, not only the CU text.
There is another source of confusion. A host can have more than one SQL Server instance. Patching one instance does not automatically prove that every other instance reached the same level. Shared components have their own servicing story. Compare each Database Engine instance separately and inspect setup results for the other installed features.
If a failover group appears inconsistent, connect to each replica directly. The listener follows the active role and can return the same primary twice. A direct replica check makes the gap visible. Keep the replica name beside every captured version.
Compare the Right Things
A version comparison starts with the major release. A larger numeric build from an older major release is not newer software. Within one release, consult the official build table to understand branch ordering. A GDR on the base branch and a CU on another branch are not interchangeable just because one integer looks larger.
For an application issue, compare more than binaries. Database compatibility level can differ even when engine builds match. Server configuration, trace flags, drivers, and application settings also affect behavior. Do not blame a CU merely because two environments have different plans. First identify every difference that can change the workload.
Capture compatibility levels with the version snapshot. The result also helps when a database was moved recently. Restoring a database on a newer engine does not automatically mean the compatibility level was changed. This query gives you a direct answer instead of a guess.
SELECT
name,
compatibility_level
FROM sys.databases
WHERE database_id > 4
ORDER BY name;Turn the SQL Server Build Number Into an Action
When you find an old build, do not jump straight to installation. Read the CU notes, known issues, prerequisite information, and restart guidance. Check your backup and recovery plan. Schedule a test on a comparable server. Record the desired build in the change ticket so the final verification has a clear target.
After installation, reconnect to the instance and run the same version query. Compare the full before and after values. Read the setup summary and SQL Server error log. A successful installer window is encouraging, but the running engine is the final authority. If the version did not move as expected, stop the rollout and investigate before touching another server.
I also retain the old build in the inventory. The historical record answers a useful question later: was the server already on this CU when the incident began? Without dates, a build list becomes a photograph with no timestamp.
Keep a Small Version Record
A useful version record has the instance name, machine name, environment, capture time, ProductVersion, ProductLevel, ProductUpdateLevel, and ProductBuildType. Add the change ticket that installed the patch. That is enough for a DBA to trace most servicing questions without opening a remote session. Store the result in a table or a controlled inventory, and let a scheduled job refresh it.
Do not overwrite the previous row each night. Append a dated row. A later difference between two servers is easier to explain when you can see when it first appeared. The history also reveals a server that stopped reporting after a rebuild or a name change. Silence from the inventory is an alert, not proof that the machine went away.
The SQL Server build number is a clue, not a diagnosis. Once you know the exact release, you can read the right fix notes and test the right change. That beats treating every slow query as a mysterious patch problem.
Related reading on this blog: Script to Determine Which Version of SQL Server 2000-2005 is Running and Raising a Database Compatibility Level Safely.
A build number is not decoration, it is the shortest reliable path to the exact engine you are running.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
