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.
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.
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
What’s missing? The following scenario:
No rows due to
Click through to see how
DBCC CHECKIDENT behaves differently depending upon the scenario.
— 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.
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
WHERE clauses. This is a nicer solution, though.
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.
If the column is
NOT NULL, then not specifying
NOT NULLwill 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.
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).
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.
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.