Press "Enter" to skip to content

Altering User-Defined Table Types

Published 2020-10-20 by Kevin Feasel

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

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. 

This is a clever solution. Prior to it, my workflow was:

  1. Create a new user-defined table type
  2. Create new stored procedures which reference the new user-defined table type
  3. Alter and deploy code to call these new stored procedures
  4. Drop the old procedure and user-defined table type

If the changes are such that they don’t require immediate app changes to use (for example, adding a nullable column to the UDTT), this can save a lot of effort.

Published in T-SQL