Derik Hammer notes that you cannot rollback a TRUNCATE TABLE operation in Redshift:
In SQL Server, or PostgreSQL for that matter, the TRUNCATE command is allowed in a transaction and it will commit or rollback like any other DML operation. In all of the scripts, below, I will do the following.
Check my row counts
Begin a transaction
TRUNCATE the table
INSERT one row
Check my row counts from within the transaction
ROLLBACK
Check to see if my row checks revert or not
That’s an important behavioral difference when loading data using a truncate-and-reload pattern.
Dave Mason investigates the valid data types you can use as inputs for WAITFOR:
There are certain design patterns in T-SQL that give me pause. They may not be “code smells” per se, but when I encounter them I find myself thinking “there’s got to be a more sensible way to accomplish this”. WAITFOR DELAY is one example. I’ve used it a few times here and there, mostly in one-off scripts. If I wrote some code that used it regularly in production, I’d be paranoid about putting my SPID to sleep forever. Maybe a little bit of paranoia is a good thing. But I digress.
A recent task found its way to me, and I’ve decided to use WAITFOR DELAY as part of my solution. (It hasn’t been tested or implemented yet–perhaps more on this in another post.) My usage this time has been more complex than in the past. What I already knew is that you can use a string literal for the time_to_pass argument. For example, this will delay for 3½ seconds:
WAITFOR DELAY '00:00:03.500'
Click through for a bunch of testing.
Doug Kline explains the ANY and ALL operators in SQL:
-- note that this creates a single column of values -- which could be used in something like IN -- for example SELECT 1 WHERE 12 IN ( SELECT tempField FROM (VALUES(11),(12),(7)) tempTable(tempField)) -- I could rephrase this as: SELECT 1 WHERE 12 = ANY ( SELECT tempField FROM (VALUES(11),(12),(7)) tempTable(tempField))
I rarely see these operators in the wild and might have used them in production code a couple of times if that.
Aaron Bertrand gives us the highlights:
Certificate Management in Config Manager View and validate all of your certificates from a single interface, and manage and deploy certificate changes across all of the replicas in an Availability Group or all of the nodes in a Failover Cluster Instance.
Built-in data classification A new
ADD SENSITIVITY CLASSIFICATIONstatement helps you identify and automatically audit sensitive data, a huge step up from the previous SSMS wizard (which just used extended properties).
Aaron also digs into the engine a bit:
APPROX_COUNT_DISTINCT
This new aggregate function is designed for data warehouse scenarios, and is an equivalent for
COUNT(DISTINCT()). Instead of performing expensive distinct sort operations to determine actual counts, it relies instead on statistics to get something relatively accurate. You should find that the margin of error is within 2% of the precise count, 97% of the time, which is usually fine for high-level analytics, values that populate a dashboard, or quick estimates.On my system I created a table with integer columns ranging from 100 to 1,000,000 unique values, and string columns ranging from 100 to 100,000 unique values. There were no indexes other than a clustered primary key on the leading integer column. Here are the results of
COUNT(DISTINCT())vs.APPROX_COUNT_DISTINCT()against those columns, so you can see where it is off by a bit (but always well within 2%):
By the way, APPROX_COUNT_DISTINCT() is a really good idea, and I’m glad it’s here.
Doug Lane walks us through various scenarios with updates including the same column multiple times:
An application developer came to me with this question recently: “Can I use the same column twice in a SQL UPDATE statement?”
Yes and no.
It depends on what you mean by “use”.
Read on to see what Doug means.
Randolph West looks at the AT TIME ZONE clause when working with a specific time zone in SQL Server:
The time zone name is taken from a list maintained in the following Windows registry hive:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time ZonesNote: For SQL Server on Linux (and Docker containers), a registry shim performs the same function as the Windows registry by intercepting the API calls and returning the expected value(s).
We can also use a Transact-SQL (T-SQL) query against the system view
sys.time_zone_info, which uses the information from the registry hive. This is the recommended method if you do not have access to the registry hive.
Click through for a couple of examples.
Bert Wagner shows off the FIRST_VALUE window function and walks us through a case it struggles with:
The SQL Server FIRST_VALUE function makes it easy to return the “first value in an ordered set of values.”
The problem is that if that first value happens to be a NULL, there is no easy, built-in way to skip it.
While a UserVoice item exists to add the ability to ignore nulls (go vote!), today, we’re going accomplish that end result with some alternative queries.
Click through for the demo, as well as a video version of the post.
Ryan Desmond demonstrates the purpose of GOTO in T-SQL:
So I was playing around at work today and decided for whatever reason to see how I could get the code I was writing to fire off only in certain situations.
If it’s Sunday maybe, or if this is in a particular environment, or if a record in an admin table was something specific. I’m not sure how I’ll use this but I stumbled on Labels and decided to play with them.
Ok, so how to get to know labels. Well, in order to get them to work sometimes I have to create labels that are based on some criteria.
I do try to avoid these as much as possible, but they are valid syntax and I’ve seen a couple of cases where it makes sense to use GOTO.
Steve Jones reminds us that when running a window function, there is a default window in place:
What I want to do is compare the passing yards each year with the most current value for that player, showing the plus or minus. This means that for Aaron Rodgers, who threw for 1675 yards in 2017, I’d want to show this for the first few years of his career:
This shows me an easy view of the years where he was better in his career than he is now. Last year was likely a down year because of injury, but we’ll see this year.
In any case, if I run this query using LAST_VALUE() for the final year of his career, I don’t get the right results.
It’s good to keep in mind the full syntax for a window function for just this reason.
Hugo Kornelis has a brain-teaser for us:
The query below can be executed in any version of the AdventureWorks sample database. Don’t bother understanding the logic, there is none. It is merely constructed to show how SQL Server handles what appears to be an impossible situation.
If you look at the descriptions of the various join operators in the Execution Plan Reference, you will see that this query poses the optimizer for what appears to be an insolvable problem: none of the join operators can be used for this query!
But it’s possible, and Hugo explains exactly what happens, as well as places where the optimizer could be better at solving the impossible (or at least marginally difficult).
Kevin Feasel
2018-10-30
Syntax
No Comment