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.

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

StuKay
150 ● 6 ● 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)

1 answer:

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.

answered Oct 04 '10 at 06:01 AM

Grant Fritchey ♦♦
62.1k ● 12 ● 19 ● 66

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)