Category: Syntax

Jon Shaulis shows why you want to reference tables when including column names in queries:

If you don’t read the rest of this setup, I want you to take away one thing. 

Always reference your tables with your columns when more than one table is involved in the query!

This post is made primarily with SQL Server in mind, but this behavior is actually ANSI SQL and can be replicated in PostgreSQL, MySQL, and Oracle.

Jon’s example is a case where perfectly valid ANSI SQL logic (which is why you can replicate this across platforms and why it’s not a bug) leads to an unexpected result.

Koen Verbeeck shows how two words makes solving a problem with Snowflake a lot easier than with SQL Server:

Sometimes you need to find the previous value in a column. Easy enough, the LAG window function makes this a breeze (available since SQL Server 2012). But what if the previous value cannot be null? You can pass a default, but we actually need the previous value that was not null, even if it is a few rows back. This makes it a bit harder. T-SQL guru Itzik Ben-Gan has written about the solution to this problem: The Last non NULL Puzzle. It’s a bit of tricky solution. 

Click through for the magic words and if you’re on the SQL Server side, upvote this issue to get that functionality in SQL Server too.

Anvesh Patel does not like DISTINCT:

I am telling you personally that I hate the use of DISTINCT.
DISTINCT used by those people, who are not sure about their data set or SELECT statement or JOINS.
Whenever I get any query with DISTINCT, immediately I suggest to remove it.

I agree with this sentiment about 85% of the time. There are cases where I know l am working with data at a finer grain than I need and the counts aren’t important. But just tossing a DISTINCT on a query to stop it from repeating rows is the wrong approach: figure out why that repetition happens and fix it.

Koen Verbeeck notes that Snowflake DB aliasing is a bit more robust than SQL Server’s:

That’s right. I defined an expression – a concatenation of two fields – and used the alias of that expression in another expression. Furthermore, I used the alias of the second expression in the WHERE clause. Gasp.

My workaround is to use CROSS APPLY and define calculations in blocks there. This doesn’t work for aggregation operations, but in cases like Koen’s example, it does simplify the SELECT and WHERE clauses. This is a nicer solution, though.

Lukas Eder points out that there are two ways to use PERCENTILE_DISC() and most database platforms support only one:

Oracle has the most sophisticated implementation, which supports both the ordered set aggregate function, and the window function version:
– Aggregate function: PERCENTILE_DISC (0.5) WITHIN GROUP (ORDER BY x)
– Window function: PERCENTILE_DISC (0.5) WITHIN GROUP (ORDER BY x) OVER (PARTITION BY y)

But there are ways to calculate PERCENTILE_DISC() using a couple of window functions, so read the whole thing.

Solomon Rutzky points out a few things which you can unintentionally change when running an ALTER TABLE [tbl] ALTER COLUMN [col] command:

If the column is NOT NULL, then not specifying NOT NULL will cause it to become NULLable. The documentation for ALTER TABLE even states:

ANSI_NULL defaults are always on for ALTER COLUMN; if not specified, the column is nullable.

Let’s see for ourselves. 

Solomon also has a couple collation-related items, including unexpected silent truncation when working with UTF-8 collations.