Press "Enter" to skip to content

Category: T-SQL

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

Enumerating Local Admins on a Computer with Powershell

Published 2021-10-15 by Kevin Feasel

Jess Pomfret has a Powershell snippet for us:

This morning I was working on pulling together some information which included whether certain accounts were in the local administrator’s group on some remote servers. I had the perfect snippet saved in my code repo so I was quickly able to answer that question – and then I realised I should share that with you all.

Click through for the script.

Comments closed

Fundamentals of Inline TVFs

Published 2021-10-15 by Kevin Feasel

Itzik Ben-Gan explains Inline Table-Valued Functions:

Compared to the previously covered named table expressions, iTVFs resemble mostly views. Like views, iTVFs are created as a permanent object in the database, and therefore are reusable by users who have permissions to interact with them. The main advantage iTVFs have compared to views is the fact that they support input parameters. So, the easiest way to describe an iTVF is as a parameterized view, although technically you create it with a CREATE FUNCTION statement and not with a CREATE VIEW statement.

It’s important not to confuse iTVFs with multi-statement table-valued functions (MSTVFs). The former is an inlinable named table expression based on a single query similar to a view and is the focus of this article. The latter is a programmatic module that returns a table variable as its output, with multi-statement flow in its body whose purpose is to fill the returned table variable with data.

Now that we have that sorted, click through to see examples and dive into performance ramifications.

Comments closed

Things You Can Do with Random Values

Published 2021-10-14 by Kevin Feasel

Andy Yun shows off some random skills:

First, there are times where you need multiple random numbers. Simply using multiple iterations of RAND() in a single statement won’t cut it, as you need to vary the seed. So I keep this snippet handy for when I need a bunch of random values in a single statement:

Click through for that as well as two more uses of RANDOM(). This is my reminder that RANDOM() generates data across a uniform distribution (every value in the range is equally likely to be chosen), making it great for these sorts of experiments but can look weird by itself if you’d expect non-uniform distributions of the data. For that, you would need some distributional trickery—though frankly, between the uniform and normal distributions, you’ve probably covered about 95-99% of test dataset needs.

Comments closed