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 |
