SQL Service Pack Versions

I know this information is available in multiple other places, but I can never find it when I need it, so I'm putting it here for my benefit ... and for anyone who stumbles across this!

First up, it is possible to determine the service pack level directly from SQL using the SERVERPROPERTY function with the 'productlevel' key, for example:

SELECT @@SERVERNAME AS "SQLInstance" ,

SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') AS "Server",

SERVERPROPERTY ('ProductVersion') AS "Build",

SERVERPROPERTY ('edition') AS "Edition",

SERVERPROPERTY ('productlevel') AS "Patch"

would return results similar to

SQLInstance	Server	Build	Edition	Patch
GRUMPY\DBA	GRUMPYCLUSTER1	10.50.4000.0	Enterprise Edition (64-bit)	SP2

Loop through all your servers and you can quickly keep track of which servers are at what build, and what needs patching.

However, if you just want to check what patch level a certain build number equates to (since you can see that as soon as you connect in SSMS), here are the major releases and their version numbers:

SQL 2000	Shiloh
8.00.194	RTM
8.00.384	SP1
8.00.534	SP2
8.00.760	SP3
8.00.2039	SP4
SQL 2005	Yukon
9.00.1399	RTM
9.00.2047	SP1
9.00.3042	SP2
9.00.4035	SP3
9.00.5000	SP4
SQL 2008	Katmai
10.00.1600	RTM
10.00.2531	SP1
10.00.4000	SP2
10.00.5500	SP3
10.00.6000	SP4
SQL 2008 R2	Kilimanjaro
10.50.1600	RTM
10.50.2500	SP1
10.50.4000	SP2
10.50.6000	SP3
SQL 2012	Denali
11.00.2100	RTM
11.00.3000	SP1
11.00.5058	SP2
11.00.6020	SP3
SQL 2014	Hekaton
12.00.2000	RTM
12.00.4050	SP1 (original)
12.00.4100	SP1 (re-released)
SQL 2016
13.00.200	CTP