June 8, 2005 at 4:10 am
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?
June 8, 2005 at 9:49 pm
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
June 8, 2005 at 9:57 pm
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?
July 21, 2015 at 4:08 am
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