Modify Computed Column Data Type Using T-SQL


You need to drop the column first and add the column back with explicit data type CAST/CONVERT.
An example:



ALTER TABLE [dbo].[myTable] 
   DROP COLUMN [fullname];

ALTER TABLE [dbo].[myTable] 
ADD [fullname]  AS (CONVERT(varchar(250)
,case when gid=1 
then isnull([fname],'')+' '+isnull([lname],'')
else [busname] end));

  

 

If your computed column has mixed column data types in its formula, you should align (fix) these columns to appropriate data type. The last option is to use a cast/convert to control the final data type for the computed column.

If you have the computed column in the table already when you want to modify one of the column data type used in the formula, you need to drop the computed column first and recreate it when you finish the data type change for the column involved.
An example:



ALTER TABLE [dbo].[myTable] 
   DROP COLUMN [fullname];

ALTER TABLE [dbo].[myTable] 
  ALTER COLUMN [theOtheColumn] varchar(55) null

ALTER TABLE [dbo].[myTable] 
ADD [fullname]  AS (CONVERT(varchar(250)
,case when gid=1 
then isnull([fname],'')+' '+isnull([lname],'')
else [busname] end));

  

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s