Browse by Tags

• Architecture
• logic
• Optimization
• Query Tuning

• 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 | 8 Comments Filed under: Performance, Query Tuning, 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

• Controlling Stored Procedure Caching with ... Dyanmic SQL?!?

  Originally posted here . Tell me if this situation sends a chill down your spine: You've written a stored procedure, tested it against a variety of inputs, and finally rolled it out in production. All is well... Or so you think. You start getting complaints Read More... Posted Wednesday, July 12, 2006 10:19 PM by Adam Machanic | 0 Comments Filed under: Performance, T-SQL, Optimization

• Performance: ISNULL vs. COALESCE

  Originally posted here . Mladen aka spirit1 posted a speed test of COALESCE vs. ISNULL. Reported result: COALESCE is faster. But leave it to Anatoly Lubarsky to argue with what was posted. He posted his own speed test, showing that ISNULL is faster. Anatoly's Read More... Posted Wednesday, July 12, 2006 10:16 PM by Adam Machanic | 2 Comments Filed under: Performance, T-SQL

• Rowset string concatenation: Which method is best?

  Originally posted here . Yeah, yeah, yeah, let's get this out of the way right from the start: Don't concatenate rows into delimited strings in SQL Server. Do it client side. Except if you really have to create delimited strings in SQL Server. In which Read More... Posted Wednesday, July 12, 2006 10:15 PM by Adam Machanic | 6 Comments Filed under: Performance, T-SQL, Architecture

• Is PATINDEX faster than LIKE?

  Originally posted here . I keep seeing the same suggestion on various "tips and tricks" websites: For situations in which you might want to use LIKE in the WHERE clause, but for which indexes cannot be used, PATINDEX will perform faster. So, according Read More... Posted Wednesday, July 12, 2006 10:13 PM by Adam Machanic | 1 Comments Filed under: Performance, T-SQL

• Paging in SQL Server 2005

  Originally posted here . I keep seeing questions on newsgroups about paging in stored procedures, and whether there will be a better way in SQL Server 2005. However, aside from a few answers in newsgroups, I haven't seen any content on how to do it. So Read More... Posted Wednesday, July 12, 2006 10:12 PM by Adam Machanic | 0 Comments Filed under: Performance, Query Tuning, T-SQL