Post reply

This can't happen. Different results SQL 2k and Sql 2005

  • November 1, 2007 at 9:06 am

    #62485

    I know this cannot happen ... but it does. At least for me!

    I have a table from a production database on SQL 2005

    BookersInfo2

    I want to delete certain rows from it.

    I run a SELECT INTO ##B query to generate the rows to delete. I'm only doing it this way to break the problem down.

    I run

    DELETE BookersInfo2

    FROM BookersInfo2 b2 INNER JOIN ##B bb

    ON b2.surrogate_key = bb.surrogate_key -- surrogate_key is unique

    All is well

    I run

    SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM BookersInfo2

    and get my checksum

    I repeat this DELTEE with the same data on a SQL 2000 database.

    I repeat the SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM BookersInfo2 on the SQL 2000 database and get a different value

    I repeat this a few times on SQL 2000 and SQL 2005

    Before the delete the Checksums on BookersInfo2 are the same between 2000 and 2005 and are always the same values.

    Similaly the Checksums on ##B are the same between 2000 and 2005 and are always the same values.

    However after the DELETE the Checksums on BookersInfo2 on SQL 2005 are always the same values, however the Checksums on BookersInfo2 on SQL 2000 are different to those on SQL 2005 and are differ with each run.

    Row counts are the same.

    COLLLATION is the same on 2000 and 2005 both on the DB and TempDB.

    Any thoughts or should I just get my coat and go home.

  • November 2, 2007 at 9:02 am

    #747800

    Have you got the same index in 2000 as in 2005? If not then SQL Server will randomize the rows and give a different checksum result.

    Jez

  • November 2, 2007 at 10:33 am

    #747858

    If you run a select instead of a delete on the rows and compare the two values (2k and 2k5), is the data the same?

    I'd be suspicious of something changing in the checksum feature as mentioned above.

  • November 5, 2007 at 5:04 am

    #748181

    Thanks Jez & Steve,

    Of course I _KNEW_ my data was the same....but it wasn't.

    I was using a SELECT INTO and the IDENTITY function with an ORDER BY which I was sure would always generate the same data.

    http://support.microsoft.com/kb/273586/en-us

    That was a hard learnt lesson.

  • November 5, 2007 at 6:12 am

    #748200

    Maybe it doesn't apply to your case but be aware that there is a nasty gotcha with BINARY_CHECKSUM(*). The checksum is determined by column order rather than name. So if the column order is different then the checksums will be too, even if the data is the same. To avoid that problem, do not use BINARY_CHECKSUM(*). Use BINARY_CHECKSUM(col1, col2, col3, ... etc)

  • November 5, 2007 at 6:57 am

    #748215

    Thanks David, that too is news to me.

    Are there any *Nice* gotchas?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply