How to add a column to all tables which don’t have it already in SQL Server

In SQL Server if you need to add a column to all tables which don’t have it already I found the below script on Stack Exchange the other day which helps create the ALTER statements needed…

SELECT 'ALTER TABLE ' + QUOTENAME(ss.name) + '.' + QUOTENAME(st.name) + ' ADD created_date DATETIME NULL;'
FROM sys.tables st
INNER JOIN sys.schemas ss on st.[schema_id] = ss.[schema_id]
WHERE st.is_ms_shipped = 0
AND NOT EXISTS (
 SELECT 1
 FROM sys.columns sc
 WHERE sc.[object_id] = st.[object_id]
 AND sc.name = 'created_date'
)

The example below shows that the created_date column is not in three tables. The generated ALTER statements can be easily reviewed before executing them.

Add a new columns to all tables that don't have it currentlyWe could also easily modify lines 4 and 8 to create drop statements for a column for all tables in which it exists.

How to use Azure Cache for Redis in a .NET Core web app

If you need a distributed cache and are planning on deploying your .NET Core web app to Azure, using Azure Cache for Redis as your cache provider is a good choice.

The steps below show you how to get started…

Create an Azure Cache for Redis instance using the portal

Search for ‘azure cache for redis’ on the portal and on the create screen fill in details such as resource group, DNS name, location and cache type.

Choose the same location as whatever app will be consuming your cache to minimise latency. When you’re just getting setup I’d recommend choosing the Basic C0 cache as this is the cheapest and you can always upgrade later. For all the other tabs just accept the defaults for the moment.

Create a new Redis cache in the portal

After Azure has finished creating your new cache, navigate into it and go to the Access keys page as shown below.

Copy the primary connection string somewhere as you’ll need to put this into your appsettings.json file in the next step.

Viewing Azure Redis access keys

Setup and configure your app

Install Microsoft.Extensions.Caching.StackExchangeRedis from NuGet.

Amend Startup.cs…

Amend startup.cs to use caching

Add a connection string pointing to your Redis instance on Azure into your appsettings.json file. Remember you can get this from the Access keys page as shown above. In our real apps we’d likely store this value in Azure Key Vault.

appsetting-redis-cache

Create a cache wrapper or helper class

At this stage your app should be able to set and read items in Azure Redis by instantiating a concrete instance of the IDistributedCache instance. The next step is to create a simple thin wrapper around this interface. It’s not 100% needed but it helps keep code that is using the cache a little bit neater.

Since .NET Core 3.0 Microsofts default JSON deserializer has been System.Text.Json, however it doesn’t support circular references so in the below example I’ve fallen back to using Newtonsoft.

Note… according to Microsoft System.Text.Json will support circular references from .NET 6.0.

Cache wrapper

Inject IDistributedCache into your controller or service class

Since dependency injection is built into .NET Core and because we’ve registered the Redis Cache service in our Startup.cs file we can inject an instance of the IDistributedCache into our controller class…

Inject IDistributedCache

Check the cache when retrieving data

Finally to use the cache when getting data… we can implement the cache-aside pattern. In this pattern we…

First check the cache for a particular key.
If it does not exist we read data from the DB and store it in the cache.
If it does exist in cache we use the cached version of the data.

Below I’m checking the cache for a list of products…

Example of cache-aside pattern

In this case I’ve not set any DistributedCacheEntryOptions meaning items will not expire. If you need to set absolute or sliding expirations on items you can set the relevant options when adding them to the cache.

Remove items from cache after they’ve been updated

When some data change occurs which means previously cached items are now stale we can remove items from the cache as below…

Invalidate items in cache by removing them

In this case the list of products I previously cached is no longer valid as I’ve deleted one of them.

Depending on the situation we might not have to explicitly remove certain items from the cache like above. In many cases some staleness is acceptable so in this case we might set a sliding expiration of 1 hour or depending on the data just re-cache certain items overnight.

How to test your app is storing items in and reading from Azure Cache for Redis

To confirm everything is working as expected we can run the monitor command in the Redis console direct on the Azure portal.

An exception has been raised that is likely due to a transient failure when accessing an Azure SQL Database with Auto-pause on from Entity Framework Core

When you create an Azure SQL Database with the Serverless (pay per second of usage) pricing model there’s an auto-pause option which will pause the DB after a period of inactivity. This option is intended to save you money. One problem with enabling it is connection failures when activity re-starts again as the DB has to go through a cold start.

When you’re connecting to a paused DB from Entity Framework Core you’ll often see an error like below…

An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding ‘EnableRetryOnFailure()’ to the ‘UseSqlServer’ call.

As suggested by the error we can handle this by including EnableRetryOnFailure in our DbContext configuration as shown below…

EnableRetryOnFailure setting for Entity Framework

When the above options are set and the DB is in a paused state the first request of the app will take longer than normal but the user won’t see an error as the DB will be accessible after one or two connect retries.

Testing EnableRetryOnFailure when Azure SQL DB is paused

Unfortunately at the moment there doesn’t seem to be a way to simulate the paused state for Azure SQL Databases so you’ll need to wait an hour (min auto-pause time) to test. You can confirm the DB is in a paused state from the portal…

Azure SQL DB state

… and after hitting your app if you’re logging debug and info messages you should see some failed connection attempts (first yellow box below) and finally a successful connection and query execution (second yellow box below) in your logs.

The sample log output below (click for a larger view in a new window) is taken from the App Service Log stream page which allows you to view your application logs in real-time direct from the portal.

Viewing app service logs

How to automatically generate DDL change scripts when creating or editing tables in SQL Server Management Studio

If we want SQL Server Management Studio to create a DDL change script for us when we create or change a table we can configure this in the Options -> Designers -> Tables and Database Designers screen as shown below…

When we have the ‘Auto generate change scripts’ option selected above SMSS will popup a window with the DDL change script each time we create or edit a table.

The example below shows the change script shown after I’ve added a new column called Fee to the Courses table…

Create change scripts in SQL Server Management Studio

Unfortunately it seems this functionality only works on tables.