Many of us who work with SQL databases have used the VALUES keyword with the INSERT statement when inserting data into a database.
But perhaps a lesser known fact is that some RDBMSs allow us to use VALUES as a stand alone SQL statement. PostgreSQL is one such RDBMS.
When used as a stand alone statement, we can use a VALUES statement instead of a SELECT statement.
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 readingIf you’re getting error message 4112 that reads “The function ‘CUME_DIST’ must have an OVER clause with ORDER BY” when using the CUME_DIST() function, it’s probably because you’re omitting the ORDER BY clause from the OVER clause.
When using the CUME_DIST() function in SQL Server, we must include an OVER clause that contains an ORDER BY clause. This error happens when we provide the OVER clause but not the ORDER BY clause.
To fix this error, add an ORDER BY clause to the OVER clause.
With SQL we can use various methods to check whether or not a table (or other object) exists in the database. The method we use will often depend on the RDBMS we’re using, as well as the task we’re trying to undertake.
There’s usually a reason we’re trying to check for the existence of a table, and often the syntax we use will be tied to that reason. For example the ...IF EXISTS clause is a handy addition to the DROP TABLE statement, and the ...IF NOT EXISTS clause can often be used with the CREATE TABLE statement.
Other times we may simply want to see if the table exists without performing any immediate actions against that table. In such cases, we would need to run code specifically to see if the table exists.
Below are examples of code we can use in each of the above scenarios.
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 readingMySQL 8.0.19 introduced the VALUES DML statement, which is a table constructor that can be used as a stand alone SQL statement.
This is not to be confused with the VALUES keyword that’s used with the INSERT or REPLACE statements when inserting data (although it can be used for that purpose too). It also shouldn’t be confused with the VALUES() function that’s used with INSERT … ON DUPLICATE KEY UPDATE statements.
If 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.