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 years. Aaron's post includes many...
Bad Habits to Kick: Inconsistent Indentation
My last post in Aaron's series drew a mixed review from some readers, and I'm sure this one will do the same. But that's part of the fun! One of the biggest threats to maintainability is code that's not properly formatted. When I'm called in by a customer to debug some legacy code, often...
Bad Habits to Kick: Not Using "AS"
Aaron has recently been doing an absolutely fantastic series of posts detailing various "bad habits" that many of us pick up somewhere along the way. These coding anti-patterns aren't going to crash your server, but they will make your code more difficult to read and maintain. I'm enjoying Aaron's series so much that I've...
Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE
It would be fair to say that there aren't a huge number of programmability features added to SQL Server 2008 that will have a great impact on most of our day-to-day lives as SQL Server developers. The release was clearly manageability themed rather than programmability themed, and I can count the most interesting and...
Uniquely Identifying XML Nodes with DENSE_RANK
When working with XML in SQL Server, you might want to uniquely identify one node against another. But due to the flexibility with which XML can be defined, this is not always directly possible. SQL Server's own XML structures are guilty of having this problem, as I discovered while writing the Extended Events Code...
Introducing: Extended Events Code Generator v1.0
SQL Server 2008 Extended Events. It's a high-performance, feature-rich, and extremely useful tracing system. Too bad it's so difficult to figure out how to use it! Or is it?Earlier this week Jonathan Kehayias announced his SSMS addin for Extended Events, which makes it super-easy to create and manage events, and even view the raw...
The Hidden Costs of INSERT EXEC
INSERT and EXEC: relational brothers in arms, helping you get your data and then put it somewhere. But like all brothers, their relationship has its ups and downs and sometimes you need to look just below the surface to see the real issues. In this post I will cover hidden penalties that a decision--seemingly...
SQLCLR String Splitting Part 2: Even Faster, Even More Scalable
Two days ago, after posting what I thought was a pretty solid SQLCLR string splitting method, I received a comment telling me about a big thread on SQLServerCentral dedicated to the question of how best to split strings. So I jumped in, and went back and forth, and back and forth, and back... and...
Faster, More Scalable SQLCLR String Splitting
It seems like every couple of months we see yet another post on SQLCLR string splitting routines. Many bloggers, I suppose, are still struggling, even three years later, to find that "perfect" use case for SQLCLR. Is string splitting it? Probably not. And with SQL Server 2008 table-valued parameters now available, SQLCLR string splitting...
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, rightmost and leftmost, or best and worst happens...