Understanding the SQL Server SELECT @@VERSION command

By: K. Brian Kelley | Read Comments (2) | Print Brian is a SQL Server author and columnist focusing primarily on SQL Server security. Related Tips: More

Problem

I was recently trying to determine what version of SQL Server was installed on a particular server. I found this tip to determine the version and tried to execute the code in the command.  However, I do not understand what all the information means. Could someone explain it?  Check out this tip to learn about the SELECT @@VERSION command in SQL Server.

Solution

Here is the code that I ran from the tip:

SELECT @@VERSION;

Let's look at the output of this query on a particular SQL Server. You'll see it below:

Looking at the first line of the output, the first thing we can determine is the version of SQL Server: "Microsoft SQL Server 2008" in this example. If the SQL Server has been updated with a SQL Server service pack, then next to the version you should see the service pack level. Here, we know this server has had SQL Server 2008 SP3 applied since we see "(SP3)" in the output. If you see (RTM), then you know that no SQL Server-related service pack has been installed. RTM stands for release-to-manufacturing , which means when the product is first released.

Then there is a set of four numbers in the format of A.B.C.D. In this case, it's 10.0.5500.0. The first two numbers (10.0) tell us the numeric version of SQL Server. Going back to SQL Server 2000, we have the following versions, as of the writing of this tip:

The last half of that set of four numbers corresponds to the build of SQL Server at that version and service pack. The best resource to match up what has been installed on your SQL Server compared to the version you're receiving back is the SQL Server Version Database at SQLSecurity.com. 5500.0 is SQL Server 2008 SP3 with no cumulative updates or hotfixes according to the database.

After determining the specific configuration using the version database, the only thing remaining on the first line is "(X64)". This last piece of information tells us what version of SQL Server is running with respect to chip architecture. It is entirely possible to run a SQL Server x86 installation on an x64 system. Whatever the case, if you see (X86), that tells you that the x86 version of SQL Server was installed. 

The middle two lines contain information on when the SQL Server was compiled as well as the copyright notice for SQL Server itself. There's not a whole lot to glean here, so let's take a look at that last line. The first thing we see is the edition of SQL Server. In this example, that would be "Developer Edition." Some features are limited due to edition. For instance, Developer Edition is a specially licensed edition of SQL Server that allows you to test and use Enterprise Edition features on, say, a development workstation running Windows 7. Developer Edition is never to be used for a production server. To do so would be a violation of the licensing agreement. Knowing what version of SQL Server is important. Knowing what edition of SQL Server for that version is equally important.

After that we see confirmation that it is running the 64-bit version of SQL Server.  It then tells us the operating system. Windows NT 6.1 corresponds to Windows 7. So this particular SQL Server is running on a workstation. We're then told if the OS is 32 or 64-bit. In this case, it's 64-bit. Finally, we see the build and the service pack version.

This last bit of information is often confused with the SQL Server version. Folks see the "Service Pack 1" and think that means they are running a version of SQL Server with SQL Server Service Pack 1 applied. The service pack version for SQL Server is always going to be on the first line. The service pack version after the operating system corresponds to the OS. So we're not just running on Windows 7, but Windows 7 SP1. 

Putting all of this together, from the query we can conclude that we are running SQL Server 2008 SP3 Developer Edition (64-bit) on the 64-bit version of Windows 7 SP1. We could dig in to see if a cumulative update or hotfix was applied on the SQL Server side by taking that four part number (in this case we've already done so with 10.0.5500.0) and comparing it with the version database if that's necessary.

Next Steps

• Test executing SELECT @@VERSION; on different SQL Servers to practice interpreting the results.
• Check this tip to see other ways of determining the SQL Server version.

Share:

Share

Tweet

Free SQL Server Learning Waiting on Waits Let's connect on facebook! Tweet...tweet...tweet... Follow us on twitter

Comments and Feedback:

I prefer to use

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code