Cry How to...
Determine which service pack is installed (or which version of SQL Server)
To determine which SQL Server service pack is installed simply obtain the current version number of SQL Server and from this the service pack number can be looked up.
To obtain the version number using SQL:
select @@version
Alternately, if you are using SQL Server 2005 or later then the version number is shown next to the server name in Microsoft SQL Server Management Studio. For SQL Server 7 and SQL Server 2000 using SQL Server Enterprise Manager expand the list of servers, right click the server and select 'Properties'.
Then look the version number up in one of the tables below, which cover:
- SQL Server 2014
- SQL Server 2012
- SQL Server 2008 R2
- SQL Server 2008
- SQL Server 2005
- SQL Server 2000
- SQL Server 7
- SQL Server 6.5
- SQL Server 6
@@version |
Version and Service Pack |
|---|---|
| 12.0.4100.10 | SQL Server 2014 with Service Pack 1 |
| 12.00.2000.80 | SQL Server 2014 RTM. |
@@version |
Version and Service Pack |
|---|---|
| 11.00.6020.00 | SQL Server 2013 with Service Pack 3. |
| 11.00.5058.00 | SQL Server 2012 with Service Pack 2. |
| 11.00.3000.00 | SQL Server 2012 with Service Pack 1. |
| 11.00.2100.60 | SQL Server 2012 RTM. |
@@version |
Version and Service Pack |
|---|---|
| 10.50.6000.34 | SQL Server 2008 R2 with Service Pack 3. |
| 10.50.4000.0 | SQL Server 2008 R2 with Service Pack 2. |
| 10.50.2500.0 | SQL Server 2008 R2 with Service Pack 1. |
| 10.50.1600.1 | SQL Server 2008 R2 RTM. |
@@version |
Version and Service Pack |
|---|---|
| 10.00.5500.00 | SQL Server 2008 with Service Pack 3. |
| 10.00.4000.00 | SQL Server 2008 with Service Pack 2. |
| 10.00.2723.00 | SQL Server 2008 with Cumulative Update 3 (includes SP 1 and hot-fixes) |
| 10.00.2531.00 | SQL Server 2008 with service pack 1. |
| 10.00.1600.00 | SQL Server 2008, with no service pack applied. |
Subsequent numbers (higher than 9.00.4035) indicate security updates released after SQL Server 2005 service pack 3. See http://support.microsoft.com/kb/960598/ for details.
@@version |
Version and Service Pack |
|---|---|
| 9.00.5000 | SQL Server 2005 with Service Pack 4. |
| 9.00.4035 | SQL Server 2005 with Service Pack 3. |
| 9.00.3054 | SQL Server 2005 with Service Pack 2 GDR2. |
| 9.00.3050 | SQL Server 2005 with Service Pack 2 GDR1. |
| 9.00.3042 | SQL Server 2005 with Service Pack 2. |
| 2005.90.2047 | SQL Server 2005 with Service Pack 1. |
| 2005.90.1399 | SQL Server 2005 with no service pack applied. |
@@version |
Version and Service Pack |
|---|---|
| 8.00.2039 | SQL Server 2000 with Service Pack 4 |
| 8.00.760 | SQL Server 2000 with Service Pack 3a |
| 8.00.760 | SQL Server 2000 with Service Pack 3. |
| 8.00.532 | SQL Server 2000 with Service Pack 2 |
| 8.00.384 | SQL Server 2000 with Service Pack 1 |
| 8.00.194 | SQL Server 2000 with no service pack applied |
@@version |
Version and Service Pack |
|---|---|
| 7.00.1063 | SQL Server 7 with Service Pack 4 |
| 7.00.961 | SQL Server 7 with Service Pack 3 |
| 7.00.842 | SQL Server 7 with Service Pack 2 |
| 7.00.699 | SQL Server 7 with Service Pack 1 |
| 7.00.623 | SQL Server 7 with no service pack applied |
@@version |
Version and Service Pack |
|---|---|
| 6.50.479 | SQL Server 6.5 with Service Pack 5a (updated) |
| 6.50.416 | SQL Server 6.5 with Service Pack 5a. |
| 6.50.415 | SQL Server 6.5 with Service Pack 5 |
| 6.50.281 | SQL Server 6.5 with Service Pack 4 |
| 6.50.258 | SQL Server 6.5 with Service Pack 3 |
| 6.50.240 | SQL Server 6.5 with Service Pack 2 |
| 6.50.213 | SQL Server 6.5 with Service Pack 1 |
| 6.50.201 | SQL Server 6.5 with no service pack applied |
@@version |
Version and Service Pack |
|---|---|
| 6.00.151 | SQL Server 6 with Service Pack 3 |
| 6.00.139 | SQL Server 6 with Service Pack 2 |
| 6.00.124 | SQL Server 6 with Service Pack 1 |
| 6.00.121 | SQL Server 6 with no service pack applied |
SQL Server Service packs can be downloaded from the Microsoft web site.
See also Microsoft Knowledge Base article 321185.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.