Browse by Tags

• Architecture
• bitmasks
• challenge
• concatenation
• contest
• date
• datetime
• developer tools
• grouping
• Hierarchies
• logic
• Optimization
• Performance
• puzzle
• Query Tuning
• Scripts
• splitting strings
• Testing

• What Happened Today? DATE and Date Ranges Over DATETIME

  A few days ago Aaron posted yet another fantastic entry in his Bad Habits series, this one discussing mishandling of date ranges in queries . This is a topic near and dear to me, having had to clean up a lot of poorly thought out code in the past few Read More... Posted Tuesday, October 20, 2009 3:21 PM by Adam Machanic | 18 Comments Filed under: Performance, T-SQL, Optimization, datetime, date

• Grouped String Concatenation: ... The Winner Is ...

  After weeks of putting it off, I finally found the time and spent the last day and a half judging the Grouped String Concatenation Challenge . I would like to congratulate the winner, Peter Larsson , who submitted a great query and walks away with a shiny Read More... Posted Sunday, May 31, 2009 5:07 PM by Adam Machanic | 20 Comments Filed under: T-SQL, challenge, concatenation Attachment(s): GroupedString.zip

• The Grouped String Concatenation Challenge is Closed

  Just over two weeks ago I posted the Grouped String Concatenation Challenge . A more difficult challenge than the last one I posted, partially in hopes that not as many people would submit solutions and it would be easier for me to judge. But alas, my Read More... Posted Sunday, March 15, 2009 9:22 PM by Adam Machanic | 36 Comments Filed under: T-SQL, challenge, concatenation

• T-SQL Challenge: Grouped String Concatenation

  It's been quite a while since the LIKE vs ? Puzzle , and I feel like it's time for another one. Response was overwhelming last time, and I'm back with a much tougher puzzle and a much bigger prize. So get ready, because I'm going to really make you stretch Read More... Posted Friday, February 27, 2009 2:22 PM by Adam Machanic | 47 Comments Filed under: T-SQL, puzzle, contest, challenge, grouping, concatenation Attachment(s): grouped_string_concatenation_output.zip

• Who's On First? Solving the Top per Group Problem (Part 1: Technique)

  Relative comparison is a simple matter of human nature. From early childhood we compare and contrast what we see in the world around us, building a means by which to rate what we experience. And as it turns out, this desire to discover top and bottom, Read More... Posted Friday, February 08, 2008 7:09 PM by Adam Machanic | 14 Comments Filed under: Performance, Query Tuning, T-SQL, logic

• Medians, ROW_NUMBERs, and performance

  A couple of days ago, Aaron Bertrand posted about a method for calculating medians in SQL Server 2005 using the ROW_NUMBER function in conjunction with the COUNT aggregate. This method (credited to Itzik Ben-Gan) is interesting, but I discovered an even Read More... Posted Monday, December 18, 2006 3:52 PM by Adam Machanic | 3 Comments Filed under: Performance, Query Tuning, T-SQL

• Scalar functions, inlining, and performance: An entertaining title for a boring post

  Scalar. Function. Wow. Could any other combination of words evoke the same feeling of encapsulation, information hiding, and simplification of client code? After years spent developing software in the procedural and OO worlds, it can be difficult--perhaps, Read More... Posted Friday, August 04, 2006 12:07 AM by Adam Machanic | 12 Comments Filed under: Performance, Query Tuning, T-SQL

• Stored procedures are not parameterized views

  Peter van Ooijen over at CodeBetter.com posted in his blog about some observations he had when working with stored procedures in a recent project . What I found to be interesting about his post was his comment that a stored procedure can be, "a view with Read More... Posted Wednesday, July 12, 2006 10:54 PM by Adam Machanic | 6 Comments Filed under: T-SQL, Architecture

• T-SQL Variables: Multiple Value Assignment

  Tony Rogerson brings us an interesting blog post about T-SQL variable assignment and SET vs. SELECT . The issue? With SELECT you can assign values to multiple variables simultaneously. But with SET, you can set up your assignment such that you get an Read More... Posted Wednesday, July 12, 2006 10:53 PM by Adam Machanic | 4 Comments Filed under: T-SQL

• Running sums, redux

  Siddhartha Gautama, the Buddha, taught us to understand that the key to enlightenment is following the Middle Path. And today I learned a valuable lesson in extremes. You can file this one in the "Doh! Wrong again!" category... A fairly common question Read More... Posted Wednesday, July 12, 2006 10:51 PM by Adam Machanic | 11 Comments Filed under: Performance, T-SQL

• Swinging From Tree to Tree Using CTEs, Part 2: Adjacency to Nested Intervals

  In our previous installment , we saw how to convert Adjacency Lists into Nested Sets using a CTE. In this episode, we will convert the Adjacency List into a Nested Intervals encoding. Specifically, this encoding will make use of the Nested Intervals with Read More... Posted Wednesday, July 12, 2006 10:49 PM by Adam Machanic | 1 Comments Filed under: T-SQL, Hierarchies

• Swinging From Tree to Tree Using CTEs, Part 1: Adjacency to Nested Sets

  I'm not sure how many times over the last several years I've seen the same tired article titles... "Climbing Trees in SQL," "Climbing Up the SQL Tree," or maybe, "Naked Coeds Playing in the Trees!" ... Oh wait, I think that last one might be something Read More... Posted Wednesday, July 12, 2006 10:47 PM by Adam Machanic | 15 Comments Filed under: T-SQL, Hierarchies

• Texas Hold 'em Hint #1

  The other day I annouced the Texas Hold 'em SQL Challenge. I haven't gotten any feedback on it yet, so I have no idea if anyone is working on it, but I thought I'd get the ball rolling and come up with my own solution... The first step I've decided to Read More... Posted Wednesday, July 12, 2006 10:46 PM by Adam Machanic | 0 Comments Filed under: T-SQL

• Looping over routines using sp_foreachroutine

  Of all of the undocumented stored procedures shipped with SQL Server, there are two in particular that I constantly use: sp_MSforeachtable and sp_MSforeachdb . These procedures internally loop over each non-Microsoft shipped (i.e. user-defined) table Read More... Posted Wednesday, July 12, 2006 10:45 PM by Adam Machanic | 3 Comments Filed under: T-SQL, Scripts

• SQL Server 2005 T-SQL: Aggregates and the OVER clause

  A new feature added to SQL Server 2005 for the sake of the windowing functions is the OVER clause. Using this clause, you can specify ordering or partitioning for the windowing functions. For instance, to enumerate the names of all of the products in Read More... Posted Wednesday, July 12, 2006 10:44 PM by Adam Machanic | 2 Comments Filed under: T-SQL