Tag: what is

An Introduction to Invisible Columns in MySQL

Posted on by Ian

In MySQL 8.0.23 and later, we have the ability to create invisible columns when creating or altering a table.

An invisible column is one that is hidden to certain SQL queries, but can be accessed if explicitly referenced.

Basically, invisible columns are hidden to queries that use the asterisk wildcard (*) in their SELECT list. But they can be accessed by explicitly referencing the column by its name.

Invisible tables are hidden from the TABLE statement, as this statement provides no way of selecting individual columns.

Continue reading →

Fix Error “The function ‘DENSE_RANK’ must have an OVER clause” in SQL Server

Posted on by Ian

If you’re getting error 10753 when using a window function in SQL Server, it’s probably because you’re calling the function without an OVER clause.

When using the DENSE_RANK() function, the error message reads “The function ‘DENSE_RANK’ must have an OVER clause”.

The DENSE_RANK() function requires an OVER clause (and that clause must have an ORDER BY clause).

To fix this issue, add an OVER clause to the DENSE_RANK() function.

Continue reading →

How to use VALUES as a Stand Alone SQL Statement in PostgreSQL

Posted on by Ian

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.

Continue reading →

Understanding the Right Shift and Left Shift Operators in SQL Server

Posted on by Ian

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 reading →

Using VALUES as a Stand Alone Statement in MySQL

Posted on by Ian

MySQL 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.

Continue reading →