The introduction of the LEAST() and GREATEST() functions in SQL Server 2022 were a welcome addition. These functions enable us to get the minimum or maximum value from a list of values. There are plenty of use cases for these functions.
One such use case is to provide a cap on the values returned by a query.
If you’re getting SQL Server error 402 that reads something like “The data types numeric and numeric are incompatible in the approx_percentile_disc operator“, it’s probably because you’re trying to use the APPROX_PERCENTILE_DISC() function on a column of the wrong data type.
It could be that you’ve simply passed the wrong column, or it could be that the column is the correct one, but it’s of the wrong type.
To fix, be sure that the column/expression is of a supported type.
SQL Server 2022 introduced the GREATEST() function that returns the maximum value from a list of values. You may be thinking, “but there’s already a MAX() function that returns the maximum value, so why the need for another function that does the same thing?”.
Well here’s the thing – they don’t do the same thing. They’re actually quite different functions, used in different scenarios.
If you’re wondering what the difference is between the MAX() and GREATEST() functions, read on to find out.
When using the APPROX_PERCENTILE_DISC() function in SQL Server, you may get an error that reads “The function ‘APPROX_PERCENTILE_DISC’ must have a WITHIN GROUP clause“. If you get this error, it’s because you omitted the WITHIN GROUP clause when using the APPROX_PERCENTILE_DISC() function.
To fix this issue, make sure you include the WITHIN GROUP clause whenever you use the APPROX_PERCENTILE_DISC() function.
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.
If you’re using SQL Server’s GENERATE_SERIES() function/relational operator and you’re getting an empty result set, then it could be due to one of the following reasons.
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.
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).
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.