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
This is a clever solution. Prior to it, my workflow was:
- Create a new user-defined table type
- Create new stored procedures which reference the new user-defined table type
- Alter and deploy code to call these new stored procedures
- 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.