Browse by Tags

• Architecture
• bitmasks
• developer tools
• Hierarchies
• logic
• Optimization
• Performance
• Query Tuning
• Scripts
• splitting strings
• Testing

• 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 | 13 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 | 5 Comments Filed under: Performance, Query Tuning, T-SQL

• Stored procedures are not parameterized views

  Originally posted here . 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 Read More... Posted Wednesday, July 12, 2006 10:54 PM by Adam Machanic | 2 Comments Filed under: T-SQL, Architecture

• T-SQL Variables: Multiple Value Assignment

  Originally posted here . 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 Read More... Posted Wednesday, July 12, 2006 10:53 PM by Adam Machanic | 0 Comments Filed under: T-SQL

• Running sums, redux

  Originally posted here . 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... Read More... Posted Wednesday, July 12, 2006 10:51 PM by Adam Machanic | 7 Comments Filed under: Performance, T-SQL

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

  Originally posted here . 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 Read More... Posted Wednesday, July 12, 2006 10:49 PM by Adam Machanic | 0 Comments Filed under: T-SQL, Hierarchies

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

  Originally posted here . 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 Read More... Posted Wednesday, July 12, 2006 10:47 PM by Adam Machanic | 1 Comments Filed under: T-SQL, Hierarchies

• Texas Hold 'em Hint #1

  Originally posted here . 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 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

  Originally posted here . 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. Read More... Posted Wednesday, July 12, 2006 10:45 PM by Adam Machanic | 0 Comments Filed under: T-SQL, Scripts

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

  Originally posted here . 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 Read More... Posted Wednesday, July 12, 2006 10:44 PM by Adam Machanic | 1 Comments Filed under: T-SQL

• Introducing: TSQLMacro

  Originally posted here . I am pleased to announce the official introduction of the TSQLMacro framework , version 0.5 (prebeta-1). This version is functionally complete based on the initial design spec , features are stable, and I am not currently aware Read More... Posted Wednesday, July 12, 2006 10:37 PM by Adam Machanic | 0 Comments Filed under: T-SQL, Testing, developer tools

• Introducing: TSQLAssert

  Originally posted here . Yes, yet another introduction (although there was almost zero reaction to yesterday's introduction ). Today, I introduce the first thing built on top of TSQLMacro . I proudly present the TSQLAssert debug assertion framework ! Read More... Posted Wednesday, July 12, 2006 10:37 PM by Adam Machanic | 0 Comments Filed under: T-SQL, Testing, developer tools

• Tokenize UDF

  Originally posted here . Yes, another string splitting UDF from a guy who's obvioiusly become obsessed with TSQL string splitting. This time we delve into a mysterious world that I call, "Tokenization." So what is Tokenization? It's a word I made up for Read More... Posted Wednesday, July 12, 2006 10:34 PM by Adam Machanic | 0 Comments Filed under: T-SQL, Scripts

• "Reflect" a TSQL routine

  Originally posted here . Ever want to see the text of a stored procedure, function, or trigger -- or manipulate the text in some way? sp_helptext works, sort of. But I really don't like the way it handles large procedures (> 4000 characters). They Read More... Posted Wednesday, July 12, 2006 10:33 PM by Adam Machanic | 0 Comments Filed under: T-SQL, Scripts