Michael J. Swart

October 19, 2020

How to Alter User Defined Table Types (Mostly) Online

Filed under: Miscelleaneous SQL,SQL Scripts,Technical Articles — Michael J. Swart @ 2:41 pm

Last year, Aaron Bertrand tackled the question, How To Alter User Defined Table Types. Aaron points out that “There is no ALTER TYPE, and you can’t drop and re-create a type that is in use”. Aaron’s suggestion was to create a new type and then update all procedure to use the new type.

I think I’ve got a bit of improvement based on sp_rename and sp_refreshmodule. Something that works well with

  • blue-green deployments,
  • both ad-hoc queries and procedures,
  • imperfectly understood schemas, like schemas that may have suffered from a little bit of schema drift.

Example

Say I have… I don’t know, let’s pick an example out of thin air. Say I have a simple table type containing one BIGINT column that I want to make memory optimized:

What I’ve Got

CREATE TYPE dbo.BigIntSet 
  AS TABLE ( 
    Value BIGINT NOT NULL INDEX IX_BigIntSet );

What I Want

CREATE TYPE dbo.BigIntSet 
  AS TABLE ( 
    Value BIGINT NOT NULL INDEX IX_BigIntSet )
  WITH (MEMORY_OPTIMIZED=ON);

I can’t directly ALTER this table type, but I can do this three-card monte trick using sp_rename to put the BigIntSet in its place.

The Migration Script

IF NOT EXISTS (
  SELECT * 
  FROM sys.table_types
  WHERE name = 'BigIntSet' 
  AND is_memory_optimized = 1
)
BEGIN
 
  CREATE TYPE dbo.BigIntSet_MO 
    AS TABLE ( 
      Value bigint NOT NULL 
      INDEX IX_BigIntSet ) 
    WITH (MEMORY_OPTIMIZED=ON);
 
  -- the switcheroo!
  EXEC sp_rename 'dbo.BigIntSet', 'zz_BigIntSet';
  EXEC sp_rename 'dbo.BigIntSet_MO', 'BigIntSet';
 
  --refresh modules
  DECLARE @Refreshmodulescripts TABLE (script nvarchar(max));
 
  INSERT @Refreshmodulescripts (script)
  SELECT 'EXEC sp_refreshsqlmodule ''' + QUOTENAME(referencing_schema_name) + '.' + QUOTENAME(referencing_entity_name) + ''';'
  FROM sys.dm_sql_referencing_entities('dbo.BigIntSet', 'TYPE');
 
  DECLARE @SQL NVARCHAR(MAX) = N'';
  SELECT @SQL = @SQL + script FROM @Refreshmodulescripts;
 
  EXEC sp_executesql @SQL;
 
END

But Is This Online?

Mostly. All queries that are in progress (whether ad-hoc or via procedures), continue to execute with no issues. However, there may be an issue with other queries that begin their execution during this migration.

If someone sends a query that uses the table type in the split second between the two sp_rename statements, then the query may fail.

If someone executes a procedure in the time between the first sp_rename and when sp_executesql gets around to refreshing that procedure, then the procedure may fail.

In practice, even on a busy server, I saw no such errors the few times I’ve tried this method, but of course, that’s no guarantee. In my case, even when refreshing close to 300 modules, this script takes about one second with no issues.

I actually tried adding a transaction around this whole migration script, and I did in fact see issues on a busy server. The schema modification lock that needs to be taken and held on all 300 objects was too much. It caused excessive blocking and I had to abandon that approach. In practice, I avoided trouble by ditching the explicit transaction.

1 Comment »

  1. […] Michael J. Swart has a clever solution to the inability to alter user-defined table types: […]

    Pingback by Altering User-Defined Table Types – Curated SQL — October 20, 2020 @ 8:10 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress