Understanding the SQL Server SELECT @@VERSION command

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.