There are times where you need to know what version of SQL Server is installed. Usually you want to know which version and which service pack has been applied. There have been a few isolated cases over the years where we saw bugs go away or significant performance boosts by merely installing the latest service pack. It’s less of an issue with 2005, but with SQL Server 2000, we wanted to make sure the user installed the latest service packs to block against stuff like the “Slammer” worm.
The following bit of T-SQL will send back the version information in easy to process pieces
SELECT
SERVERPROPERTY(‘productversion’) AS ProductVersion, SERVERPROPERTY (‘productlevel’) AS ProductLevel, SERVERPROPERTY (‘edition’) AS Edition
For SQL Server 2005, you could get back something like this:
ProductVersion ProductLevel Edition
--------------- ------------- ----------------
9.00.2047.00 SP1 Standard Edition
Which indicates the Standard Edition of SQL Server 2005, with Service Pack 1 installed. You can also get most of that information with
select
@@version
But you would have to parse out the version from a block of text like this:
----------------------------------------------------------------------
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 2)
Using SERVERPROPERTY (2000, 2005 or 2008) is much easier than parsing that block of text. To determine which version is running based just on the version number, Microsoft has a KB article that lists all of the releases under KB321185.