SQL Service Pack Versions

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
8.00.194	RTM
8.00.384	SP1
8.00.534	SP2
8.00.760	SP3
8.00.2039	SP4
SQL 2005
9.00.1399	RTM
9.00.2047	SP1
9.00.3042	SP2
9.00.4035	SP3
9.00.5000	SP4
SQL 2008
10.00.1600.22	RTM
10.00.2531	SP1
10.00.4000	SP2
10.00.5500	SP3
SQL 2008 R2
10.50.1600.1	RTM
10.50.2500	SP1
10.50.4000	SP2
SQL 2012
11.00.2100.60	RTM
11.00.3000	SP1