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.
[…] 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
That was very useful, thanks!
In my case, I needed to alter length of a varchar datatype for a column within the table type; so I used the following construct to check whether that had already been done or not:
[Presumably the parameter values in angle brackets got stripped as non-html by my wordpress site. I’ve restored those parameter names to what I’m guessing they were -Michael]
Comment by kavil subramanian — January 5, 2021 @ 10:50 am
re-sumbitting the comment; it did not like text that I had put in within angle brackets
That was very useful, thanks!
In my case, I needed to alter length of a varchar datatype for a column within the table type; so I used the following construct to check whether that had already been done or not:
IF EXISTS (
SELECT * FROM sys.table_types AS tt
JOIN sys.all_columns AS cols ON cols.object_id = tt.type_table_object_id
JOIN sys.types AS typ ON (typ.user_type_id = cols.system_type_id and typ.user_type_id = typ.system_type_id)
WHERE tt.name = ‘my table type name’
AND SCHEMA_NAME(tt.schema_id) = ‘my schema name’
AND cols.name = ”my col name’
AND typ.name = ‘old col type name’
AND cols.max_length = ‘old col length’)
BEGIN
… make the change
END
Comment by kavil subramanian — January 5, 2021 @ 10:53 am
Thanks Kavil.
I’m glad you found this post useful. Folks that can take what I post and then adapt what I’ve written for their situations is exactly the kind of thing that I like to see. It’s great to hear you’re using this post this way. Thanks!
Comment by Michael J. Swart — January 5, 2021 @ 10:56 am