Category: T-SQL

Greg Low might have violated Betteridge’s Law of Headlines:

Using T-SQL it’s quite easy to build a table-valued function that can step through a string, character-by-character, and (based on a delimiter) output the delimited strings. The problem is that the performance of these functions is appalling.

When XML support appeared in SQL Server 2005, there were many attempts to create more efficient string-splitting functions. For many strings, these work quite well, but do have a few oddities that you need to cope with. Plus, most have limitations on the strings that you can split.

Ultimately, what was really needed was an efficient and native built-in function.  

Greg points out two mechanisms and contrasts them.

Louis Davidson points out a neat update:

Sometimes along comes a feature that seems so obvious, so natural, that you wonder why it took so long for Microsoft to implement it. One of those features in SQL Server 2005 is the optional length parameter in the SUBSTRING function. It has long been one of those questions when you wrote a SUBSTRING expression when you wanted to go from the Nth character to the end of the string, how many characters do you want? And for the most part, it didn’t really matter.

But sometimes it did (especially when dealing with nvarchar(max) data.

I learned about this when putting together an update to my Teaching Old Dogs New Tricks presentation. This capability is pretty nifty and something I wish I had a while ago.

Andy Brownsword can’t stop at one:

Having TOP (1) return multiple rows feels wrong… but that’s what WITH TIES can do.

For a long time I used patterns like this to get the first record in a group:

Andy goes on to explain how WITH TIES works in T-SQL, shows an alternative to using a common table expression + window function to narrow down to the first logical group, and digs into when you might not want to use that alternative.

Louis Davidson runs some tests:

A month and a half ago, I wrote a blog on using DATE_BUCKET. It is a cool feature thta makes doing some grouping really quite easy. It is here: Cool features in SQL Server I missed…DATE_BUCKET. One of the comments that came in was about performance of the DATE_BUCKET versus using things like DATEDIFF or a date table.

I started working on it then, but it got a bit involved (as performance comparison tests often do), so it took me a bit longer to get to than expected. But here it is, and the results are kind of what you would expect. The uses for DATE_BUCKET are really straightforward, and would rarely involve an index or a lot of filtering using the the function. But over a large number of rows, if it takes more time (even a millisecond more) than another method, you would notice it pretty quickly adding up.

Read on to see how DATE_BUCKET() performs compared to other methods of solving the same problem.