Press "Enter" to skip to content

Category: T-SQL

IF Branches and Dynamic SQL

Published 2022-05-30 by Kevin Feasel

Erik Darling takes us through the scenic route:

I’m going to use the example from yesterday’s post to show you what you can do to further optimize queries like this.

To make the code fit in the post a little better, I’m going to skip the IF branch for the Posts table and go straight to Votes. Using dynamic SQL here will get you the same behavior at stored procedures, though.

Read on for more detail and a wrap-up of Erik’s series on conditional branching logic and performance tuning.

Comments closed

IF Branching, Local Variables, and Stored Procedures

Published 2022-05-27 by Kevin Feasel

Erik Darling continues a quest. Part 3 involves local variables:

What never seems to get a bad name, despite numerical supremacy in producing terrible results, are local variables.

In this particular scenario, I see developers use them to try to beat “parameter sniffing” to no avail.

A chorus of “it seemed to work at the time”, “I think it made things a little better”, “it worked on my machine”, and all that will ensue.

But we know the truth.

The next part is around stored procedures:

You know and I know and everyone knows that stored procedures are wonderful things that let you tune queries in magickal ways that stupid ORMs and ad hoc queries don’t really allow for.

Sorry about your incessant need to use lesser ways to manifest queries. They just don’t stack up.

But since we’re going to go high brow together, we need to learn how to make sure we don’t do anything to tarnish the image of our beloved stored procedures.

Erik notes that stored procedures are part of the solution but there’s a bit more that we need.

Comments closed

Window Function Improvements in SQL Server 2022

Published 2022-05-26 by Kevin Feasel

Itzik Ben-Gan knows how to inspire joy:

Microsoft recently released the first public preview of SQL Server 2022. This release has a number of T-SQL improvements. In this article I focus on windowing and NULL-related improvements. These include the new WINDOW clause and the windowing NULL treatment clause.

I’ll be using the sample database TSQLV6 in the examples in this article. You can download this sample database here.

I’ve been waiting for INGORE NULLS since they previewed it in Azure SQL Edge.

Comments closed

T-SQL Enhancements in SQL Server 2022

Published 2022-05-26 by Kevin Feasel

Aaron Bertrand notes some T-SQL improvements:

A few of the most useful changes I’ve been able to play with in SQL Server 2022 so far:

– GREATEST / LEAST

– STRING_SPLIT

– DATE_BUCKET

– GENERATE_SERIES

In this tip, I’ll explain each one, and show some practical use cases.

Click through for more information. I particularly like GREATEST() and LEAST() but GENERATE_SERIES() could be very useful as well. Some of this stuff was first made available in Azure SQL Edge.

Comments closed

Running Dynamic SQL in a Different Database

Published 2022-05-20 by Kevin Feasel

Kenneth Fisher doesn’t like this database and wants to move to a new one:

This is one of those things that on hindsight was a stupid problem, but still cost me hours and a major headache. So I thought I would share the story of my headache with you. 

A few weeks ago I was working on some dynamic SQL that hit multiple databases. Not a huge issue. I do lots of dynamic SQL.

But it didn’t quite work out the way Kenneth initially envisioned. Read on for the full story.

Comments closed

T-SQL Order of Execution and Aliases

Published 2022-05-19 by Kevin Feasel

Joe Billingham explains why you can’t do that thing you want to do:

So, you have just written a query, hit execute and you have encountered an error: Invalid column name ‘[column name]‘.

The column you’ve used in your WHERE clause cannot be identified by its alias. You’ve defined it at the top of the query and used it fine previously as your ORDER BY condition, so why can’t the engine recognise it?

Read on for the answer. This is why some people I know have wanted a SQL-like language which runs in order of execution, so a query would start with the FROM clause rather than the SELECT clause. Languages like KQL do work that day, so there are examples in the wild.

Comments closed

Forced Parameterization and Local Variables

Published 2022-05-05 by Kevin Feasel

Erik Darling tries something out:

I think it was sometime in the last century that I mentioned I often recommend folks turn on Forced Parameterization in order to deal with poorly formed application queries that send literal rather than parameterized values to SQL Server.

And then just like a magickal that, I recommended it to someone who also has a lot of problems with Local Variables in their stored procedures.

They were curious about if Forced Parameterization would fix that, and the answer is no.

Shot down by the third paragraph of the intro. That’s rough. Still, click through for the demo.

Comments closed

Multiple Aggregations with CASE

Published 2022-05-03 by Kevin Feasel

Chad Callihan shows off a good use of aggregate functions and the CASE statement:

Have you have been tasked with pulling multiple counts from the same table? Maybe you need to find how many records have a value for a column and how many are NULL. Or maybe you need to see how many records are true and how many are false.

It’s simple enough to run a query to count one set of criteria, run another query for the second set of criteria, and combine them when sending your results. Did you know you can get multiple counts with one query?

Here’s an example of how using COUNT and CASE can speed up your day.

Click through for an example. I mildly disagree with Chad’s conclusion that this is something you’ll rarely do—the more you work with reporting and analytical queries, the more you’ll appreciate this.

Comments closed

Simplifying T-SQL Code

Published 2022-04-28 by Kevin Feasel

Ed Pollack shares several good T-SQL tips to simplify code:

While trying to solve the data challenges an organization throws our way, it is often easy to get buried in large, complex queries. Despite efforts to write concise, easy to read, maintainable T-SQL, sometimes the need to just get a data set quickly results in scary code that is better off avoided rather than committed.

This article dives into a handful of query patterns that can be useful when attempting to simplify messy code and can both improve query performance while improving maintainability at the same time!

Click through to learn more. I use most of those tips to great effect (though don’t often have need to hit some of the DMVs Ed mentions).

Comments closed

Splitting Strings with Quoted Names

Published 2022-04-22 by Kevin Feasel

Daniel Hutmacher mixes separators with regular characters:

Suppose you have a delimited string input that you want to split into its parts. That’s what STRING_SPLIT() does:

DECLARE @source nvarchar(max)='Canada, Cape Verde, '+    'Central African Republic, Chad, Chile, China, Colombia, Comoros';

SELECT TRIM([value]) AS[Country]
FROM STRING_SPLIT(@source, ',');

Simple enough. But delimited lists are tricky, because the delimiter could exist in the name itself. Look for yourself what happens when we add the two Congos to the list:

Daniel has a clever solution to the problem.

Comments closed