March News: @NTSSUG and #SQLSat35

First let me announce that we got so many really good entries for the SQL Server Musical Contest that we're taking the weekend to decide on the winner. Keep your hats on, kids…we'll get there.

Last night we had the North Texas SQL Server User Group meeting for March. Thanks to our sponsor Odyssey Information Services for the pizza and giveaways!

Sean McCown (blog) – you may've heard of him – gave part 2 of his Ground Zero SSIS course (Twitpic) Video will be on MidnightDBA.com next week. Ou can find part 1 on MidnightDBA.com now, on the Dev page (SSIS tab). He got started late after technical problems, but it was a really great talk, and well received.

Drew Minkin (LinkedIn profile) gave a talk on Data Mining for SQL Developers (Tweet photo): "This session will introduce SQL Developers to how SQL Server Analysis Services (SSAS)'s Data Mining algorithms can enhance your applications.  Topics covered will include basics of data mining theories and practices, data life cycle managment, building a data mining structure and model, and choosing an algorithm."  I'd love to tell you how it went, but I was locked in a room with the other SQL Saturday #35 planners. Worry not!  You and I can both catch up on the session next week, because we're publishing his session on MidnightDBA.com too!

SQL Saturday #35 – Dallas 

We're in the final-final stages of, uh, finalizing the speaker schedule. If you submitted a session, we'll let you know within the next week or so. We'll publish the speaker schedule for all by April 2.

The conference hotel information is now available on the SQL Saturday 35 home page under Event News. When making reservations, keep in mind that we're having an after party following the conference…details coming soon!

Speaking of SQL Saturday #35, let's give a big welcome to our new planning committee volunteers: Ryan Adams (Twitter | Blog) , David Stein (Twitter | Blog), and Vic Prabhu (Twitter)!  If you're interested in volunteering, contact us at sqlsaturday35@sqlsaturday.com!

Happy days,
Jennifer McCown
http://www.MidnightDBA.com

 

Dirty Tricks – Comparing rowcount

Content Rating: Beginner

I don't have many cool tools at my current gig to help me out, so I'm often forced to come up with quick and dirty solutions of my own. Today I have to compare row counts in two identical databases, one in Dev and one in QA. Here's what I did.

1. Generate a list of select count(*) statements

— Get the SELECT statements for rowcounts for all user tables

select 'select ''' + name + ''' nm, count(*) cnt from ' + [name] + ' UNION '

from sys.objects where type_desc = 'USER_TABLE' order by [name]

This generates a list of statements like this:

select 'Client' nm, count(*) cnt from Client UNION

2. Copy-paste that list into a query window and run. (Note that we remove the last Union statement).

select 'Client' nm, count(*) cnt from Client UNION

select 'Supplier' nm, count(*) cnt from Client UNION  
select 'Anchor' nm, count(*) cnt from Client

This gets us a resultset like this:

nm          cnt 
Client       12333 
Supplier    401
Anchor     32

3. Do the same on the other environment.

 Now at this point, you have your data. If there are few enough tables, you can compare by hand. Or you could run each set of SELECT statements with Save As File, import those file to your sandbox DB as two tables, and compare rowcounts that way.

Edit: That's the old school way of getting rowcounts from pre-DMV days. So if you're using SQL Server 2000 or earlier, or need exact rowcounts, use the above.  If you'd like something faster and easier, check out Aaron Bertrand's (blog | twitter) solutions in the comments below!

Another day, another dirty little trick from the MidnightDBA.  Show us your dirty tricks, or a better way to compare – either blog or post in comments!

-Jen McCown
http://www.MidnightDBA.com

Today’s Announcements

Attention students, attention. Here are your morning announcements:

  • Your MidnightDBAs are running a fun contest this week that already has some excellent entries. Read and enter here.
  • The North Texas SQL Server User Group (NTSSUG) meeting is this Thursday. Sean will present part 2 of "Ground Zero SSIS for People who Want to Learn SSIS and Want to Learn to do Other Stuff Good too" at 6pm, and Andrew Minkin will give a talk on Data Mining starting at 7. We will have the usual pizza, soda, and raffle for books and swag.
  • Uh, there's a Toyota Prius in the lot with your lights on, license plate BT7 OL4.
  • SQL Saturday #35 is on track for May 22, 2010. Registration is open, the only cost is $10 for lunch; there is a vegetarian option.  We'll have the finalized speaker list and schedule later this month.
  • This week's blogcast will be delayed due to a sudden onset of Battlestar Galactica addiction.
     
  • That is all.

Jennifer McCown