Knowing the exact version of your SQL Server installation is fundamental for maintaining security, applying the correct updates, and ensuring compatibility with your applications. Whether you are troubleshooting an issue, preparing for an upgrade, or documenting your environment, identifying the build number and edition is the first step toward effective database administration.
Using SQL Server Management Studio (SSMS)
For most database professionals, the easiest method is to use SQL Server Management Studio. This graphical interface provides immediate access to server properties without requiring any query writing.
Object Explorer Method
After connecting to your instance in Object Explorer, right-click on the server name at the top of the tree view. Select "Properties" from the context menu. In the resulting "Server Properties" window, navigate to the "General" page. Here, you will find the "Product" level, which displays the edition such as Enterprise or Standard, and the "Version" number, which corresponds to the specific release year and build number of the SQL Server software.
T-SQL Queries for Programmatic Checks
When you need to check multiple servers or prefer a script-based approach, Transact-SQL offers several reliable methods. These queries are useful for automation and can be executed directly within Query Editor.
SERVERPROPERTY Function
The most precise way to retrieve version details is by using the SERVERPROPERTY function. You can run a simple select statement to pull the major version, minor version, and build number. This method returns the raw data that corresponds to the specific build of your instance.
System Global Variables
Alternatively, you can leverage system global variables that are widely used in legacy scripts. The @@VERSION command returns a string that includes the version, processor architecture, build date, and operating system information. While this provides the most comprehensive output, parsing the specific build number usually requires looking at the last part of the string.
Checking via the Windows Command Line
Administrators who do not have access to SSMS or need to check instances remotely can utilize the command prompt. This method relies on the utility tools installed with the database engine.
SQLCMD Utility
If you have the SQLCMD utility installed, you can connect to the server and execute the version command. Even if you do not have a full client connection, the presence of the SQLCMD executable often indicates that client tools are present on the machine. Running the appropriate command will reveal the version details of the target SQL Server.
Interpreting the Build Numbers
SQL Server versions are identified by a specific build number rather than a simple year label. Understanding this numbering system allows you to pinpoint the exact release and cumulative update level of your installation.
Current generation
Previous generation
Extended Support
Legacy
Once you identify the build number, you can cross-reference it with the Microsoft Knowledge Base to determine the specific Cumulative Update (CU) level. This level is critical for understanding which security patches have been applied to the instance.
