Press "Enter" to skip to content

Category: T-SQL

Overwhelming the IN Operator

Published 2023-04-04 by Kevin Feasel

Tariq Rasheed Al-Qaralleh recounts a customer problem:

In some cases, like when customers use Object-relational Mapping tools (ORM) Like Entity framework or LINQ, part of the code at the end will be converted to a TSQL executable statement.

For example, The LINQ with  .Where () method will be a TSQL Query with a Where clause :

Query Syntax and Method Syntax in LINQ (C#) | Microsoft Learn

Write LINQ queries in C# | Microsoft Learn

Read on for some of the practical consequences of doing this, including performance issues and possibly even runtime errors.

Tariq gives a couple examples of how to fix the issue, and a third possible fix is to pass in the IN clause as a table-valued parameter and join to that TVP.

Comments closed

Fending off Sessions while in Single-User Mode

Published 2023-04-03 by Kevin Feasel

Eitan Blumin just wants to switch the database type:

Today we had an interesting use case where a customer reported that one of the databases they just restored from a backup got stuck in “Single-User” mode in one of their environments.

To resolve it, I first tried running the following command:

ALTER DATABASE MyDB SET MULTI_USER WITH ROLLBACK IMMEDIATE;

In response, I got deadlocked with the dreaded error 1205:

There were a few different attempts with no success until Eitan came up with the final script. Eitan’s analogy was to curling, though the first thing I thought of was Odysseus fighting off his wife’s suitors as he came back to claim his home.

1 Comment

ANSI SQL and Trailing Spaces

Published 2023-04-03 by Kevin Feasel

Chris Johnson finds a language quirk:

Recently I found a quirk of T-SQL, where a group by statement was treating strings as the same if the only difference was one or more trailing spaces. So, ‘aa’ would be grouped with ‘aa ‘. I did some digging, and this is what I found.

Yeah, this isn’t just Microsoft’s T-SQL variant—it’s a standard part of SQL, as Chris notes later in the post.

My “just-so” story is that this might have been implemented to deal with CHAR(x) comparisons, such as CHAR(2) to CHAR(3). There’s no way to make that comparison unless you treat trailing spaces as irrelevant. Because we almost always use VARCHAR(x) or NVARCHAR(x), it isn’t something top of mind to most database practitioners, but there is a method to the madness.

Comments closed

Rewriting Scalar UDFs and NULL Results

Published 2023-03-29 by Kevin Feasel

Erik Darling jumps out of the time machine to warn us, but we have no idea what he’s talking about so we ignore the warning and end up doing the thing he warned us not to do, causing us to need to send him into a time machine to warn us not to do it:

I think I have probably spent 500 hours of my life rewriting T-SQL Scalar UDFs to avoid all the performance problems associated with them.

The obvious choice is the Inline Table Valued Function, which has fewer performance issues baked in. For the kids out there: they don’t spill trauma.

But getting the rewrite right can be tricky, especially when it’s possible for the function to return NULL values.

Click through for the example.

Comments closed

Using a Trigger to Auto-Refresh View Metadata

Published 2023-03-23 by Kevin Feasel

Aaron Bertrand keeps metadata in sync:

As much as we tell people to use SCHEMABINDING and avoid SELECT *, there is still a wide range of reasons people do not. A well-documented problem with SELECT * in views, specifically, is that the system caches the metadata about the view from the time the view was created, not when the view is queried. If the underlying table later changes, the view doesn’t reflect the updated schema without refreshing, altering, or recreating the view. Wouldn’t it be great if you could stop worrying about that scenario and have the system automatically keep the metadata in sync?

It’s almost entirely not apropos, but the first thing I thought of when I read the title and Problem statement was Goethe’s line about Mephistopheles: “Oft evil will shall evil mar.” Make of that what you will.

Comments closed

Checking XML Validity

Published 2023-03-23 by Kevin Feasel

Kevin Wilkie doesn’t like misshapen XML data:

Sometimes you’ll find that you will have XML in your database. This could be for various reasons – from storing the XML after receiving an API response to keeping it in a table because a web developer couldn’t figure out another way to store their data. Sometimes – no matter how much you trust your source – you should question if the XML is well-formed. Let’s work out a few ways you can do that in a database.

Read on for a few tests. The more concerned you are about XML data quality, the more you’d want to push in the direction of having an XSLT defined as well.

Comments closed

Analyzing SQL Server Table Metadata

Published 2023-03-23 by Kevin Feasel

Barney Lawrence looks at details about a table:

For a while I’ve been building up a script that uses SQL Servers INFORMACTION_SCHEMA.COLUMNS table to create a query that applies aggregates to each column in a table and then formats the results to allow an at a glance summary of the shape of the data inside of it.

I showed some of the techniques used to build this in a session regarding metadata and data warehousing which I co-presented with Emma Dolling and Ruth Pearson at SQLBits. The one comment that I heard the most after this session was “I want that script”. I did promise to make it available more than a few times and so I’ve put together a github repository to hold it and other resources we built for the session.

Barney anticipated the most likely change I’d make while reviewing the script: APPROX_COUNT_DISTINCT() if you’re on SQL Server 2022 and dealing with a large table.

Comments closed

Mnemonics for Remembering SQL Clause Order

Published 2023-03-22 by Kevin Feasel

Bob Pusateri keeps it all straight in his head:

Ooh! A mnemonic! And a pretty good one at that. The idea being that the first letter of each word of the sentence helps you remember something else, like the order of the major parts of a SELECT statement:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

Click through for Bob’s best attempts. Oddly enough, now I want some Rally’s fries. Or maybe Checkers—I can’t decide.

Comments closed

Building Your Own TRY_PARSE Function

Published 2023-03-10 by Kevin Feasel

Aaron Bertrand gives it a go:

A while back, I wrote a couple of tips about simulating TRY_CONVERT – a function added in SQL Server 2012 – in earlier versions (see Part 1 and Part 2). Recently, someone in the community had a similar requirement: they wanted to make TRY_PARSE work for dates in SQL Server 2008. Now, I’m not one to help people stay on versions of SQL Server that are now 15 years old, but I do acknowledge that hands may be tied, and they can’t always control the version they’re stuck with.

Aaron does this community member a solid, though it’s probably something you’d never want to use if you’re on SQL Server 2012 or later.

Comments closed

Handling Optional T-SQL Code via SQLCMD

Published 2023-03-07 by Kevin Feasel

Louis Davidson shows off a bit of SQL Server Management Studio’s SQLCMD mode:

In the creating a database script, there are quite a few parameters you will want as part of your script. The database name, the settings, whether or not to drop the database or not. Built into SQL Server Management Studio is a cool tool called SQLCMD mode. Scripts using this mode get a few scripting tools that are really useful. These tools allow you to do things like insert other script files, set environment variables, and one I particularly like, make sure you don’t accidentally execute a script if no code is highlighted using EXIT to start your script ().

Where it is really lacking is in the area of control of flow language. In this blog I am going to share a few techniques I have used to get around this when building scripts that need to optionally execute different bits of code.

Click through to see how you can use it. Going one step further, you can directly script against sqlcmd.exe, which can be useful for automating deployments.

Comments closed