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.

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.

Should I use SQL Server identity columns for static lookup tables?

Most systems have static data lookup tables. These are tables that are not inserted into from the GUI or any programmatic process and usually contain a master list of ‘kinds’ or ‘types’.

These ‘types’ mean something to the business but because they either never change, change infrequently or require logic to be written against them there is no GUI to edit them. Quite often records in these tables get displayed in dropdown lists we see onscreen.

Two simple examples…

Example lookup tables

… so should we use auto-increment identity IDs in these tables or normal non identity IDs? Others may disagree but I recommend NOT using identity columns with these tables.

Why?

Identity columns are not needed

Identity columns are auto-increment columns which are most useful when we are inserting new records programmatically and need automatically generated IDs which haven’t been used previously.

For static lookup tables we have all the items ahead of time so can assign arbitrary IDs to them. We don’t need auto-increment IDs as the lookup records are inserted as part of the development and deployment process not programmatically.

Identity columns can lead to bugs

The contents of static lookup tables are often programmed against in our code. To use the membershipType table above as an example we might have logic that shows extra functionality if a members membershipType is premium…

Modelling enums

We’ve used an identity column for the membershipType ID column and no problems so far…. on DEV and UAT everything works. We push to PROD and since the premium membership is a new feature for this release we need to insert an entry for it into the MembershipType table.

The developer, unaware of the potential for mishap and knowing the IDs are automatically generated by the identity column scripts the insert without reference to an explicit ID.. eg…

Insert into identity column

The insert works fine, no complaints from SQL Server Management Studio..

…BUT…

as there was a couple of membership types previously inserted and deleted on the PROD table the premium option now gets an ID of 10. Remember identity IDs are not guaranteed to be contiguous but a lot of developers won’t know this.

Our logic expects that premium membership is ID 3 so we now have a bug which means our premium members won’t get to see the extra stuff.

I see the above kind of thing A LOT and although it’s not the fact we’re using identity but rather how we scripted the insert that caused the issue we could have avoided it in the first place by not using identity. For a static lookup table whose IDs we code logic against we don’t gain anything from using identity IDs and they actually require extra effort to use (as we have to include SET IDENTITY_INSERT).

What about static lookup tables which we don’t code logic against?

If we have a table which is not coded against, the case against identity is less strong as we don’t NEED to align IDs across environments. Even though we don’t need it aligning IDs gives us a few advantages…

We can write logic against the lookup type much easier in the future without having to rebuild our environments.

We can delete a lookup item by ID across environments.

We can select FK records by ID across environments.

If we decide that we’re going to align IDs then using identity is just extra work as we need to set IDENTITY_INSERT on/off each time we add records. If we really don’t care about ID alignment the one advantage of using identity is easier inserts as we don’t have to include the ID column.

Summary

In summary… if you don’t need identity IDs I wouldn’t use them and in particular if you need to attach any sort of meaning to IDs it’s best IMHO to stick with explicitly chosen IDs.