Know your SQL Server version

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.