mssql

An Introduction to the IS [NOT] DISTINCT FROM Predicate in SQL Server

by

SQL Server 2022 introduced the IS [NOT] DISTINCT FROM predicate that compares the equality of two expressions and guarantees a true or false result, even if one or both operands are NULL.

Normally if we compare two NULL values, they will always be different (although this will depend on your ANSI_NULLS setting – setting ANSI_NULLS to OFF will result in NULLs being treated as equal). The IS [NOT] DISTINCT FROM predicate enables us to compare NULLs as though they’re equal, even when our ANSI_NULLS setting is set to ON.

Read more

CREATE TABLE IF NOT EXISTS Equivalent in SQL Server

by

In SQL, we can use the CREATE TABLE IF NOT EXISTS statement to create a table only if it doesn’t exist. The benefit of doing this is that we won’t get an error if there’s already a table with the same name.

But SQL Server doesn’t support this syntax – at least not in the current version of SQL Server at the time of writing (SQL Server 2022) .

So with SQL Server, we need to do a bit of extra work.

Read more

Fix “Argument value 0 is invalid for argument 3 of generate_series function” in SQL Server

by

If you’re getting SQL Server error 4199 that reads “Argument value 0 is invalid for argument 3 of generate_series function“, it’s probably because you’re passing zero as the third argument to the GENERATE_SERIES() function.

The GENERATE_SERIES() function accepts an optional third argument, but this argument can’t be zero.

To fix this error, either pass a non-zero expression as the third argument, or omit the argument altogether (in order to use the default step of 1).

Read more

Generate a Time Series in SQL Server

by

SQL Server’s GENERATE_SERIES() function returns a series of numbers within a given interval. But just because it returns numbers, doesn’t mean we can’t leverage its functionality in order to generate a series of time values.

If you need to create a series of time values with evenly spaced intervals between a start and end point, maybe the following technique can help.

Read more

Fix “The ORDER BY in WITHIN GROUP clause of ‘APPROX_PERCENTILE_CONT’ function must have exactly one expression” in SQL Server

by

If you’re getting SQL Server error 10751 that reads “The ORDER BY in WITHIN GROUP clause of ‘APPROX_PERCENTILE_CONT’ function must have exactly one expression” it’s probably because you’re using too many ORDER BY expressions with the APPROX_PERCENTILE_CONT() function.

The APPROX_PERCENTILE_CONT() function requires the WITHIN GROUP clause, and that clause requires an ORDER BY sub-clause. However, that ORDER BY sub-clause requires exactly one expression – no more, no less. So, you can’t pass multiple expressions, and you can’t pass zero expressions. It must be exactly one expression.

To fix this issue, be sure to have just one ORDER BY expression in the WITHIN GROUP clause when using the APPROX_PERCENTILE_CONT() function.

Read more

Why GENERATE_SERIES() Only Returns the First Value in the Series in SQL Server

by

If you’re using the GENERATE_SERIES() function to create a series of numbers, but you’re finding that only the first value in the series is returned, it could be something very obvious.

The obvious reason this could happen is that your step value is too big. In particular, if the step is so big that it covers the whole series, then it stands to reason that there will only be one value in the series.

If this is an issue for you, you might want to check that you’re using an appropriate step value. Using a smaller step value can help to create a series with more values.

Read more