SQL Server Version History - From 6.5 to 2008
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 lastelatestice 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 |
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.
End of Essential Guide
For further essential guides, technical articles, blogs, newsgroups, forums, links and more, please visit the SQL Server Club site at www.sqlserverclub.com