Press "Enter" to skip to content

Category: T-SQL

Dueling Sequences for Positive and Negative Numbers

Published 2023-09-19 by Kevin Feasel

Jose Manuel Jurado Diaz hears banjo music:

SQL Server’s INT data type, by design, provides a range from -2,147,483,648 to 2,147,483,647. But often, developers only utilize the positive range for primary keys and other identifier fields, effectively wasting half of its potential. What if we could harness this full range to temporarily extend the capacity of an INT column? In this article, we explore this idea in-depth.

I’m of two minds here. On the one hand, surrogate keys don’t have a meaning (by definition!), so it doesn’t really matter if that number is positive or negative. Also, including negative numbers makes sense when you expect the steady state table size to be above 2 billion but below 4 billion rows (assuming that you’re using an INT datatype), or you’ve found out that the steady state size is that big after the fact.

On the other hand, I don’t like having a caller define whether they want positive or negative values, as that now imbues meaning to the surrogate key, where positive keys mean one thing and negative keys mean another.

Comments closed

Alternatives to GREATEST and LEAST in SQL Server 2022

Published 2023-09-19 by Kevin Feasel

Drupal Grupal gives us alternatives:

If you haven’t already heard, SQL 2022 has introduced a new built-in system function called GREATEST. Simply put, it is to a set of columns, variables, expressions etc. what the MAX function is to a set of values (i.e., rows) of a single column or expression. The opposite of GREATEST function is LEAST function which returns the smallest value from the supplied list of columns, variables, constants etc. GREATEST & LEAST can be considered a pair, just as MIN/MAX functions are.

In other situations, GREATEST() and LEAST() are known as ARGMAX() and ARGMIN(), respectively.

Drupal shows us two alternatives to the built-in function and includes a performance comparison.

Comments closed

TINYINT Casts in Spark SQL vs T-SQL

Published 2023-09-12 by Kevin Feasel

Bill Fellows runs into an interesting oddity:

Yet another thing that has bitten me working in SparkSQL in Databricks—this time it’s data types.

In SQL Server, a tinyint ranges from 0 to 255 but both of them allow for 256 total values. If you attempt to cast a value that doesn’t fit in that range, you’re going to raise an error.

SQL Server’s TINYINT data type is an unsigned one-byte number, whereas TINYINT in Spark SQL is a signed one-byte number. But that’s not the biggest difference Bill finds, so check out the post to learn more.

Comments closed

String Aggregation with STRING_AGG

Published 2023-09-01 by Kevin Feasel

Jeff Mlakar uses the STRING_AGG() function:

Often when generating dynamic sql we must add some separator values in the string(s) we build. A function delivered way back in 2017 called STRING_AGG is a simple way to do this. Let’s look at some examples.

There are a lot of people who have never heard of the function and don’t know that there’s a better alternative to the combination of STUFF() and FOR XML PATH to concatenate together strings.

Comments closed

Executing Transactions in PostgreSQL

Published 2023-08-31 by Kevin Feasel

Salman Ahmed rolls it back:

Transactions, like any other database, are a key component of PostgreSQL. A transaction is a sequence of one or more database operations that are executed as a single unit of work. These operations can be queries (e.g. SELECT, INSERT, UPDATE and DELETE) that modify the database’s state.

A transaction’s main purpose is to combine multiple statements into an atomic, all-or-nothing process. It ensures that either all operations within a transaction are fully completed, or none of them are executed at all. Concurrent transactions cannot see each other’s unfinished changes. Updates from ongoing transactions remain hidden until completion, at which point all changes become visible simultaneously.

This is very similar to SQL Server, except their savepoints actually work they way they’re supposed to.

Comments closed

Using IS DISTINCT FROM in SQL Server 2022

Published 2023-08-31 by Kevin Feasel

Chad Callihan is distinguished:

One feature introduced with SQL Server 2022 that I’ve recently been playing around with is IS [NOT] DISTINCT FROM. This new feature can help when it comes to dealing with NULL value comparisons.

Read on for examples. Do note that x IS NOT DISTINCT FROM y does not provide a performance benefit over its equivalent of x=y OR (x IS NULL AND y IS NULL).

Comments closed

DATEDIFF() and Month Boundaries

Published 2023-08-22 by Kevin Feasel

Deb Melkin fed the mogwai after midnight:

I was working on a query this week that reminded me of a fun quirk when working with dates and the DATEDIFF function in particular.

I have a process that takes a while to run. Because of all of the moving parts to keep track of, I have an audit table to track what I’m doing to collect basic info like when did it start, when did it end, etc. I created a simple report for myself to break things down so I can report back to the team. I threw together a simple SQL statement, using DATEDIFF to figure out the how long things took. Looking at the results, I saw some interesting results.

Read on for two queries, one which has a bit of a problem and one which strives to correct that problem.

Comments closed

ANSI_PADDING and Its Relevance

Published 2023-08-16 by Kevin Feasel

Vitaly Bruk won’t be late to meetings anymore:

Today, I have late for the team meeting … 🙁

When I joined a Teams meeting, the team discussed ANSI_PADDING. One of the teammates fires at me with the teacher’s voice: “What are we talking about? What is ANSI_PADDING? Shoot!”.

That was a joke, but … just in case, let’s see what is this.

Read on to learn what the ANSI_PADDING attribute is and why it’s relevant.

Comments closed

Working with Similar but Different Schemas across SQL Server

Published 2023-08-15 by Kevin Feasel

Aaron Bertrand writes a query, or maybe two:

At Stack Overflow, our environment has multiple implementations of a largely – but not 100% – identical schema. (By “schema,” I mostly mean the set of tables within a database.) I sometimes need to retrieve or update data across a large number of sites. Let’s say, pulling or removing information from the Users table, and related tables, across all of our databases.

Most of the time, we think of “schema drift” as “different from a Platonic ideal of our schema” and try to correct it. In this case, Aaron shows how we can still write queries while embracing schema drift as a basic fact.

Comments closed