How to tell what SQL Server version you are running
Written By: Greg Robidoux -- 12/21/2006
-- read/post comments
-- print --
Rating:
 
Rate
Problem
One issue that I am often faced with is determining what service pack is installed on the SQL Server. Until recently the version of SQL Server that was installed was just a number, but now with the later service releases for SQL Server 2000 you can also see what service pack is installed. For SQL Server 2005 it still only shows you the version number that is installed. So how do you know what service pack is installed?
Solution
There are two ways to determine the version of SQL Server that is installed.
The first is by using either Enterprise Manager or Management Studio and right clicking on the database instance name and selecting properties. In the general section you will see information such as on the following screenshots. The "Product version" or "Version" gives you a number of the version that is installed. As you can see with the SQL Server 2000 screenshot it also shows you the service pack that is installed.
SQL Server 2000
SQL Server 2005
Another option is to run the T-SQL command to give you this information. As you can see the output from both versions is pretty much the same. The one thing that you do not get when you do this is the Service Pack name like you do in Enterprise Manager.
SELECT @@VERSION
When this is run it will give you information such as the following:
SQL Server 2000
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
SQL Server 2005
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
So now that you have this number such as 9.00.1399.06 or 8.00.760 what do these even mean? Basically the 8.0 is for SQL Server 2000 and the 9.0 is for SQL Server 2005. Here are a couple of links to other articles that give you the details of what is installed for SQL Server 6.0, 6.5, 7.0, 2000 and 2005.
Next Steps
- Keep these links handy to determine what version of SQL Server you are running
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
