Press "Enter" to skip to content

Category: Syntax

Greatest and Least in T-SQL

Published 2022-06-09 by Kevin Feasel

Hasan Savran takes us through a rather useful pair of functions:

GREATEST and LEAST functions are available in Azure SQL and SQL Server 2022. GREATEST returns the maximum value from a list of expressions. LEAST returns the minimum value from a list of expressions. 

     The Highest number of expressions you can pass to these functions is 254. All expressions must be comparable for these functions to work. For example, SQL Server can automatically convert the following string to an integer and find the greatest number.

These kinds of row-wise comparisons cut out a lot of writing OR clauses. Though standard function rules apply: if you put these in the WHERE clause of queries against large tables and expect them to be your primary filters and you’ll probably be in for a nasty surprise.

Comments closed

T-SQL Language Enhancements in SQL Server 2022

Published 2022-06-03 by Kevin Feasel

Chad Baldwin checks out what’s new:

I’ve been exicted to play around with some of the new features and language enhancements that are available in SQL Server 2022 so I’ve been keeping an eye on the Microsoft Docker repository for a new 2022 image. Well, they finally added it to Docker Hub! I immediately pulled the image and started playing with it.

I want to focus on the language enhancements as those are the easiest to demonstrate, and I feel that’s what you’ll be able to take advantage of the quickest after upgrading.

Read on for a dozen or so language enhancements. This isn’t as big a change as what 2012 brought but there is a lot of useful stuff in here, as well as more that has been publicly announced like APPROX_PERCENTILE_CONT() (and _DISC(), yeah, but bah humbug).

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

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

Stored Functions in MySQL

Published 2022-05-10 by Kevin Feasel

Robert Sheldon continues a series on MySQL:

In the previous three articles in this series, I focused on creating basic database objects that you can use to get started with MySQL. You learned how to build an initial database and then add tables, views, and stored procedures. In this article, I cover one more important type of object, the stored function, a routine that is stored in a database and can be invoked on-demand, similar to a user-defined scalar function in SQL Server or other database systems.

I’m not familiar enough with stored functions to know if they have the same performance limitations as what we have in SQL Server (specifically around needing to run everything in the function once for each row) but based on a comment at the end of Robert’s post, it does seem that way.

Comments closed

Counting in KQL

Published 2022-05-10 by Kevin Feasel

Robert Cain continues a series on KQL operations:

The previous post in the series covered the take operator. In that post I mentioned that take was one of the simplest operators in KQL. But it is not the simplest, that honor goes to the count operator.

The count operator does nothing more than takes the piped in dataset and returns the number of rows in it. We’ll see more in a moment.

Click through to see more.

Comments closed

Fun with Natural Full Join

Published 2022-05-05 by Kevin Feasel

Lukas Eder shows off natural joins:

At first I though of the UNION CORRESPONDING syntax, which doesn’t really exist in most SQL dialects, even if it’s a standard feature. But then, I remembered that this is again a perfect use case for NATURAL FULL JOIN, this time slightly differently from the above example where two tables are compared for contents. This time, we want to make sure the two joined tables never have matching rows, in order to get the UNION like behaviour.

I wasn’t aware of the notion of natural joins because they’re not available in SQL Server. They are available in Oracle, Postgres, and MySQL. Fun as Lukas’s blog post is, I could see natural joins going wrong in so many ways.

Comments closed

The take Operator in KQL

Published 2022-05-03 by Kevin Feasel

Robert Cain continues a series on KQL:

In this example we took the Perf table, and piped the dataset it generated into the take operator. We indicated we wanted to get 10 rows, which it did as you can see.

It is important to understand that take grabs these rows at random. Further, there is no consistency between each execution of take. You are likely to get a different set of rows with each execution. Let me run the exact same query again, so you can see the different data being returned.

Take if you want a slice, if you want a piece, if it feels alright.

Comments closed

Empty Catch Blocks in T-SQL

Published 2022-05-03 by Kevin Feasel

Erik Darling looks disapprovingly:

So if you execute the above proc, all it returns is an empty result with no error message.

Kinda weird.

Like not having finger or toenails.

I’m dating myself here but I remember a comment in a TheDailyWTF thread in which somebody puts an ASCII art Pikachu in every empty catch block because hey, you gotta catch ’em all.

Comments closed