Browse by Tags

• Architecture
• date
• datetime
• logic
• Optimization
• Query Tuning

• 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 | 14 Comments Filed under: Performance, T-SQL, Optimization, datetime, date

• 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 | 18 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 | 6 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 | 28 Comments Filed under: Performance, Query Tuning, 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 | 9 Comments Filed under: Performance, T-SQL

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

  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 from some users that Read More... Posted Wednesday, July 12, 2006 10:19 PM by Adam Machanic | 3 Comments Filed under: Performance, T-SQL, Optimization

• Performance: ISNULL vs. COALESCE

  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 results showed a Read More... Posted Wednesday, July 12, 2006 10:16 PM by Adam Machanic | 17 Comments Filed under: Performance, T-SQL

• Rowset string concatenation: Which method is best?

  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 case you should read on. Read More... Posted Wednesday, July 12, 2006 10:15 PM by Adam Machanic | 25 Comments Filed under: Performance, T-SQL, Architecture

• Is PATINDEX faster than LIKE?

  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 to these sources , this: Read More... Posted Wednesday, July 12, 2006 10:13 PM by Adam Machanic | 5 Comments Filed under: Performance, T-SQL

• Paging in SQL Server 2005

  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 I'd like to spend a few Read More... Posted Wednesday, July 12, 2006 10:12 PM by Adam Machanic | 3 Comments Filed under: Performance, Query Tuning, T-SQL