SQL 2000Determining the SQL Server Version
Software developed for use with SQL Server may be executed on many different versions of the database management system (DBMS). Sometimes you will need to know the edition and service pack level in use to optimise a program or to avoid known issues.
ServerProperty Function
The ServerProperty function can be executed using Transact-SQL (T-SQL) to determine information about the current SQL Server instance. The function permits the extraction of three key property values that describe the version of the instance. These are the product version, level and edition.
Product Version
The SQL Server product version is a multi-part number that can be used to identify the major release number and service pack level of the DBMS. To retrieve the number, the following command is executed:
SELECT serverproperty('ProductVersion')The command returns one of the many possible version numbers for SQL Server. The following table lists the meanings of some of the key version numbers available at the time of writing. The table is not exhaustive as version numbers change as patches are applied. However, the number always increases with new versions making it easy to check for a minimum version requirement.
NB: The first part of the number can be used to quickly identify the major release of the DBMS; version 8 for SQL Server 2000, version 9 for SQL Server 2005, etc.
| Version Number | Description |
|---|
| 8.00.194 | SQL Server 2000 | | 8.00.384 | SQL Server 2000 Service Pack 1 | | 8.00.532 | SQL Server 2000 Service Pack 2 | | 8.00.760 | SQL Server 2000 Service Pack 3 (or 3a) | | 8.00.818 | SQL Server 2000 Service Pack 3 with Cumulative Security Patch MS03-031 | | 8.00.2039 | SQL Server 2000 Service Pack 4 | | 9.00.1399 | SQL Server 2005 | | 9.00.2047 | SQL Server 2005 Service Pack 1 | | 9.00.2153 | SQL Server 2005 with Rollup for SP1 | | 9.00.3042 | SQL Server 2005 Service Pack 2 | | 9.00.3152 | SQL Server 2005 with Cumulative Update 1 for SP2 | | 9.00.3175 | SQL Server 2005 with Cumulative Update 2 for SP2 | | 9.00.3186 | SQL Server 2005 with Cumulative Update 3 for SP2 | | 9.00.3200 | SQL Server 2005 with Cumulative Update 4 for SP2 | | 9.00.3215 | SQL Server 2005 with Cumulative Update 5 for SP2 |
Product Level
The product level property returns a human-readable value indicating the service pack level of SQL Server that is in use. The value does not include the major release number (eg. 2000, 2005) for the DBMS so is generally used in conjunction with the product version property.
To use the property, the ServerProperty function is executed with the "ProductLevel" parameter.
SELECT serverproperty('ProductLevel')
Product Edition
The product edition property allows the determination of the edition of the current SQL Server instance. This allows you to distinguish between Enterprise edition, Developer Edition, Express Edition, etc. The text that is returned from the property is human-readable.
SELECT serverproperty('Edition')
|