Thursday, May 21, 2009

Microsoft SQL Server versions and editions

The purpose of this article is to describe how to identify your current Microsoft SQL Server version number and the corresponding product or service pack level. It also describes how to identify the specific edition of SQL Server you are using in your environment.

How to determine which version of SQL Server 2008 is running

To determine which version of Microsoft SQL Server 2008 is running, connect to SQL Server 2008 by using SQL Server Management Studio, and then run the following Transact-SQL statement.


SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The following results are returned:
The product version (for example, 10.0.1600.22)
The product level (for example, RTM)
The edition (for example, Enterprise)
For example, the results resemble the following.

10.0.1600.22 RTM Enterprise Edition

The following table lists the product version and file version (Sqlservr.exe) numbers for SQL Server 2008:


Product Version File Version (SQLSERVR.EXE)

RTM 10.0.1600.22 2007.100.1600.0
SQL Server 2008 Service Pack 1 10.0.2531.0 2007.100.2531.0

How to determine which version of SQL Server 2005 is running

To determine which version of Microsoft SQL Server 2005 is running, connect to SQL Server 2005 by using SQL Server Management Studio, and then run the following Transact-SQL statement.


SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The following results are returned:
The product version (for example, 9.00.1399.06)
The product level (for example, RTM)
The edition (for example, Enterprise Edition)

For example, the results resemble the following.

9.00.1399.06 RTM Enterprise Edition

The following table lists the product version and file version (Sqlservr.exe) numbers for SQL Server 2005:


Product Version File Version (SQLSERVR.EXE)

RTM 9.00.1399 2005.90.1399
SQL Server 2005 Service Pack 1 9.00.2047 2005.90.2047
SQL Server 2005 Service Pack 2 9.00.3042 2005.90.3042
SQL Server 2005 Service Pack 3 9.00.4035 2005.90.4035

How to determine which version of SQL Server 2000 is running

To determine which version of SQL Server 2000 is running, connect to SQL Server 2000 by using Query Analyzer, and then run the following code.


SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The following results are returned:

The product version (for example, 8.00.534)
The product level (for example, "RTM" or "SP2")
The edition (for example, "Standard Edition").

For example, the results resemble the following:

8.00.534 SP2 Standard Edition

The following table lists the product version and file version (Sqlservr.exe) numbers for SQL Server 2000:


Product Version File Version (SQLSERVR.EXE)

RTM 8.00.194 2000.80.194.0
SQL Server 2000 SP1 8.00.384 2000.80.384.0
SQL Server 2000 SP2 8.00.534 2000.80.534.0
SQL Server 2000 SP3 8.00.760 2000.80.760.0
SQL Server 2000 SP3a 8.00.760 2000.80.760.0
SQL Server 2000 SP4 8.00.2039 2000.8.00.2039

For releases of SQL Server prior to SQL Server 2000, check Microsoft Support KB article 31185. Keep in mind also there were a number of Cumulative Updates for each version, which are not listed above. For a detail list of Cumulative Updates version information check SQL Team's article on SQL Server Versions.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

2 comments:

victoriayudin said...

Hi Mariano,

I have always used select @@version to get this information. Is there a difference?

Thanks,
-Victoria

Mariano Gomez said...

Victoria,

The SERVERPROPERTY function returns individual properties that relate to the version information whereas the @@VERSION function combines the output into one string. If your application requires individual property strings, you can use the SERVERPROPERTY function to return them instead of parsing the @@VERSION results.

MG.-
Mariano Gomez, MVP
http://www.maximumglobalbusiness.com