T-SQL Tuesday #002: The Roundup

According to T-SQL Tuesday rules as ratified by me in the first and second T-SQL Tuesday posts, the T-SQL Tuesday host is supposed to post a roundup within two days of the end of the event. So a reasonable person should expect a roundup to be posted by the second Thursday of the month. It gives me no pleasure to admit that I’ve been completely unreasonable and have totally dropped the ball. I’m twothreefour weeks late. (I actually started the post two weeks ago. That’s bad. And now I’m forced to finish it because tomorrow is T-SQL Tuesday #003. That’s very bad.) I have lots of excuses, but none of them are any good. So I hope that everyone who participated will accept my apology.

Now that the unpleasant part of this post is behind us, let’s visit the positive side. The event generated 24 great posts! Apparently everyone out there can relate to being confused by something in SQL Server.

And since I’m three weeks late, this month’s T-SQL Tuesday invitation has already been posted by the host, the much-better-organized-than-I-am Rob Farley (who sent me a link to the post over two weeks before he published it). Once you’re done reading all of the great posts listed below, visit Rob’s blog and get ready for this month’s event.

The posts below are listed roughly in the order in which comments showed up on my blog on the day in question. I’ve also included a couple of stragglers who were late getting their posts in. If I’m three weeks late with the roundup I can surely forgive someone who posted their entry 24 hours behind schedule.

Rob Farley, who as I mentioned before is quite well organized, kicked things off with a cool and quite puzzling puzzle. What happens when you use a HAVING clause without a GROUP BY clause? Check out the post, then read the solution in his followup.

Kalen Delaney’s post popped up next, puzzlingly on an almost identical topic to that of Rob’s. Kalen gives us a bit of a history lesson about some strange situations with non-aggregated columns that used to be possible in older versions of SQL Server.

Sankar Reddy is so puzzled by SQL Server that he posted not just one, but two posts as part of the event. In his first entry, he talks about an issue with transactional replication (always a fun technology to manage). In his second post he discusses the mysterious Sort Warning event.

Jonathan Kehayias is yet another double-poster for the event. In his first post he describes a SQL Server 2008 to 2005 downgrade process and how he worked around the issue of doing the migration for 80 databases. In the second post he shows the twists and turns one can take when trying to do something simple like find out what SQL a request is executing.

Stephen Horne took the opposite end of the “puzzle” challenge, posting an actual puzzle. Can you write an unbeatable tic-tac-toe game in T-SQL? Give it a shot, it’s fun!

Michael Swart comes up with a difficult puzzle of his own. Can you reverse-engineer an MD5 hash? You shouldn’t, in theory, be able to do so (at least, easily), but here are some hints… And if you still can’t figure it out, read the solution.

Another Michael–Michael Coles–jumped the gun a bit, posting his first T-SQL Tuesday post a day early. Its topic is the ever-puzzling task of creating properly-ordered build scripts. After I mentioned that his post wasn’t technically eligible for the event, Michael pulled out a second great post, this time on the topic of SQL Server’s often-misunderstood extended properties feature.

David Leibowitz brings a nice post on a topic that many SSRS developers get wrong: how to properly do multivalue parameters with stored procedures.

Another SSRS-related issue comes up in the puzzle posted by Brian Garrity. How do you tell whether FMTONLY is turned on?

Beginning Spatial provided a few spatial puzzles for those of you interested in that area.

Mladen Prajdic knows that there are many ways subqueries can be misunderstood… And shows us another. How many times is that subquery really evaluated?

Grant Fritchey may be scary, but is certainly not afraid of SQL Server; he says in his post that it was difficult to think of a puzzling situation. But after some brainstorming he came up with an interesting post about the query optimization process.

Alexander Kuznetsov continues his series of posts on defensive database programming and busts one of the most common patterns I’ve seen–the IF EXISTS(…) UPDATE ELSE INSERT pattern. A very nice read.

Steve Jones did a great job of describing one of the most common puzzling situations SQL programmers can encounter when working with less-than-perfectly-designed databases: implicit conversion issues.

Brad Schulz is up next with a T-SQL Tuesday first: a fairy tale! He uses the story to describe, step-by-step how to write a complex query and why the query optimizer throws exceptions when you try to do various things illogically. A very nice post.

Can anything strange happen when you reconfigure the “max server memory” setting? Ask Aaron Nelson… He was certainly puzzled by the outcome of this seemingly-innocent action.

Quick, which ASCII character is this: ” “? Janusz Marchewa tells us, in his post, how he learned the hard way that not all white space characters are created equally–and how he figured out how to tell the difference.

Despite being late with this roundup post, I did managed to pull together my own entry for the event. If you work with non-typed XML on a regular basis, you might want to read my post to get a bit of a performance boost when using the Nodes method.

Andrew Hogg contributed a post in which he describes the trickery required to query the various DMVs in order to properly do dynamic partitioning. In the end, five views are needed to satisfy what at first seems like an easy query.

Jorge Serrada had a seriously confusing situation involving shared access rights to a program and the requirement that certain XML files get distributed to end-user’s computers before they could run the thing and … Read his post to find out how he resolved this mess.

Closing out the event is Jason Brimhall, who shares an interesting tale of woe: his UDF worked fine in SSMS and not so fine in SQL Agent. An all-too-familiar situation. Read his post to find out how he solved it.

…And that’s it for thislast month’s event! Head over to Rob’s blog right away to read up on this month’s edition–you have just over 24 hours from the time I hit Publish until your submissions are due. Get writing! And I’ll see you next time.