If you've used SQL Server for a while, you're probably familiar with the @@version function. 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.
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.
Cheers!
Joe
