SQL Server 2022 introduced a bunch of new bit manipulation functions and operators. Amongst these are the right shift and left shift operators.
The right shift and left shift operators can be used to shift bits to the left or right. Given an integer or binary expression, these operators can shift bits by the amount that we specify.
Continue readingIn SQL Server, we can use the GET_BIT() function to return the bit from the specified location in a value. We pass the value as either an integer or binary expression.
The function accepts two arguments; the expression, and the offset. The function then returns the bit from the given offset in the expression.
Continue readingIf you’re getting error number 4123 that reads “Window element in OVER clause can not also be specified in WINDOW clause” in SQL Server, it’s probably because you’re referring to a named window with the same clause that’s in the named window.
When we use the OVER clause to refer to a named window in SQL Server, we can’t include a clause that’s also present in the named window.
To fix the issue, remove one of the clauses – either the one in the OVER clause or the one in the named window.
In SQL Server, the BIT_COUNT() function returns the number of bits set to 1 in the given argument, based on the binary equivalent of that argument.
Different values will have a different number of 1s in their binary value. Also, the same value can have a different number of 1s, depending on the data type.
Continue readingWhen we use SQL Server’s GENERATE_SERIES() function to create a series, we provide the start and stop points as numbers. And if we provide the optional step argument, then it’s provided as a number too.
The numbers we provide can be decimal fractions if required. This means that we can create a series made up of decimal fractions. But we still need to ensure that all our arguments are of the same type.
Continue readingIf you’re getting error 8144 with a message that reads “Procedure or function GENERATE_SERIES has too many arguments specified” in SQL Server, it’s because you’re passing too many arguments to the GENERATE_SERIES() function.
The GENERATE_SERIES() function/relational operator accepts a minimum of two arguments, and a maximum of three (at least, this is the case at the time of writing).
So to fix this error, be sure to provide either two arguments or three when using the GENERATE_SERIES() function.
SQL Server 2022 introduced the SET_BIT() function that sets a bit in an integer or binary value (other than a large object).
The function allows us to specify an offset for which to set a bit to 1 or 0.
Many RDBMSs support the concept of named windows – windows that we can reference from within an OVER clause when defining a window function.
For those of us using SQL Server, we had to wait until the release of SQL Server 2022 before we could create named windows for our window functions. But with the release of SQL Server 2022, we now have the option of using named windows in our window functions.
Continue readingIn SQL Server, RIGHT_SHIFT() is a bit manipulation function that returns the first argument bit-shifted right by the number of bits specified in the second argument.
The RIGHT_SHIFT() function was introduced in SQL Server 2022.
If you’re getting SQL Server error msg 9837 that tells you “The date value is less than the minimum date value allowed for the data type…”, it sounds like you’re using the DATETRUNC() function with the week date part on a date that would cause the result to backtrack to a date that’s earlier than the date type supports.
This is a rare error that only occurs when using the week date part with the DATETRUNC() function on a very small number of early dates.
One way to deal with this error would be to use iso_week instead of week, if that’s suitable for your particular situation.