What SQL Server service pack & sort order do I have installed ?

If you've used SQL Server for a while,for checking Sort Order sp_helpsort and for summery of SQL servers config sp_server_info for service pack information you're probably familiar with the @@version function. run select @@version in Query Analyzer. Selecting the function in a query window produces the following results on my system. 

From this, you can quickly determine that I'm running SQL Server 2005 Developer Edition version 9.00.3054.00 on an Intel X86 processor. It may also appear that I've installed SQL Server SP2, however that is rather misleading. The "Service Pack 2" text in the above image refers to the operating system, not the SQL Server system.

To determine SQL Server's service pack level, use the built-in SERVERPROPERTY function with appropriate parameters - EDITION, PRODUCTLEVEL, and PRODUCTVERSION.

For example the following queries produce the results shown in the image below.

select serverproperty('EDITION') As SQL_Server_Edition
select serverproperty('PRODUCTLEVEL') As SQL_Product_Level
select serverproperty('PRODUCTVERSION') As SQL_Version 

From this, you can see that I am indeed running SQL Server 2005 Developer Edition updated with Service Pack 2.

The SERVERPROPERTY function is part of SQL Server 2000 and greater. Prior to that, we had to resort to equating the product version to a service pack level. Here's a link to a KB article with more details for v7.0 and earlier.