Aaron is a Staff Database Reliability Engineer at Stack Overflow. We don't want to say he's old, but his experience dates back to a time when SQL Server 6.5 was considered "modern." He writes here at sqlperformance.com, over at sqlblog.org, and at MSSQLTips.
The most common need for stripping time from a datetime value is to get all the rows that represent orders (or visits, or accidents) that occurred on a given day. However, not all of the techniques that are used to do so are efficient or even safe. If you want a safe range query that performs well, use an open-ended range or, for single-day queries on SQL Server 2008 and above, use CONVERT(DATE). Read on to discover why…
Medians require sorting. Sorting in SQL Server is typically a pretty expensive operation if there isn't a supporting index, and adding an index to support an operation which probably isn't requested that often may not be worthwhile. This post explores the performance characteristics of several solutions to the median problem.
Playing around today, I discovered that I couldn't connect to my local named instance using the dedicated administrator connection. This post shows why and explains how you can avoid getting locked out of your own instance.
People wonder whether they should do their best to prevent exceptions, or just let the system handle them. I've seen several discussions where folks debate whether they should do whatever they can to prevent an exception, because error handling is "expensive." There is no doubt that error handling isn't free, but I would predict that a constraint violation is at least as efficient as checking for a potential violation first. This may be different for a key violation than a static constraint violation, for example, but in this post I'm going to focus on the former.
A common scenario in many client-server applications is allowing the end user to dictate the sort order of results. Some people want to see the lowest priced items first, some want to see the newest items first, and some want to see them alphabetically. This is a complex thing to achieve in Transact-SQL because you can't just throw @SortColumn and @SortDirection into a straight T-SQL query.
The "Don't Repeat Yourself" principle suggests that you should reduce repetition. This week I came across a case where DRY should be thrown out the window. There are other cases as well (for example, scalar functions), but this one was an interesting one involving Bitwise logic.
I see a lot of advice out there that says something along the lines of, "Change your cursor to a set-based operation; that will make it faster." While that can often be the case, it's not always true. One use case I see where a cursor repeatedly outperforms the typical set-based approach is the calculation of running totals. This is because the set-based approach usually has to look at some portion of the underlying data more than one time, which can be an exponentially bad thing as the data gets larger; whereas a cursor – as painful as it might sound – can step through each row/value exactly once.