MSFT-MSO: Support ALTER TYPE - by Narayan_Iyer

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 319134 Comments
Status Closed Workarounds
Type Bug Repros 23
Opened 12/21/2007 2:05:04 AM
Access Restriction Public


There is no way to ALTER a "type" other drop and recreate? - What if I create a type with VARCHAR(10) and use it in many tables and after that I need to increase the width of the type?

P.S. I did search in BOL for any documentation on this, but no luck. Please let me know if I'm missing something obvious here. 

Sign in to post a comment.
Posted by MgSm88 on 12/22/2016 at 6:53 AM
This feature is sorely needed. Altering types is a huge pain. And now that CREATE OR ALTER is also a thing, supporting that as well would be fantastic.
Posted by Jsamuel on 11/21/2016 at 3:30 PM
We use CREATE TYPE xxx AS TABLE, in order to use the type as a Table-Valued Parameter to Stored Procedures.
In order to support CREATE OR ALTER syntax for our programmability code, we need to be able to support CREATE OR ALTER on user defined table types.
Without this feature, we are stuck having to DROP & CREATE everywhere.
Posted by AmazingAnt on 1/16/2014 at 9:51 AM
Trying to pass a hundreds of records at a time from a .NET application? Sounds like you need Table Valued Parameters!
Need to change the design of the records you're using without dropping everything? Too bad!

I guess I'll go back to long sets of insert statements and temp tables.
Posted by w0nnie on 9/22/2013 at 11:14 PM
and yet more votes still roll in for this.....
Posted by Joshua Russo on 6/18/2013 at 11:23 AM
That and the workaround doesn't work for my scenario because I was adding a column to a user defined table type
Posted by Joshua Russo on 6/18/2013 at 11:20 AM
This in fact completely breaks the SSDT publish automation, unless you just already know that Type objects can't be modified. I'm stuck running a manual drop/create before doing a deploy to each environment.
Posted by Joshua Russo on 6/18/2013 at 10:50 AM
This makes working with SSDT project very obscure when I can change any other object I create, but not Types.
Posted by EEaston on 9/26/2012 at 4:13 PM
Before executing the workaround script, I thought I would run it and spit out the results by selecting from each temp table and changed each EXEC to SELECT. My results were huge, containing a ton of objects than my short list of uddts were not being used in.

Am I doing something wrong? Not understanding the scope? Or is there a way to filter down the list to just those objects impacted?
Posted by atverweij on 7/15/2011 at 1:58 AM
I posted the script that I use to alter an UDDT.
Hope that someone benefits from it.
Posted by colesillaman on 7/14/2011 at 1:12 PM
Alter Type - now that table type paramters have been added to stored procedures... the need for an alter type has gone beyond just udf column types etc.
Hey its 2011 requests have been coming in since 2007 and earlier...

So how about it.. ?? The closed and wont fix.. over a 5 year period.. is a little long.. and negates any arguments of complexity etc.

Posted by Microsoft on 3/10/2011 at 4:56 PM
Hello Narayan,

Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”.

Please consider using tool like Visual Studio or SQL Studio to refactor your types. They will handle the renames including reference inside module definitions.

Umachandar, SQL Programmability Team
Posted by fotis12 on 12/22/2010 at 5:39 AM
WE NEED alter type too, both for simple and for table value types as well
It's killing us having to drop every time we need to modify something every procedure , view, function, table in the chain of dependancies, only for to be able to drop the old and create a new type with the same name but with just a minor modification.
Posted by Eidolon on 2/2/2010 at 8:45 AM
As niyer pointed out, not being able to alter a type really defeats the main purpose of declaring a type, which is to have a consistent, "inheritable" type. You would expect that by creating a type, if in the future your data grows and you need to change the field from an INT to a BIGINT, or from VC(10) to VC(50) you should be able to alter the type and all the tables which use it would be updated automagically.
Posted by Pawel Potasinski on 12/14/2009 at 1:22 AM
If only we could perform drops and recreates within a single transaction... See another item for details:
Posted by Patrick Stitts on 2/14/2008 at 2:30 PM
I suggested this back in SQL 7.0... been waiting ever since for this feature. It would be a very powerful addition for those of us who use user-defined types as an easy way to keep data types consistent throughout the database. I know there will be rules (like can't convert text to int, etc.) but most of the time you end up changing the actual length of a field instead of totally changing the data type.
Posted by Narayan_Iyer on 1/17/2008 at 5:06 AM
This defeats the usage of the 'TYPE' in my code. The main advantage I see of using TYPE is to modify the column width in several objects by modifying it in one place.

As per your workaround, if I have to drop the TYPE, first I need to drop all the objects that are referring to that TYPE. :-(

Anyways, I understand the busy schedule and I will let you take your own call on this.
Posted by Microsoft on 1/17/2008 at 12:50 AM
Thanks for the suggestion and feedback.

We're in the process of seriously triaging customer requests for the Katmai release. Given the time and resource constrain, we might not be able to address all customer DCRs for the release.

Meanwhile, as a workaround, please do stick with the drop and recreate workaround if you indeed need to change the alias type.