How to Find Out Your Version of SQL Server
The SQL Server on your instance determines which features are available to you and indicates the level of protection from known vulnerabilities. Besides, it is also important for application compatibility. You can define your SQL Server version whether you are running the instance on-premises or in the cloud. This article will explore how to do it in your environment.
Identifying Your SQL Server Version
Understanding SQL Server Versions
Microsoft has the concept of major and minor versions. Major versions represent major releases, such as SQL Server 2019 – 2019 is the major version. Minor versions refer to incremental updates within the major version. For example, in SQL Server 2019 CU5 (Cumulative Update 5), CU5 is the minor version number indicating a specific update within the major version 2019.
The concept of major and minor versions was introduced in SQL Server 2012.
SQL Server Versioning and its Significance
SQL Server Version Abbreviations
You might come across quite a few abbreviations when learning about SQL Server versions. That’s why it is worth exploring them appropriately. Fortunately, SQL Server Blogs has been tracking version records for over a decade with profound levels of detail, making very good references aside from Microsoft documentation.
As defined by SQL Server Blogs, the SQL Server version documentation includes the following abbreviations:
| CTP | Community Technology Preview (beta release). This implies the version is in a "pre-release" state, and interested customers – users, developers, and administrators – are expected to try the product and its new features. Based on that, they can perform tests and provide feedback. |
| RC | Release Candidate. This implies that the version of SQL Server is ready for release. The feedback from the community has been factored in; typically, no new features are added. The state of the version may be the state at the official final initial release (RTM). |
| RTM | Released To Manufacturing. This is the original, released build version of SQL Server. An RTM is a production-ready release with no Service Packs and no Cumulative Updates. |
| CU | Cumulative Update. Cumulative updates contain bug fixes and feature enhancements – up to that point in time – that have been added since the RTM or Service Pack. Installing the Cumulative Update is like installing a Service Pack, though Cumulative Updates are not fully regression-tested. Important: Since January 2016, Microsoft has recommended ongoing, proactive installation of SQL Server Cumulative Updates as soon as they are available. They are certified to the same levels as Service Packs and should be installed with the same level of confidence. |
| SP | Service Pack. A much larger collection of hotfixes that have been fully regression tested. In some cases, they also contain product enhancements. All SQL Server service packs are cumulative – each new service pack contains all fixes included with previous service packs and new fixes. Note: Starting from SQL Server 2017, Service Packs have been discontinued. Only Cumulative Updates (CUs) are provided. |
| GDR | General Distribution Release. GDR fixes are stable releases with no new features. They ensure stability by including bug fixes and security updates. |
| QFE | Quick Fix Engineering. QFE updates include CU fixes. They are focused on resolving specific software issues, that’s why, users should pay attention to the accompanying documentation. QFE releases are temporary until the release of the next CU or SP. |
| OD | On-Demand hotfix. An OD is released per the customer’s request to resolve the specific problem. OD hotfixes may or may not be released to the general public. |
| COD | Critical On-Demand hotfix. A COD hotfix is released in case of a security breach or when the stability or functionality is affected severely. |
Table 1: SQL Server Version Abbreviations
More Information About SQL Server Versions
Cumulative Updates are called “cumulative” because they contain specific fixes or patches. If there are, for instance, five cumulative updates between two versions, it is mandatory to apply them all sequentially before signing up for a complete patch. Applying Service Packs (SPs) ensures a whole bunch of cumulative updates are applied.
When running SQL Server on-premises, it is important to schedule upgrades and patch updates explicitly. Previously, when running on-premise instances, we needed to test and apply security updates in-place but resort to an out-of-place approach when doing upgrades (out-of-place means building a new instance of SQL Server and then migrating databases from the old environment to the new one).
Cloud Service Providers (CSPs) typically ensure the application of security patches without recourse to cloud service consumers. This approach addresses security concerns and ensures that users receive all the latest features. CSPs also mitigate potential compatibility issues by including mechanisms for rollback. In the case of Amazon Web Services (AWS), major version upgrades must be manual but users can choose whether or not to apply minor updates automatically.
Methods for Checking SQL Server Version
Method 1: Using SQL Server Management Studio
To check the version of your SQL Server instance with SQL Server Management Studio, right-click the instance name and proceed to Properties as shown in Figure 1.
Fig. 1: SSMS Object Explorer
The Properties window provides a lot of information about the instance. We are particularly interested in the product and version fields (Figure 2).
The product for the instance is Microsoft SQL Server Express (64-bit) and the version is 15.0.4198. Notice the build number composed of three parts – the numerical expression in the ##.#.####.# format. We’ll explain it later in this article.
Fig. 2: SQL Server Version in SSMS
Method 2: Using Transact-SQL (T-SQL) Commands
Several system functions in SQL Server can reveal the SQL Server instance version. The simplest one is @@VERSION – it returns the system and builds information for the current installation of SQL Server. Another well-known function is SERVERPROPERTY.
The below example demonstrates how to retrieve the SQL Server version using SERVERPROPERTY:
-- Listing 1: Check SQL Server Version
-- Listing 1a: Check Version Using functions (formerly called Global Variables) --
PRINT 'Checking Server Name'
SELECT @@SERVERNAME AS [Server Name]
, @@VERSION AS [Server Version];
-- Listing 1b: Check Version Using the SERVERPROPERTY function --
SELECT
SERVERPROPERTY('ServerName') AS [ServerName]
, SERVERPROPERTY('Edition') AS [Edition]
, SERVERPROPERTY('ProductLevel') AS [ProductLevel]
, SERVERPROPERTY('ProductVersion') AS [ProductVersion]
, SERVERPROPERTY('ProcessID') AS [ProcessID];
In SSMS, the output of the @@VERSION function can be better visualized if you configure it to return results to text rather than grid (Figure 3):
Fig.3: Return @@VERSION Results as Text
Method 3: Checking the Windows Registry
- Open the Run Menu by clicking Windows + R.
- Type regedit and press Enter to open the Windows Registry Editor.
-
Navigate to the SQL Server Version Key. The path depends on the SQL Server version 😃 .
For SQL Server 2017 and later, follow the below path:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\{Instance Name}\Setup
Replace {Instance Name} with the name of your SQL Server instance.
For SQL Server 2016 and earlier, the path is:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\{SQL Server Version}\Tools\Setup
Replace {SQL Server Version} with the specific version number (e.g., MSSQLServer for the default instance). - Check the Version Number in the right pane of the Registry Editor and look for the value named version or PatchLevel. The value data will contain the version number of the SQL Server.
Obviously, this method is relevant for those running SQL Server on-premises or as Infrastructure as a Service (IaaS). Working with the Windows Registry requires caution, as making the wrong changes can disrupt the system.
Method 4: Using SQL Server Error Logs
Each time you restart an instance of SQL Server, the basic information about the instance is written into the log, including the SQL Server version. To view these details, open the current log file and scroll down to the beginning (see Figure 4):
Fig.4: SQL Server Version Captured in Error Log
However, this instance is an Amazon RDS instance, and we are reading the log from SQL Server Management Studio installed on an Amazon EC2 instance. The thing is, on-premised installation of SQL Server allows us to view the version information from both SSMS and the Windows directory storing SQL Server Log files. By default, this path is:
Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG
Understanding the Output
The log file we viewed included the product version: 15.0.4198.2. This indicator consists of several parts:
- Major Version Number (15): It represents the major release version of SQL Server – version 15. This version refers to SQL Server 2019.
- Minor Version Number (0): The minor version number typically indicates service packs or cumulative updates within the major version. A minor version number 0 usually defines the initial release of the major version (Released to Manufacturing (RTM) as in Table 1).
- Build Number (4198): The build number is a specific identifier for the exact build or patch level of SQL Server (the specific set of code changes and bug fixes). Each cumulative update or service pack release has a different build number.
- Patch Number (2): The patch number is sometimes included to represent the specific hotfix applied to the build. In this case, 2 means that there have been two additional patches applied to the base build 4198.
Therefore, this version number signifies a specific configuration of SQL Server with the indicated build and patch level. As with any software, it's essential to keep SQL Server up to date with all the latest cumulative updates and patches to ensure stability, security, and appropriate performance.
You can find more information about SQL Server versions and their corresponding build numbers on the Microsoft SQL Server documentation or support websites. Also, have a look at the below table that maps major versions to their corresponding version numbers:
| SQL Server Version | Version Number |
| SQL Server 7.0 | 7.00.623 |
| SQL Server 2000 | 8.00.194 |
| SQL Server 2005 | 9.00.1399 |
| SQL Server 2008 | 10.00.1600 |
| SQL Server 2008 R2 | 10.50.1600 |
| SQL Server 2012 | 11.00.2100 |
| SQL Server 2014 | 12.00.2000 |
| SQL Server 2016 | 13.00.4000 |
| SQL Server 2017 | 14.00.1000 |
| SQL Server 2019 | 15.00.2000 |
| SQL Server 2022 | 16.0.1050.5 |
Table 2: SQL Server Major Versions
The version numbe.rs in this table are associated with the initial release of each SQL Server version. Subsequent updates, service packs, and cumulative updates change the build numbers.
More detailed information is available in Microsoft documentation such as the SQL Server 2022 Build Versions. For instance, there are slight variations in the version numbers associated with the executable files of SQL Server products. It is also worth mentioning that Azure SQL and other database-as-a-service platforms suggest different version numbering.
Conclusion
This article outlined the most common ways of checking the SQL Server version. As we mentioned in the beginning, this information is important for understanding the security posture of your instance, its compatibility with other applications, and the features available to you. In practice, you may decide to always be on the latest version of SQL Server or on step behind until a stable minor version is available for the latest major version. You can find out more about this topic by turning to the sources in the References section.
One of the easiest options to obtain the necessary information is using SQL Server Management Studio. You may also choose to explore more sophisticated tools such as dbForge Studio or Azure Data Studio, which are more powerful and adaptable software solutions for handling database tasks in SQL Server.
References
- SQL Server Releases – Find My SQL Version
- SQL Server Builds
- SQL Server 2019 Build Versions
- Viewing the SQL Server Error Log