A Fine Slice Of SQL Server
Itzik 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 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 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 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 closedChad Callihan has some advice:
A quick T-SQL/SSMS trick that I know and love is the ability to add text to multiple lines at a time. I tend use macros in Notepad++ to accomplish these types of repetitive actions but this type of work can be completed in SSMS.
Annoyingly, this doesn’t work quite the same way in VS Code or Azure Data Studio. Alt + Down moves a line down one, Alt + Shift + Down copies the current line. To replicate this behavior in VS Code / Azure Data Studio, hold down Alt + Shift and use the mouse to select the lines you want.
Comments closedCathrine Wilhelmsen gets some Lego data:
In the April session of the Azure Synapse Analytics and Microsoft MVP series, I got to show one of my favorite demos: ingesting and exploring LEGO datasets! 🤓 In just a few minutes, I grab a compressed file from the web, decompress it, store it as a Parquet file in my data lake, and run SQL queries on it using Serverless SQL. It really is that simple:
Click through for a video and a step-by-step walkthrough.
Comments closed