Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Understanding the SQL Server SELECT @@VERSION command


By:   |   Updated: 2011-12-13   |   Comments (8)   |   Related: 1 | 2 | 3 | More > SQL Server Configurations

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:

Output of SELECT @@VERSION in SQL Server

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:

SQL Server #

SQL Server Version

8.00 SQL Server 2000 
9.00 SQL Server 2005 
10.0 SQL Server 2008 
10.5 SQL Server 2008 R2

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.


Last Updated: 2011-12-13


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, August 06, 2013 - 10:00:10 AM - K. Brian Kelley Back To Top

SQL Server 2005 doesn't use the version number. Also, @@VERSION will tell you what build level, so you can quickly scan to see if it has had a service pack applied, etc.


Monday, August 05, 2013 - 10:10:13 PM - Jay Back To Top

Even this version number can be found at the folder level also, while installing SQL Server folders will created with MSSQL10. , so on as per version.


Thursday, February 28, 2013 - 2:09:07 PM - yommy Back To Top

FUNCTIONS OF @@VERSION IN SQL AND WHY USING 2,3,4 NUMERIC VALUE AFTER THE @@VALUE


Thursday, February 28, 2013 - 2:02:33 PM - yommy Back To Top

what is the function of @@version and why using numeric veriable with like 2,3,4 after the @@version.


Thursday, February 28, 2013 - 9:31:32 AM - K. Brian Kelley Back To Top

Yommy, I don't think I understand your question. Could you clarify what you're asking?

 


Thursday, February 28, 2013 - 12:52:04 AM - yommy Back To Top

 

What do you think @@version is and why do we need the “,2,3,4” after the @@version?

 


Tuesday, January 03, 2012 - 6:27:37 AM - Michael Back To Top
I prefer to use
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Tuesday, December 20, 2011 - 8:35:24 PM - Alexander Back To Top

THX for the post.

-A


Learn more about SQL Server tools