Up through SQL Server 7.0, to determine the level of SQL Server, the routine was to execute SELECT @@VERSION, note the version number, and look it up in the table on the Microsoft site -- http://support.microsoft.com/kb/321185 .

With SQL Server 2000, SELECT SERVERPROPERTY ('ProductLevel') was introduced, which returns "RTM", "SPn", or "Bn", for Release to Manufacturing, Service Pack n, or Beta n.

When looking at the level of various SQL Servers, this always seemed like unnecessarily involved syntax to me - why not just add the SP number to SELECT @@VERSION?  @@VERSION returns a wealth of information beyond just the version, and getting it all from SELECT SERVERPROPERTY isn't even possible.  In fairness, SELECT SERVERPROPERTY makes it easier to extract version info from code, instead of having to parse the string returned by @@VERSION.

With SQL Server 2008, SELECT @@VERSION on an SP1 install returns this (results may vary depending on the server OS):

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM) 

Unfortunately, this change hasn't trickled down to SQL Server 2005 SP3.

Here's a script listing many different sources of SQL Server version information.

SELECT @@VERSION
GO

SELECT SERVERPROPERTY('ProductVersion') 'Version', SERVERPROPERTY ('ProductLevel') 'Service Pack', SERVERPROPERTY ('Edition') 'Edition' 
GO

-- Stored procedure, documented in Books Online, that returns numerous key / value pairs including version information.
-- There doesn't seem to be a correlation between the key names here and SERVERPROPERTY values;
-- for example, 'ProductVersion' is returned by xp_msver, but 'ProductLevel' is not.
xp_msver
GO

-- Stored procedure, documented in Books Online, that returns numerous key / value pairs including version information.
-- For SQL Server 2008, DBMS_VER returns "Microsoft SQL Server Yukon - 10.0.1600".
sp_server_info
GO

-- Stored procedure, not documented in Books Online, that returns the version string.
sp_MSgetversion
GO

-- @@MicrosoftVersion returns a decimal value which, when converted to varbinary, yields (for example) 0x09000FC3.  
-- Enter "convert 0x0FC3 to decimal" in Google, and the result is 4035.  Thus the overall MicrosoftVersion is 09.00.4035.
SELECT CONVERT(VARBINARY, @@MicrosoftVersion)
GO

-- get version info from the registry (SQL 2005 key names - keys may vary)
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\', @value_name='Version'
GO
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\', @value_name='Edition'
GO
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\', @value_name='SP'
GO

-- get version info from the registry (SQL 2008 key names - keys may vary)
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Setup\', @value_name='Version'
GO
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Setup\', @value_name='Edition'
GO
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Setup\', @value_name='SP'
GO