Press "Enter" to skip to content

Category: T-SQL

Thoughts on the New STRING_SPLIT

Published 2021-11-09 by Kevin Feasel

Ronen Ariely has mixed feelings on updates to the STRING_SPLIT function:

The main issue with this function, is that it returns a SET of rows with no specific order.

As you must know by now, a TABLE is a SET of rows (Rowstore table which is the more common in SQL Server) or columns (Columnstore table). The rows in the table are not stored in specific order (even if using clustered index, the rows can physically be stored in different locations on the disk, not necessarily maintained continuously one after the other. In addition, the server might read the rows in parallel and not necessarily in the order of the index. As a result, The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.

And this is the main issue with the STRING_SPLIT… until today

Read on to see how this update makes STRING_SPLIT() much better, and also how it could be even better still.

Comments closed

Wildcards and Data Type Precedence

Published 2021-11-04 by Kevin Feasel

Daniel Hutmacher has fun with implicit conversion:

Implicit conversions in SQL Server follow a specific, predictable order, called data type precedence. This means that if you compare or add/concatenate two values, a and b, with different data types, you can predict which one will be implicitly converted to the data type of the other one in order to be able to complete the operation.

I stumbled on an interesting exception to this rule the other day.

Click through for an example of date searching with LIKE.

Comments closed

Restrictions for Parallel Insertion

Published 2021-11-02 by Kevin Feasel

Erik Darling summarizes the main man:

I’d like to start this post off by thanking my co-blogger Joe Obbish for being lazy and not blogging about this when he first ran into it three years ago.

Now that we’re through with pleasantries, let’s talk turkey.

Over in this post, by Arvind Shyamsundar, which I’m sure Microsoft doesn’t consider official documentation since it lacks a GUID in the URL, there’s a list of… things about parallel inserts.

Read on for the summary of limitations.

Comments closed

More Efficient Pivoting

Published 2021-10-28 by Kevin Feasel

Dave Mason is on the hunt:

While working with some poorly performing code in T-SQL that used a PIVOT operator, I wondered if there was a more efficient way to get a result set of pivoted data. It may have been a fool’s errand, but I still wanted to try. It dawned on me that I could use the STRING_AGG() function to build a delimited list of pivoted column names and values. From there, I’d have to “shred” the delimited data to rows and columns. But how?

Read on to see how.

Comments closed

Comparing OPENJSON vs Other List-Parsing Methods in SQL Server

Published 2021-10-25 by Kevin Feasel

Aaron Bertrand splits a string:

I have long advocated avoiding splitting strings by using table-valued parameters (TVPs), but this is not always a valid option; for example, PHP drivers do not handle this feature yet. A new pattern I’ve seen emerging is to replace splitting comma-separated strings with the new OPENJSON functionality introduced in SQL Server 2016. I wanted to explore why this is not an improvement in the typical case, unless you are using a client application platform that doesn’t support TVPs and your application data already happens to be in JSON format.

Read on for the solution, which has a somewhat surprising outcome.

Comments closed

Grouping on a Substring

Published 2021-10-25 by Kevin Feasel

Kevin Hill tries something out:

I was chatting with Jeff (b|t) on my team yesterday and the context escapes me but I had this thought:

“Can you Group By the beginning characters, or a subset, of a field?”

I’m not a developer, so this question never comes my way. Except yesterday.

Click through for the answer. Mind you, this is unlikely to perform well, but if you’re looking at a small enough dataset or need a one-time query and can afford a full scan of the data, so be it.

Comments closed

On Avoiding NULL

Published 2021-10-21 by Kevin Feasel

Aaron Bertrand defends NULL:

A long time ago, I answered a question about NULL on Stack Exchange entitled, “Why shouldn’t we allow NULLs?” I have my share of pet peeves and passions, and the fear of NULLs is pretty high up on my list. A colleague recently said to me, after expressing a preference to force an empty string instead of allowing NULL:

“I don’t like dealing with nulls in code.”

I’m sorry, but that’s not a good reason. How the presentation layer deals with empty strings or NULLs shouldn’t be the driver for your table design and data model. And if you’re allowing a “lack of value” in some column, does it matter to you from a logical standpoint whether the “lack of value” is represented by a zero-length string or a NULL? Or worse, a token value like 0 or -1 for integers, or 1900-01-01 for dates?

Definitely read what Aaron has to say. I disagree with the tenor of his point enough that, now that I’m actually blogging again, I’ll have a post up tomorrow laying out the core of my disagreement. Stay tuned!

2 Comments

T-SQL Tuesday 143 Round-Up

Published 2021-10-20 by Kevin Feasel

John McCormack summarizes T-SQL Tuesday #143:

What an honour it was to host T-SQL Tuesday this month and I received some really great submissions. This wrap up post aims to give a quick insight into each of them in the hope that more members of the SQL Family can find some time to click on them and learn more. I counted 22 posts including my own which was a great response. If you missed the original invite, you can find the link below.

Click through for all of the responses.

Comments closed