When you upgrade your system to SQL Server 2008 R2, you’ll know that the instance is at that version by using the standard commands like SELECT @@VERSION or EXEC xp_msver. My system came back with this info when I typed those:
Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Developer Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2) (Hypervisor)
|1||ProductName||NULL||Microsoft SQL Server|
|3||Language||1033||English (United States)|
|4||Platform||NULL||NT INTEL X86|
|7||FileDescription||NULL||SQL Server Windows NT|
|8||FileVersion||NULL||2009.0100.1600.01 ((KJ_RTM).100402-1540 )|
|10||LegalCopyright||NULL||Microsoft Corp. All rights reserved.|
|11||LegalTrademarks||NULL||Microsoft SQL Server is a registered trademark of Microsoft Corporation.|
But a database properties are separate from the Instance. After an upgrade, you always want to make sure that the compatibility options (which have much to do with how NULLs and other objects are treated) is at what you expect. For the most part, as long as the application can handle it, I set my compatibility levels to the latest version. For SQL Server 2008, that was “10.0” or “10”. You can do this with the ALTER DATABASE command or you can just right-click the database and select “Properties” and then “Database Options” in SQL Server Management Studio.
To check the database compatibility level, I use this query:
SELECT name, cmptlevel FROM sys.sysdatabases
When I did that this morning I saw that the databases (all of them) were at 10.0 – not 10.5 like the Instance. That’s expected – we didn’t revise the database format up with the Instance for this particular release.
Didn’t want to catch you by surprise on that. While your databases should be at the “proper” level for your situation, you can’t rely on the compatibility level to indicate the Instance level.
More info on the ALTER DATABASE command in SQL Server 2008 R2 is here: http://technet.microsoft.com/en-us/library/bb510680(SQL.105).aspx