Post reply

Data Verification

  • June 21, 2007 at 3:30 am

    #196425

    BCP'ing an entire database from 6.5 to file to 2000. I then want to run a script on each server to get the row count of every table for an easy comparison?

    I don't want to use select count for performance reasons.

    I'm being thick but I'm under the cosh sadly!

  • June 21, 2007 at 9:00 am

    #714387

    Exactly which performance reasons are you concerned about that would keep you from using SELECT COUNT(*)???

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • June 21, 2007 at 9:24 am

    #714398

    Execute DBCC UPDATEUSAGE(0) for the current database and then take a look at the ROWS column of the sysindexes table.  If you join the ID column of sysindexes with the ID column of sysobjects, you can get the name of the table, as well.  Don't forget to specify 'U' for the XTYPE column in sysobjects...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 21, 2007 at 9:32 am

    #714404

    If your indexes are up to date, count(*) works great. I'd take the performance hit and use every measure you can think of to verify the data is there. A shortcut isn't worth a problem.

  • June 21, 2007 at 9:56 am

    #714412

    Thats the baby! Thankye very much.

     

    I feel like count(*) on a table of 8 million records and 499 other tables of varying sizes up to a million would not be worthwhile when i have a table of row data on tap (live system and all).

  • June 22, 2007 at 8:42 am

    #714609

    Though you don't like counts, I'm doing something along these lines:

    declare @cmd1 varchar(4000)

    set @cmd1 = 'select ''?'', checksum_agg(binary_checksum(*)), count(*) from ?'

    exec sp_msforeachtable @command1=@cmd1

    By comparing the aggregate checksums, I have much better confidence that a restore is actually good. However, since you're moving between versions of SQL Server, I don't know if comparing aggregate checksums would be a valid comparison.

    I use this with multi-gigabyte DBs with no problems. My largest DB is only 140ish gig, and it's not really a production DB (raster images for GIS) so I don't know how this would work for your environment.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • June 22, 2007 at 1:06 pm

    #714674

    My pleasure... however, Wayne is correct... his method not only inherently checks the row counts, it also checks the actual data...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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