Joe Webb Blog

Musing and observations about SQL Server, other technogies, and sometimes just life in general

What SQL Server service pack do I have installed?

If you've used SQL Server for a while, you're probably familiar with the @@version function. Selecting the function in a query window produces the following results on my system.

Version-2008-04-01

From this, you can quickly determine that I'm running SQL Server 2005 Developer Edition version 9.00.3054.00 on an Intel X86 processor. It may also appear that I've installed SQL Server SP2, however that is rather misleading. The "Service Pack 2" text in the above image refers to the operating system, not the SQL Server system.

To determine SQL Server's service pack level, use the built-in SERVERPROPERTY function with appropriate parameters - EDITION, PRODUCTLEVEL, and PRODUCTVERSION.

For example the following queries produce the results shown in the image below.

Version2-2008-04-01

From this, you can see that I am indeed running SQL Server 2005 Developer Edition updated with Service Pack 2.

The SERVERPROPERTY function is part of SQL Server 2000 and greater. Prior to that, we had to resort to equating the product version to a service pack level. Here's a link to a KB article with more details for v7.0 and earlier.

Cheers!

Joe



kick it on DotNetKicks.com

Legacy Comments


Jeff Moden
2009-05-13
re: What SQL Server service pack do I have installed?
Good tip and pretty pictures but can't copy code from the pictures. Would be much better if the code was just text to make copy and paste a little easier.

Joe Webb
2009-05-13
re: What SQL Server service pack do I have installed?
Good point. I'll keep that in mind for future posts.

BTW - I'm also now blogging over on http://www.webbtechsolutions.com/blog

Thanks!

Joe



The Helpful Hairy Fairy
2011-02-16
re: What SQL Server service pack do I have installed?
select serverproperty('EDITION') As Edition
select serverproperty('PRODUCTLEVEL') As [Product Level]
select serverproperty('PRODUCTVERSION') As Version

B.H.
2011-06-22
re: What SQL Server service pack do I have installed?
I have a Small Business Server 2003 R2 SP2 Enterprise Edition which comes with SQL Server 2005 pre-installed. When I do "select serverproperty('PRODUCTLEVEL')", I get RTM. I assume this stands for "Run Time Monitor". I don't see a Service Pack number. Is there any other way to determine the service pack?

Thank you.

MB
2011-08-10
re: What SQL Server service pack do I have installed?
@BH: RTM in this context is "released to manufacturing", as in the original version as shipped to the CD duplication factory.