exa-SebastianB
Team Exasol
Team Exasol

Whether you are a user of Exasol or a software developer working on a SQL client, keywords are an important concept to know.

SQL key words are part of the syntax of SQL statements — the smallest building blocks if you will. SELECT for example is a keyword that everyone knows...

What this article covers

What I want to point out in this article is that keywords come in two different flavors and that this has a big impact on what you can and can't do with them:

  • reserved
  • non-reserved

Which keywords exist in Exasol?

Luckily there is a system table called EXA_SQL_KEYWORDS that contains that list and it obviously fits with the installed version.

SELECT * FROM EXA_SQL_KEYWORDS;

Notice the column named RESERVED. If that value is false, you are allowed to use that keyword as an identifier. Let's try this out.

SELECT *
FROM VALUES ('MON'), ('TUE'), ('WED'), ('THU'), ('FRI'), ('SAT'), ('SUN')
AS DAYS("DAY");

Here we use the non-reserved keyword "DAY" as an identifier. Try that statement in your SQL client to see the effect. Since "DAY" is still a keyword, you need to enclose it in double quotes for the query to succeed.

Syntax Highlighting

In a proper SQL client, syntax highlighting is a good indicator whether you hit a keyword or not. Look at the side-by-side comparison in this screenshot from DBeaver.

exa-SebastianB_0-1615967536695.png

On the left side you see a the keyword "DAY" properly quoted and displayed in the color of an identifier. On the right I "forgot" the quotes and get the word highlighted as a keyword instead. Of course the query on the right will fail.

Should I use non-reserved keywords as identifiers?

The short answer is, no.

The reason is that it adds an unnecessary source of human error. If you don't use keywords as identifiers, then there is one less thing to worry about. That being said, you are sometimes forced to.

Imagine a user of your query expecting the columns of a table to have strict predefined names and those happen to be non-reserved keywords.

So, the more precise answer is: only use non-reserved keywords as identifiers if you are forced to.

Please let us know about your experiences and any feedback you have on this article below! 

Comments
mwellbro
Xpert

what this reminded me of:
"(...)

  • Data Warehouse Engineers have more flexibility in naming their tables and columns. Previously, users couldn’t name their tables with SQL Reserve Words, e.g., “table” can’t be the name of your table or column. With proper quoting, this limitation is removed. 

(...)"
taken from https://community.microstrategy.com/s/article/KB483540-Unified-Quoting-Behavior-for-Warehouse-Identi...

I guess one man´s "unnecessary source of human error" is another man´s "limitation" to be removed 😉

That being said, wherever I can I try to stay away from using reserved words as identifiers.

exa-SebastianB
Team Exasol
Team Exasol

Flexibility is great, I agree with that. I still shudder when I remember times when filenames were restricted to 8-dot-3 character (on some OSes at least).

It's probably the software engineer inside of me speaking, but I would like SQL better if all identifiers would have to be always quoted in an identical way. If there was no chance to not quote an identifier, using keywords as identifiers would be a lot safer.
On the other hand typing ad-hoc queries with lots of quotes ends up in finger acrobatics that probably no one is particularly fond off. So I guess all in all the SQL standard is a pretty good compromise in UX when it comes to identifiers.