Unable to alter user defined data type | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Unable to alter user defined data type

Hi Experts, In SQL Server 2005, I logged in as "sa". I created a user defined data type of type varchar. Now a want to change the size, but unable to edit. I am only able to rename and delete. How can I alter the user defined data type, please advice. Thanks & Regards,
Subhash
I Hope you will not be able to edit the properties of UDT. The only possible way isto drop and re-create it again with changes tat you wanted. Cheers Sugesh kumar R. MCDBA
Thanks Sugesh. But the User defined data type is used in many tables (more than 20). So due to the dependent objects, the User defined data type couldn’t dropped. So the only way I have is to create a new user defined data type and then modify all dependent tables to bind column with new UDT. Then drop the older UDT and finally rename the new UDT by previous name. Am I right?

Books online documented that
quote:
User-defined types cannot be modified after they are created, because changes could invalidate data in the tables or indexes. To modify a type, you must either drop the type and then re-create it, or issue an ALTER ASSEMBLY statement by using the WITH UNCHECKED DATA clause. For more information, see ALTER ASSEMBLY (Transact-SQL).
You can insert or modify values for user-defined type columns by doing the following: Supplying a value in a SQL Server system data type, as long as the user-defined type supports implicit or explicit conversion from that type. The following example shows how to update a value in a column of user-defined type Point by explicitly converting from a string: Copy Code
UPDATE Cities
SET Location = CONVERT(Point, ‘12.3:46.2’)
WHERE Name = ‘Anchorage’
Invoking a method, marked as a mutator, of the user-defined type, to perform the update. The following example invokes a mutator method of type point called SetXY that updates the state of the instance of the type: Copy Code
UPDATE Cities
SET Location.SetXY(23.5, 23.5)
WHERE Name = ‘Anchorage’
FYI Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I think the same case applies to 2000 as well Madhivanan Failing to plan is Planning to fail
Yes ofcourse and you have only Create & Drop in thsi case. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Yes ofcourse and you have only Create & Drop in thsi case. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>