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:


For SQL Server 2014:

@@version Version and Service Pack
12.0.4100.10 SQL Server 2014 with Service Pack 1
12.00.2000.80 SQL Server 2014 RTM.

For SQL Server 2012:

@@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.

For SQL Server 2008 R2:

@@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.

For SQL Server 2008:

@@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.

For SQL Server 2005:

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.

For SQL Server 2000:

@@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

For SQL Server 7:

@@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

For SQL Server 6.5:

@@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

For SQL Server 6:

@@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: 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.