Press "Enter" to skip to content

Curated SQL Posts

Exception Handling in Scala

Published 2022-04-29 by Kevin Feasel

Pallav Gupta shows several methods for handling errors using Scala:

Error handling is the process of handling the possibility of failure. For example, failing to read a file and then continuing to use that bad input would clearly be problematic. Noticing and explicitly managing these errors saves the rest of the program from various pitfalls.

When an exception occurs, say an Arithmetic Exception then the current operation is aborted. Then the runtime system looks for an exception handler that can accept an Arithmetic Exception. Control resumes with the innermost handler. If no such handler exists, the program terminates.

Pallav starts with the most expensive option and ends with the best option with the Either monad.

Comments closed

Preventing Accidental Runs of Powershell Scripts

Published 2022-04-29 by Kevin Feasel

Jana Sattainathan has us hold our horses:

You might want to read this to the end! You are into PowerShell and have a ton of scripts. The problem is some of them run things that are not meant to be run at any time by anyone except you when you first set it up  – like dropping the entire contents of a folder and sub-folders to reinitialize.

Once you realize that you accidentally hit F5 on your “DropFolderContents.ps1” script, it is already too late. The damage is done.

Click through for several techniques to prevent this.

Comments closed

Persisting Data in Azure Redis Cache

Published 2022-04-29 by Kevin Feasel

Arun Sirpal feeds the mogwai after midnight:

I mentioned before that you could use the idea of data persistency to rebuild your data from total failure. There are two types. RDB and AOF.

RDB – persists a snapshot of your cache in a binary format. The snapshot is saved in an Azure Storage account. AOF – saves every write operation to a log. The log is saved at least once per second into an Azure Storage account. 

I’m a big proponent of using Redis as a caching service. I’m not a big proponent of using Redis as a persisted database, mostly because I’ve had a lot of bad experiences with persistent Redis…

Comments closed

Tips for Better Graphs

Published 2022-04-28 by Kevin Feasel

Cole Nussbaumer Knaflic shares a couple recommendations:

When creating a graph to explain something to someone else, I recommend that you declutter and focus attention. These concepts are not new. We have taught and written about them—directly and indirectly—many times before. I wrote about them again when drafting my new book, but then decided to take a different approach. Rather than relinquish my original words to a dismal fate in my computer’s trash bin, I thought perhaps they might still be of use here. After all, even if we’ve shared good advice before, sometimes it bears repeating.

Those are great general principles and Cole has specific examples of the principles in action.

Comments closed

Change Data Capture in Azure SQL Database

Published 2022-04-28 by Kevin Feasel

Abhiman Tiwari announces that CDC has gone GA:

CDC is now generally available on Azure SQL databases, enabling customers to track insert / update / delete data changes on their Azure SQL Database tables. On Azure SQL database, CDC offers a similar functionality to SQL Server and Azure SQL Managed Instance, providing a scheduler which automatically runs change capture and cleanup processes on the change tables. These capture and cleanup processes used to be run as SQL Server Agent jobs on SQL Server on premises and on Azure SQL Managed Instance, but now they run automatically through the scheduler in Azure SQL databases. Customers can still run scans and cleanup manually on demand.

Looks like it works pretty much the same as on-premises SQL Server, so it’s got that going for it.

Comments closed

Deploying an Azure Function via Azure DevOps

Published 2022-04-28 by Kevin Feasel

Koen Verbeeck wants to deploy a Powershell-based Azure Function:

In the blog post Azure Function with PowerShell and the Power BI REST API I explained how you could create an Azure Function using the PowerShell scripting language. This Function connected with the Power BI REST API and retrieved the last refresh status of a dataset. Developing the Function is one thing, deploying it is another. In this blog post I’ll guide you through the set-up of a build and release pipeline in Azure Devops. As a prerequisite, the Azure Function and its dependencies (for example the requirements.psd1 file) are all checked into a Git repo. As a reminder, the folder structure looks like this:

Read on for the walkthrough.

Comments closed

Binding a “Preview” Shortcut in SSMS

Published 2022-04-28 by Kevin Feasel

Daniel Hutmacher previews a table with a keyboard shortcut:

On the surface, these query shortcuts are just what the name implies – a key combination that you can press to run a command or execute a stored procedure. But there’s a hidden super power: whatever text you’ve selected in SSMS when you press the keyboard combination gets appended to the shortcut statement.

That is quite useful, though I’ve already bound all of those SSMS shortcuts to various forms of WhoIsActive.

Comments closed

This Month in Synapse

Published 2022-04-28 by Kevin Feasel

Ryan Majidimehr recaps the updates for Azure Synapse Analytics in April of 2022:

Since the GA of Synapse, customers have asked for a fine-grained RBAC (role-based access control) role that allows a user persona to monitor the execution of Synapse Pipelines and Spark applications without having the ability to run or cancel the execution of these applications.  

Now, customers can assign the Synapse Monitoring Operator role to such monitoring personas. This allows organizations to stay compliant while having flexibility in the delegation of tasks to individuals or teams. 

That is indeed useful.

Comments closed

Simplifying T-SQL Code

Published 2022-04-28 by Kevin Feasel

Ed Pollack shares several good T-SQL tips to simplify code:

While trying to solve the data challenges an organization throws our way, it is often easy to get buried in large, complex queries. Despite efforts to write concise, easy to read, maintainable T-SQL, sometimes the need to just get a data set quickly results in scary code that is better off avoided rather than committed.

This article dives into a handful of query patterns that can be useful when attempting to simplify messy code and can both improve query performance while improving maintainability at the same time!

Click through to learn more. I use most of those tips to great effect (though don’t often have need to hit some of the DMVs Ed mentions).

Comments closed