How to Modify the User Defined Data Type?

  • Hai,

    I Created one userdefined data type called

    City as Varchar(50).

    I Used that data type in 3 tables. Those tables having lots of rows in it.

    Now, I want to change the data type length 50 to 100.

    How can i change it?

     

     

     

     

     

     

     

  • The documentation states that you must drop and recreate the user defined type.  They claim this is due to indexing issues.  So in order to drop the user defined type you must first stop not using it.  So all columns that are using it must be temporarly altered in order to drop it.  After dropping and recreating it then you can alter the columns back to using the user defined type.  The following example shows changing a user defined type from 25 to 50 characters.  It was tested on SQL Server 2005 April CTP.

     

    set

    nocount on

    CREATE

    TYPE [dbo].[city] FROM [varchar](25) NOT NULL

    go

    create

    table dbo.GeeWhizUserDataTypeCity

    (

    MyCity City

    Not Null

    )

    go

    insert

    into dbo.GeeWhizUserDataTypeCity (MyCity)

    values

    (

    '1234567890123456789012345')

    go

    select

    * from dbo.GeeWhizUserDataTypeCity

    go

    alter

    table dbo.GeeWhizUserDataTypeCity

    alter

    column MyCity varchar(25) Not Null

    go

    DROP

    TYPE [dbo].[city]

    CREATE

    TYPE [dbo].[city] FROM [varchar](50) NOT NULL

    go

    alter

    table dbo.GeeWhizUserDataTypeCity

    alter

    column MyCity City Not Null

    go

    update

    dbo.GeeWhizUserDataTypeCity

    set MyCity = '12345678901234567890123456789012345678901234567890'

    go

    select

    * from dbo.GeeWhizUserDataTypeCity

    go

       Rick Phillips

     

  • Wouldn't it be nice if we could just do :

    ALTER TYPE [dbo].[city] FROM [varchar](50) NOT NULL.

    Is it because it's impossible or not recommended to do so?

  • There is no such command to alter a user defined table type.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply