A Fine Slice Of SQL Server
Camila Henrique summarizes the most recent T-SQL Tuesday:
I had a lot of fun reading your different takes on it! Here are the posts of everyone who contributed (and dutifully linked it back to me).
Click through for the list of entries.
Comments closedSteve Jones thinks about zero:
I’m not great at building charts and graphs. I can build a basic chart, but I often depend on the tooling I use to size, scale, etc. appropriately for whatever I’m graphing. That, or I just use a basic graph that starts from zero and has some sort of linear scale. Or I just present a table of numbers.
There are plenty of misleading charts, especially used by the media that want to show some particular aspect of data that suits the story they are reporting. Many of these misleading charts often don’t start at zero, and they end up scaling in a way that can confuse people.
Steve references a lengthy article on the topic, one which is definitely worth the read, especially because as far as I’m aware, most of the academic literature on visualization and starting at 0 ignores line charts. The only work I’m familiar with is Cleveland, McGill, and McGill, who recommended banking to 45 degrees (and here’s an example of it in SAS).
Comments closedNick Edwards hits us with bad news:
Unfortunately, as of April 2022 there is not an option to parameterise or add dynamic content to an “Execute Pipeline” activity to invoke a pipeline run.
However, with the use of a Microsoft API there is method which we can use to overcome this.
Click through to learn more about this API call and see an example of it in action.
Comments closedPaul Randal answers a question:
I had an email discussion with someone last week who was wondering about log backups and minimally-logged operations. Specifically they were confused as to how the log backup works if no checkpoint has occurred since the operation completed. Surely the data pages are still dirty in memory?
Not so.
Read on to learn more about eager writing.
Comments closedBrent Ozar demonstrates index rebuild-based locking:
You’re investigating your SQL Server’s top wait stats, and you’re noticing a lot of LCK% waits. Normally, that indicates blocking and deadlocks, but you’re just not getting complaints from your end users, and you’re wondering what’s causing it.
It might be overzealous index rebuilds.
Click through for the demonstration and some practical advice.
Comments closedTom Zika and I are on opposite sides:
So I’ll take it one step further.
I’ll never use semicolons unless I have to.Tools like Redgate’s SQL Prompt can add semicolons automatically, but I still won’t do it.
My quick thoughts:
And at the end of the day, consistency and readability are the most important things…though I’ll fight for my aesthetics like I’m the third monkey in line for Noah’s Ark and brother, it’s starting to rain.
Comments closedMartin Schoombee brings up an interesting point:
We were running SSIS in an Azure VM, spinning the VM up and down as required to run the ETL processes. A third-party SSIS component was used to extract data out of Dynamics 365 CRM, and accounted for a significant part of the yearly costs. I blogged about the reasons why I think it’s worth moving from Azure AS to Power BI PPU before, and combined with the move to Azure Data Factory I estimated a cost reduction of almost 35%.
After deploying the solution I noticed that our daily ETL costs were significantly higher than I thought it would be, and that started a little rabbit-hole exercise to figure out why.
I’m used to thinking about managed virtual networks in the case of Azure Synapse Analytics, where I think it makes a lot of sense as a default (especially because you can’t switch after you’ve made a decision).
Comments closedItzik Ben-Gan has an interesting challenge for us:
The challenge is as follows:
Assuming partitioning based on the column grp and ordering based on the column ord, compute sequential row numbers starting with 1 within each consecutive group of rows with the same value in the val column. Following is the desired result for the given small set of sample data:
Click through for an example of this, a pair of “classic” solutions, and Itzik’s solution.
Comments closedMatt Allington pulls out the dynamite:
OK, so what’s the problem? The problem is that sometimes you get such a deep, nested path of measures that the underlying formula is more complex than it needs to be, and it is virtually impossible to debug. If you simply explode/expand a final measure into the full, underlying DAX code, it makes it much easier to see what is going on. Returning to the DAX example above, this is what the third measure actually looks like under the hood, removing all references to the dependent measures
Click through for an article and a video.
Comments closedWhen doing inserts, updates or delete statements using T-SQL always, and I mean always, run them inside a transaction.
You can do this in different ways in SQL Server. One of the most popular ways is to start an explicit transaction by using the begin transaction command. From there you can either commit the transaction if it is good or issue a rollback command to undo the transaction.
Read on for the why. This is excellent advice.
Comments closed