Finding The Last Non-Null Value With Snowflake

Kevin Feasel

2019-02-13

Syntax

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.

Concerns With DISTINCT

Kevin Feasel

2019-02-11

Syntax

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.

Digging Into DBCC CHECKIDENT

Solomon Rutzky covers the four primary scenarios when running DBCC CHECKIDENT and specifying a new reseed value:

So, when specifying a “new_reseed_value“, the possible scenarios covered are:
1. Rows exist
2. No rows due to none inserted since the table was created
3. No rows due to TRUNCATE TABLE operation


What’s missing? The following scenario:
No rows due to DELETE operation!!

Click through to see how DBCC CHECKIDENT behaves differently depending upon the scenario.

Using SWITCHOFFSET

Doug Kline has a video and T-SQL script around date/time offsets and particularly the SWITCHOFFSET function:

— so, before SWITCHOFFSET existed, …

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(),'-05:00') AS [EST the easy way], TODATETIMEOFFSET(DATEADD(HOUR, -5, SYSDATETIMEOFFSET()), '-05:00') AS [EST the hard way]

— so, thinking of a DATETIMEOFFSET data type as a complex object

— with many different parts: year, month, day, hour, time zone, etc.

— it looks like SWITCHOFFSET changes two things: time zone and hour

This was an interesting video. I typically think entirely in UTC and let the calling application convert to time zones as needed, but if that’s not an option for you, knowing about SWITCHOFFSET() is valuable.

Snowflake DB Aliasing

Kevin Feasel

2019-01-29

Syntax

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.

Emulating PERCENTILE_DISC

Kevin Feasel

2019-01-28

Syntax

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.

Odd Behavior With Altering Columns

Kevin Feasel

2019-01-16

Syntax

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.

LISTAGG In Snowflake DB

Koen Verbeeck continues investigating Snowflake capabilities:

Since SQL Server 2017, you have the STRING_AGG function, which has almost the exact same syntax as its Snowflake counterpart. There are two minor differences:
– Snowflake has an optional DISTINCT
– SQL Server has a default ascending sorting. If you want another sorting, you can specify one in the WITHIN GROUP clause. In Snowflake, there is no guaranteed sorting unless you specify it (again in the WITHIN GROUP clause).

It looks like LISTAGG is the ANSI standard name, though SQL Server followed Postgres’s lead in calling their function STRING_AGG.

Capturing SQLCMD Errors

Jack Vamvas shows us how we can capture errors from SQLCMD:

I’m executing  code using SQLCMD from a batch file . The code points to a sql file and there is also an output file. 


SQLCMD -E -S MYSERVER\INST1 -i “setup_job_entry.sql” -o “setup_job_entry.log”


But I noticed that if the actual SQLCMD returns an error , for example , if I’m connecting to  an server which doesn’t exist this error message will appear in the output file – but there will  not be an ERROR number , which would allow me to trap and return an appropriate message 

There is a way and Jack shows us how.

Diving Into OPTION(RECOMPILE)

Arthur Daniels explains some of the nuance behind OPTION(RECOMPILE) on T-SQL statements:

SQL Server will compile an execution plan specifically for the statement that the query hint is on. There’s some benefits, like something called “constant folding.” To us, that just means that the execution plan might be better than a normal execution plan compiled for the current statement.
It also means that the statement itself won’t be vulnerable to parameter sniffing from other queries in cache. In fact, the statement with option recompile won’t be stored in cache.

Click through for a couple of demos as well as a discussion of positives and negatives regarding its use.

Categories

March 2019
MTWTFSS
« Feb  
 123
45678910
11121314151617
18192021222324
25262728293031