Press "Enter" to skip to content

Category: T-SQL

Working with INTERSECT and EXCEPT

Published 2024-03-28 by Kevin Feasel

Erik Darling wounds me:

I have never once seen anyone use these. The most glaring issue with them is that unlike a lot of other directives in SQL, these ones just don’t do a good job of telling you what they do, and their behavior is sort of weird.

Unlike EXISTS and NOT EXISTS, which state their case very plainly, as do UNION and UNION ALL, figuring these out is not the most straightforward thing. Especially since INTERSECT has operator precedence rules that many other directives do not.

I’ve used EXCEPT to check if two datasets are equivalent for testing purposes: A EXCEPT B should be zero rows, and B EXCEPT A should be zero rows. It has built-in handling of any NULL madness. Set intersections have their uses as well.

Comments closed

UNION and UNION ALL

Published 2024-03-27 by Kevin Feasel

Erik Darling hires the Pinkertons:

UNION and UNION ALL seem to get used with the same lack of discretion and testing as several other things in T-SQL: CTEs vs temp tables, temp tables vs. table variables, etc.

There are many times I’ve seen developers use UNION when result sets have no chance of being non-unique anyway, and many times I’ve seen them use UNION ALL when there would be a great benefit to discarding unnecessary duplicates.

Erik’s explanation goes about three steps beyond “UNION is bad so always use UNION ALL.” It’s a must-read for anybody who regularly writes T-SQL queries.

Comments closed

Parameterizing Dynamic SQL the Right Way

Published 2024-03-27 by Kevin Feasel

Andy Brownsword does things right, after a fashion:

When building dynamic SQL, safety is crucial. As we saw last week, we have the QUOTENAME function which can help when referencing object names.

Another aspect to consider is use of parameters. Integrating them incorrectly can leave us vulnerable to SQL injection attacks. Let’s take a look at how to handle them the wrong way, followed by the right way.

Why have the wrong way and then the right way? The answer is simple. It is a truth universally acknowledged, that a post with a Gallant must be in want of a Goofus.

Comments closed

Correlated Subqueries in SQL

Published 2024-03-27 by Kevin Feasel

Joseph Yeates classifies subqueries:

I’ve recently been brushing up on my SQL skills, as I’ve used the language for a while but less so recently. Through this process, I’ve found that I’m comfortable with the topics of complex joins, Common Table Expressions (CTEs), and nested subqueries. However, it was my deep dive into subqueries where I found something new: correlated subqueries. In this post, we’ll explore the intricacies of subqueries, with a spotlight on the often overlooked (at least for me) correlated subqueries.

Click through to understand what correlated subqueries are, in contrast to other forms of subquery.

Comments closed

Regex Support in Azure SQL DB

Published 2024-03-25 by Kevin Feasel

Abhiman Tiwari has a big announcement:

We are pleased to announce the private preview of regular expressions (regex) support in Azure SQL Database. Regex is a powerful tool that allows you to search, manipulate, and validate text data in flexible ways. With regex support, you can enhance your SQL queries with pattern matching, extraction, replacement, and more. You can also combine them with other SQL functions and operators to create complex expressions and logic.

This is something I’ve wanted to see in SQL Server for years, and I’m excited that there’s official support now. Prior to that, you could use SQL# to perform some regular expression operations using the CLR, but as long as performance is reasonable on these, it’s a huge feature to include.

Comments closed

Refreshing a View in SQL Server

Published 2024-03-25 by Kevin Feasel

Chad Callihan hits the reset button:

I recently encountered a question related to views: what happens when you make a change to the table that a view is based on? For example, if you change a column from VARCHAR(8) to VARCHAR(20), how does the view handle that change? You might expect the view to update, but it won’t do it on its own. You have to refresh the view.

Let’s look at a mocked up example.

Click through for that example. You’d think it could do so on its own, but nope. I suppose the reason you can’t is probably related to linked server and external object references in views, where that remote resource can change schema and SQL Server wouldn’t know about it. Perhaps also the idea that a person may be authorized to change a table in one database or schema but shouldn’t be authorized to modify (even to refresh?) a particular view that references the table.

Comments closed

Calculating Percentages in T-SQL

Published 2024-03-21 by Kevin Feasel

Edwin Sanchez shows a variety of methods to calculate percentages of the whole in T-SQL:

Calculating percentages in SQL Server is like slicing a pie. You need to know the total size (the denominator) and the size of the slice you want (the numerator). To get a percentage, you divide the slice size by the total size and multiply by 100. 

Read on for a variety of methods to calculate this. I wouldn’t use all of the methods myself, as I have certain predilections against subqueries in the SELECT clause, but they do get the job done.

Comments closed

The Proper Use of Views and Inline UDFs

Published 2024-03-20 by Kevin Feasel

Erik Darling plays tic-tac-toe:

The problem is really the stuff that people stick into views. They’re sort of like a junk drawer for data. Someone builds a view that returns a correct set of results, which becomes a source of truth. Then someone else comes along and uses that view in another view, because they know it returns the correct results, and so on and so on. Worse, views tend to do a bunch of data massaging, left joining and coalescing and substringing and replacing and case expressioning and converting things to other things. The bottom line is that views are as bad as you make them.

The end result is a trash monster with a query plan that can only be viewed in full from deep space.

Read on to learn the use cases for views and inline UDFs, as well as a few important notes regarding performance of each. Views are like mogwai: they’re fine as long as you never get them wet and never let them eat after midnight. The problem is, far too many companies are apparently the business equivalent of all-you-can-eat buffets at water parks.

Inline user-defined functions are like patenting a device that lets you shoot yourself in both feet with one pull of the trigger. Which, if I understand things correctly, means you’ll need a Form 4 for each inline UDF.

Comments closed