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, 2010 at 05:56 AM in Default

avatar image

170 11 11 16

excellent question (+1). I look forward to seeing the answer to this. (I guess it wouldn't make a difference)

Oct 04, 2010 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, 2010 at 06:01 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

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, 2010 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, 2010 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, 2010 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, 2010 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, 2010 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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Oct 04, 2010 at 05:56 AM

Seen: 1163 times

Last Updated: Oct 04, 2010 at 05:56 AM

Copyright 2018 Redgate Software. Privacy Policy