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.
As lots of you know already, Kevin Kline (blog|@KEKline) has joined us here at SQL Sentry. I’ve followed Kevin myself for a good while, so I’m really excited to have him here with us.
One of the first things Kevin asked of me was to list the top 5 technical issues we see while providing customer service for SQL Sentry. For our Client Services team, technical support is far more than just supporting our own software products or managing licenses. Everyone on the team is deeply trained in SQL Server, Windows and Microsoft .NET. In fact, in general, any member trained on our Client Services team should be able to walk into the role of a Windows sysadmin, SQL Server DBA or .NET Application Developer with very little adjustment effort.
Some interesting discussions always evolve around the topic of splitting strings. In two previous blog posts, "Split strings the right way – or the next best way" and "Splitting Strings : A Follow-Up," I hope I have demonstrated that chasing the "best-performing" T-SQL split function is fruitless. When splitting is actually necessary, CLR always wins, and the next best option can vary depending on the actual task at hand. But in those posts I hinted that splitting on the database side may not be necessary in the first place.
This month's T-SQL Tuesday is being hosted by Mike Fal (blog | twitter), and the topic is Trick Shots, where we're invited to tell the community about some solution we used in SQL Server that felt, at least to us, as a sort of "trick shot" – something similar to using massé, "English" or complicated bank shots in billiards or snooker. After working with SQL Server for some 15 years, I've had the occasion to come up with tricks to solve some pretty interesting problems, but one that seems to be quite reusable, easily adapts to many situations, and is simple to implement, is something I call "schema switch-a-roo."
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.
There were a lot of comments following my post last week about string splitting. I think the point of the article was not as obvious as it could have been: that spending a lot of time and effort trying to "perfect" an inherently slow splitting function based on T-SQL would not be beneficial. I have since collected the most recent version of Jeff Moden's string splitting function, and put it up against the others:
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.