Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to Read the Results of SELECT @@VERSION in SQL Server

The simple query SELECT @@VERSION can quickly tell you quite a bit about a SQL Server installation (and the underlying operating system), assuming you know how to read the results correctly. Many people seem to get confused between the operating system information and the SQL Server information that this query returns.

You can determine whether you are running x86 (32-bit), or x64 or IA64 (64-bit) for both SQL Server itself, and the underlying operating system. You can determine the major version and build number of SQL Server, along with the edition of SQL Server. It is very important to know whether you are on Enterprise Edition or not, since so many features depend on that. You can also see what Service Pack the operating system is on.

Looking at the first example below, you can see the SQL Server information in the first part of the result, while the operating system information is everything after the word “on”.  Knowing what the SQL Server and Windows major version numbers mean can help you interpret the query results much easier. Knowing what the SQL Server Build numbers mean will help you zero in on exactly what version and build of SQL Server you have installed. You may get build numbers that are not in my charts due to having a hotfix installed (from Windows Update or from Microsoft CSS).

Microsoft SQL Server 2008 (SP1) - 10.0.2746.0 (X64)   Nov  9 2009 16:37:47   
Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )  
-- SQL Server Major Versions
9.00 - SQL Server 2005
10.0 - SQL Server 2008
10.25 - SQL Server Azure
10.50 - SQL Server 2008 R2

-- SQL Server 2005/2008 Build lists

--   2005 SP2 Builds                  2008 SP3 Builds
-- Build       Description        Build       Description
-- 3042        SP2 RTM              4035        SP3 RTM
-- 3161        SP2 CU1              4207        SP3 CU1
-- 3175        SP2 CU2              4211        SP3 CU2 
-- 3186        SP2 CU3              4220        SP3 CU3         
-- 3200        SP2 CU4              4226        SP3 CU4         
-- 3215        SP2 CU5              4230        SP3 CU5          
-- 3228        SP2 CU6              4266        SP3 CU6        
-- 3239        SP2 CU7              4273        SP3 CU7        
-- 3257        SP2 CU8
-- 3282        SP2 CU9
-- 3294        SP2 CU10
-- 3301        SP2 CU11
-- 3315        SP2 CU12
-- 3325        SP2 CU13
-- 3328        SP2 CU14
-- 3330        SP2 CU15
-- 3355        SP2 CU16
-- 3356        SP2 CU17


--   2008 RTM Builds                    2008 SP1 Builds
-- Build       Description        Build        Description
-- 1600        Gold RTM
-- 1763        RTM CU1
-- 1779        RTM CU2
-- 1787        RTM CU3    -->      2531        SP1 RTM
-- 1798        RTM CU4    -->      2710        SP1 CU1
-- 1806        RTM CU5    -->      2714        SP1 CU2 
-- 1812        RTM CU6    -->      2723        SP1 CU3
-- 1818        RTM CU7    -->      2734        SP1 CU4
-- 1823        RTM CU8    -->      2746        SP1 CU5


-- Windows Major Versions
Windows NT 5.1    - Windows XP
Windows NT 5.2    - Windows Server 2003
Windows NT 6.0, Build 6001 - Vista 
Windows NT 6.0, Build 6002 - Windows Server 2008
Windows NT 6.1  - Windows Server 2008 R2 or Windows 7
-- Some more example outputs from SELECT @@VERSION

-- 32-bit SQL Server 2005 Developer Edition Build 4262, running on 32-bit Windows Server 2003 SP2
Microsoft SQL Server 2005 - 9.00.4262.00 (Intel X86)   Aug 13 2009 17:08:37   
Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

-- 32-bit SQL Server 2005 Standard Edition Build 4262, running on 32-bit Windows Server 2003 SP2
Microsoft SQL Server 2005 - 9.00.4262.00 (Intel X86)   Aug 13 2009 17:08:37   
Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

-- 64-bit SQL Server 2008 Enterprise Edition Build 2746, running on 64-bit Windows Server 2003 SP2
Microsoft SQL Server 2008 (SP1) - 10.0.2746.0 (X64)   Nov  9 2009 16:37:47   
Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)  

-- 64-bit SQL Server 2008 Enterprise Edition Build 2746, running on 64-bit Windows Server 2008 SP2
Microsoft SQL Server 2008 (SP1) - 10.0.2746.0 (X64)   Nov  9 2009 16:37:47   
Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) 

-- 64-bit SQL Server 2008 Enterprise Edition Build 2746, running on 64-bit Windows Server 2008 R2 (or Windows 7)
Microsoft SQL Server 2008 (SP1) - 10.0.2746.0 (X64)   Nov  9 2009 16:37:47   
Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )  

Comments

Posted by Anonymous on 8 January 2010

Pingback from  Dew Drop &#8211; January 8, 2010 | Alvin Ashcraft&#039;s Morning Dew

Posted by Giammarco Schisani on 8 January 2010

It surprises me that there is no stored procedure that only returns the version number, without the other junk (e.g. copyright text) that I am sure nobody cares about!

Anyhow, I found out that MSDE returns this (note the extra space):

"Microsoft SQL Server  2000....."

While SQL Server 2000 returns this (note the extra space is not there):

"Microsoft SQL Server 2000....."

Posted by Anonymous on 9 January 2010

Pingback from  Dew Drop &#8211; January 9, 2010 | Alvin Ashcraft&#039;s Morning Dew

Posted by Anonymous on 13 January 2010

Pingback from  SQL Server Central | Drakz Free Online Service

Posted by manonbar on 19 January 2010

I got the following information, but does this tell me I have Service Pack 1 indicated at lead or Service Pack 2 indicated at end? And what Cumulative Update would I have, the build 3790 doesn't seem to match anything?

Microsoft SQL Server 2008 (SP1) - 10.0.2746.0 (Intel X86)   Nov  9 2009 16:59:31   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

Posted by Glenn Berry on 19 January 2010

Tim,

You have 32-bit SQL Server 2008 SP1 CU5 (Build 2746) running on 32-bit Windows Server 2003 SP2.  The part at the end is about the operating system.

Posted by timserges on 20 January 2010

Thanks much, your examples clearly stated all the pieces, which I realized after I sent my comment.

Leave a Comment

Please register or log in to leave a comment.