SQL Server Club
Home Articles Essential Guides Blogs Links Twitter Facebook Join Contact About
SQL Server Version Numbers - From 6.5 to 2008
Rachel Clements About the Author

Rachel has been working with SQL Server for ten years, building her database skills on top of her roles as both a web and Windows developer.

As well as being Technical Editor here at SQL Server Club, she is a founder and organiser of the SQL Server Club User Group Southwest, based in Bristol, UK. Her favorite activities include stepping through code, reading about the internal workings of SQL Server and drinking coffee.

Linked In Typepad Profile Rachel Clements on Facebook Rachel Clements RSS Feed Rachel Clements on Twitter
Send to a Friend Printer Friendly Version
Many of us are familiar with the query:
select @@version
Run this little query in either Management Studio or Query Analyzer and it brings back information about the SQL Server database engine and the operating system it sits on. Running this query against my test server, it brings back the following information:
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

(1 row(s) affected)

We can see that I am querying against SQL Server 2005, but is it upgraded to the lastest service pack? I need to know because I'm developing and testing against this server and I want to make sure my code works with both servers that are service packed, and those that are not. My code works at the moment, but which version is it?

The results that came back from the query tell me the service pack version of the operating system is SP1, though it doesn't tell me the actual name of the operating system - the opposite of the information brought back about SQL Server. Perhaps Microsoft will change this one day, but in the meantime I know my OS is Windows Server 2003 (or Windows NT 5.2 as it tells me above) but which, if any, service packs have been installed for SQL Server?

Well, all I have to go on in the number 9.00.2047.00 following the words SQL Server 2005. I'll have to look it up. The following table lists all the SQL Server versions and their corresponding numbers which come back when you run the query select @@version:

select @@version number SQL Server and Service Pack Version
6.50.201 SQL Server 6.5 RTM
6.50.213 SQL Server 6.5 with Service Pack 1
6.50.240 SQL Server 6.5 with Service Pack 2
6.50.258 SQL Server 6.5 with Service Pack 3
6.50.281 SQL Server 6.5 with Service Pack 4
6.50.415 SQL Server 6.5 with Service Pack 5
6.50.416 SQL Server 6.5 with Service Pack 5a
7.00.623 SQL Server 7.0 (MSDE 1.0 RTM)
7.00.699 SQL Server 7.0 with Service Pack 1
7.00.842 SQL Server 7.0 with Service Pack 2
7.00.961 SQL Server 7.0 with Service Pack 3
7.00.1063 SQL Server 7.0 with Service Pack 4
8.00.194 SQL Server 2000 RTM
8.00.384 SQL Server 2000 with Service Pack 1
8.00.534 SQL Server 2000 with Service Pack 2
8.00.760 SQL Server 2000 with Service Pack 3
8.00.760 SQL Server 2000 with Service Pack 3a
8.00.2039 SQL Server 2000 with Service Pack 4
9.00.1399 SQL Server 2005 RTM
9.00.2047 SQL Server 2005 with Service Pack 1
9.00.3042.00 SQL Server 2005 with Service Pack 2
9.00.3042.01 SQL Server 2005 with Service Pack 2a
9.00.3050 SQL Server 2005 with Service Pack 2 with Hot Fix
9.00.3054 KB934458 - Fixes maintenance plans losing details
10.00.1075.23 SQL Server 2008 November CTP
10.00.1600 SQL Server 2008 RTM
10.00.2531 SQL Server 2008 with Service Pack 1
I can see from referencing the above table that I am running SQL Server 2005 with Service Pack 1. Perhaps in future releases of SQL Server, Microsoft will bring back all the information in a select @@version query in text and numerical format, so I don't have to look it up each time.
Send to a Friend Printer Friendly Version
Top of Page
© Norb Technologies 2007-2010 Privacy Policy Norb Technologies Devdex Feedback Home
Feedback Form