Press "Enter" to skip to content

Curated SQL Posts

Creating Local Server Groups in SSMS

Published 2022-03-22 by Kevin Feasel

Andrea Allred creates a local server group:

In the past, I have talked about CMS (Central Management Servers), but now I don’t have CMS configured and still want to query multiple instances at once. Local Server Groups are my friend.

My preference is CMS, especially as the number of data platform professionals increases. Keeping track of all of those new instances can be a pain otherwise. But if you’re in an environment in which that’s not an option, local server groups provide a reasonable alternative.

Comments closed

An Overview of Azure Redis Cache

Published 2022-03-22 by Kevin Feasel

Arun Sirpal lays out the use case of Azure Redis Cache:

Redis Cache is a well know caching technology and you can run it in Azure as a fully managed service. A common requirement (the most basic one) is doing a workflow like:

1. When an application needs to retrieve data, it will first search to see if it exists in Azure Cache for Redis.

2. If the data is found in Azure Cache for Redis (cache hit) use it

3. If the data is not found in Azure Cache for Redis (cache miss), then the application will need to retrieve the data from Azure SQL (or whatever cloud db back end you use)

4. For cache miss scenarios, the requesting application should add the data retrieved from the Azure Database to Azure Cache for Redis.

This is also known as the cache-aside pattern. If you’re feeling really cheeky, you can combine cache-aside with the decorator pattern to “hide” the cache in your code.

Comments closed

Running SQL Scripts on Snowflake from Azure Data Factory

Published 2022-03-22 by Kevin Feasel

Koen Verbeeck shows off the Script activity in Azure Data Factory:

Azure Data Factory has a new activity introduced this week (around the 10th of March 2022 for you future readers): the Script activity! This is not to be confused with the script task/component of SSIS, which allows you to execute .NET script (C# for most people, or VB if you’re Ben Weissman). No, this task executes SQL, so it’s more akin to the Execute SQL Task of SSIS.

Click through to see how it works while I lament the fact that SSIS never supported the best .NET language.

Comments closed

An Overview of Simple Parameterization

Published 2022-03-22 by Kevin Feasel

Paul White begins a new series:

This is the first part of a series about simple parameterization and trivial plans. These two compilation features are closely connected and have similar goals. Both target performance and efficiency for workloads frequently submitting simple statements.

Despite the “simple” and “trivial” names, both have subtle behaviours and implementation details that can make how they work difficult to understand. This series doesn’t dwell too long on the basics but concentrates on less well-known aspects likely to trip up even the most experienced database professionals.

In this first part, after a quick introduction, I look at the effects of simple parameterization on the plan cache.

Read on for more detail.

Comments closed

Linear Data Structures

Published 2022-03-21 by Kevin Feasel

Dave Saunders describes the concept of linear data structures:

As we said in the introduction, a data structure is ‘linear’ if the elements form a sequence.

That means that the data structure has a first and last element, and each element is connected to its previous and next element.

– An ‘array’ is a linear data structure; the items are stores sequentially.

– A ‘graph’ is not a linear data structure; any node can be linked to any other node in the graph – there is no fixed ‘sequence’.

Click through for several examples of linear data structures.

Comments closed

Setting Powershell’s TLS Version

Published 2022-03-21 by Kevin Feasel

Frank Gill updates TLS:

I was recently working on a client server and need to install the Az PowerShell module. I opened and administrator window and typed

Install-Module Az

and received this error:

WARNING: Unable to find module repositories.

Turns out that was not the real answer. Click through to see what Frank ended up needing to do.

Comments closed

The Upsert Pattern and Unique Indexes in SQL Server

Published 2022-03-21 by Kevin Feasel

Michael J Swart recommends a unique index:

To avoid deadlocks when implementing the upsert pattern, make sure the index on the key column is unique. It’s not sufficient that all the values in that particular column happen to be unique. The index must be defined to be unique, otherwise concurrent queries can still produce deadlocks.

Read on for Michael’s thoughts as well as some smart commenters adding on.

Comments closed

Cross-Database Graph Query Problems

Published 2022-03-21 by Kevin Feasel

Louis Davidson receives a nastygram from SQL Server’s graph functionality:

Just understand that if you need any of the graphDb underlying data structures, you will need to find their actual physical name and use it. I would definitely suggest never accessing these columns via any method other than the pseudocolmns for production code (because you have no way to predict the column names from dev to prod (you cannot specify the names when creating a table), but this following code does work:

Click through to see the issue and Louis’s workarounds.

Comments closed