How to check if SP1 for SQL Server 2008 R2 is already installed?

I have trouble figuring out if the SP1 is already installed. I don't think I could check it from the Management Studio as its开发者_Go百科 a different application. But the SQl server it self has no UI to check under "about". :)

Any ideas?

There is not SP1 for SQL Server 2008 R2 just yet.....

But to check, you can inspect the productlevel server property:

SELECT  
  SERVERPROPERTY('productlevel') 

This will contain RTM for the original RTM version (as it is in my case with SQL Server 2008 R2 now), or it will contain info about the service pack installed.

I typically use this SQL query:

SELECT  
    SERVERPROPERTY('productversion') as 'Product Version', 
    SERVERPROPERTY('productlevel') as 'Patch Level',  
    SERVERPROPERTY('edition') as 'Product Edition',
    SERVERPROPERTY('buildclrversion') as 'CLR Version',
    SERVERPROPERTY('collation') as 'Default Collation',
    SERVERPROPERTY('instancename') as 'Instance',
    SERVERPROPERTY('lcid') as 'LCID',
    SERVERPROPERTY('servername') as 'Server Name'

This lists your server version, edition, service pack (if applicable) etc. - something like this:

Product Version  Patch Level  Product Edition             CLR Version  Default Collation     Instance  LCID  Server Name
10.50.1617.0       RTM        Developer Edition (64-bit)  v2.0.50727   Latin1_General_CI_AS   NULL     1033  *********

Update: this answer was correct when it was posted - July 2011.

By now, November 2012, there's SQL Server 2008 R2 Service Pack 2 available for download

Note that there are two versions of Service Pack 1 - the CTP that was released in April, and the final release which should be released any day now. Both of these versions will return "SP1" for ProductLevel, and both will say "Microsoft SQL Server 2008 R2 (SP1) ..." for @@VERSION. The difference will be that @@VERSION for the CTP will next say 10.50.2425 and reflect a date of April 6, 2011; the final release will have a higher build number and reflect a later date (I will try to remember to come back and update this post when the SP has been released for good).

Sorry for joining the discussion late, but for the benefit of future readers, I wanted to make it clear that just relying on ProductLevel saying "SP1" is not the most foolproof way to ensure that you are at the final version of Service Pack 1.

And finally, don't feel bad about getting 2008 & 2008 R2 mixed up. This was a brain-dead Microsoft naming blunder along the lines of TIMESTAMP, DATETIME2 and DACPAC ... and based on the number of people who continue to try to apply 2008 updates to R2 and vice-versa, and the lack of adoption due at least in part to the rather accurate impression that R2 is quite likely the most expensive service pack ever, I hope they've learned how foolish it was.

To check the SQL SERVER Information of Installed Version use this script:

   SELECT 
              SERVERPROPERTY('MachineName') as Host,
              SERVERPROPERTY('InstanceName') as Instance,
              SERVERPROPERTY('Edition') as Edition, /*shows 32 bit or 64 bit*/
              SERVERPROPERTY('ProductLevel') as ProductLevel, /* RTM or SP1 etc*/
              Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else
              'STANDALONE' end as ServerType,
              @@VERSION as VersionNumber

Product level will determine if you have SP1, SP2 Installed or not.