Knowing the exact version of your SQL Server instance is fundamental for database administration, security patching, and compatibility checks. Administrators often need to verify the build number to ensure specific bug fixes are applied or to confirm eligibility for particular features. The process of retrieving this information is straightforward, yet varies slightly depending on whether you are querying the system programmatically or inspecting the graphical interface.
Understanding SQL Server Version Numbers
SQL Server versioning can be confusing because it uses both a marketing name and a technical version number. For example, SQL Server 2016 is version 13.0, 2017 is 14.0, 2019 is 15.0, and 2022 is 16.0. The actual build number, however, reveals the specific cumulative update level, which is critical for troubleshooting. To get the full picture of your environment, you need to look at the specific Transact-SQL queries that extract this data from system metadata.
Using SERVERPROPERTY Function
The most common and reliable method to get the SQL Server version is by utilizing the built-in SERVERPROPERTY function. This function returns specific properties about the SQL Server instance, and the 'ProductVersion' property provides the full version string. This is the standard approach for scripts and applications that need to programmatically verify the running version without relying on external tools.
Basic Version Query
To retrieve the version, you can execute a simple SELECT statement. This query pulls the product version directly from the server's system properties, returning a string that includes the major, minor, and build numbers.
Query
SELECT SERVERPROPERTY('ProductVersion') AS 'SQL Server Version';
Additional Useful Properties
While the product version is the most important, you can also gather other details to fully document your server. The 'ProductLevel' property indicates whether you are on a RTM, SP, or CU release, and 'Edition' tells you if you are running Enterprise, Standard, or Developer. Combining these provides a complete snapshot of your SQL Server configuration.
Querying System Views for Detailed Information
For a more comprehensive view that includes the compatibility level and recovery model, you can query the sys.databases view. By selecting the top record from the system view, you can see the version of the model database, which reflects the instance version. This method is particularly useful when you need to join version data with other database-level metrics.
Comprehensive System View Query
Query
SELECT @@VERSION AS 'Complete SQL Server Version';
Checking the Error Log
When you connect to a SQL Server instance via SQL Server Management Studio (SSMS), the error log automatically displays the startup messages. This log contains the exact version and build number that the instance booted with. Reviewing this log is a quick way to verify the information without running a query, and it serves as a historical record of restarts and version confirmations.
Using Command Line Utilities
For remote administration or scripting scenarios where a GUI is unavailable, the command line utility sqlcmd is invaluable. You can connect to the server and execute the version query directly from the terminal. This method is efficient for automation and can be integrated into deployment or monitoring scripts to ensure version consistency across multiple servers.
