x

Compatibility level and Service Packs

If I attach a 2005 database to a 2008 instance and run it at compatibility level 90 (i.e. 2005) does the orignal 2005 database service pack level make any difference? In other words if I had two identical 2005 databases X and Y with X running on a server with no service pack (RTM) and Y running on a 2005 SP3 instance will they be treated any differently if they are both attached/restored to a 2008 instance?
I can not find any doumentation on this subject so please point me in the right direction if there is something relevant out there.
more ▼

asked Oct 04 '10 at 05:56 AM in Default

StuKay gravatar image

StuKay
170 9 9 11

excellent question (+1). I look forward to seeing the answer to this. (I guess it wouldn't make a difference)
Oct 04 '10 at 05:58 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
No, because the compatibility level you're running under is 2008. So while you still have the databases in something resembling 2005, they're not in 2005, they're in 2008, just running under the compatibility level.
more ▼

answered Oct 04 '10 at 06:01 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 19 21 74

So anything that was fixed using SP3 on 2k5 would be "fixed" on the database that was restored from a 2k5 SP2 machine?
Oct 04 '10 at 06:03 AM WilliamD
Well... maybe. It really depends right. Is whatever was fixed the same as something in 2008? It's conceivable that it's broken there too and waiting on an SP or CU.
Oct 04 '10 at 06:13 AM Grant Fritchey ♦♦
TEST! TEST! TEST! Grant is right but (sorry) there could be things that, in a given application, break with a newer SP. The onus is still on you to do your homework.
Oct 04 '10 at 06:21 AM Blackhawk-17
I don't have a case where I could test this. I was wondering if maybe Grant had some info to support his assertion that would explain my added question. I would assume that all would be good, as the broken parts are really less to do with the data stored inside a database, and more to do with the SQL Server Engine itself - which should have all fixes from previously released versions/sps
Oct 04 '10 at 06:26 AM WilliamD
Grant is spot on here. I don't really think the wording of the answer is necessarily 100% clear, because it's the engine that is 2008, and the engine will implement compatibility levels. But the point stands. And, @Blackhawk - yes SPs do introduce new stuff. OBJECT_SCHEMA_NAME was introduced in SP2 for 2005, for instance.
Oct 04 '10 at 07:10 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x27
x8

asked: Oct 04 '10 at 05:56 AM

Seen: 816 times

Last Updated: Oct 04 '10 at 05:56 AM