change datatype in a table

Rock Hitman 46

Hi, I want to change dataType from int to varchar for columns MakeID,ModelID.vahicleTypeID
Reason is sometimes we get the data as string.

As this table is already in Production, we just need to modify the datatype of the columns. How easy is this doing without impacting much. Please advise

CREATE TABLE [dbo].[TestData](
[ID] [int] IDENTITY(1,1) NOT NULL,
[MakeID] [int] NOT NULL,
[ModelID] [int] NOT NULL,
[Model] varchar NOT NULL,
[VehicleTypeID] [int] NOT NULL,
[VehicleType] varchar NOT NULL,
[Year] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[UpdatedDate] [datetime] NULL,
CONSTRAINT [PK_TestData] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

scott_morris-ga 16 Reputation points

2020-11-02T20:29:04.943+00:00

It is trivial to do - but the impact is likely to be severe depending on the amount of code that refers to this table. Of course you should attempt this first on a test system in order to evaluate the effect of the change and the affected areas of your existing code base.
Rock Hitman 46 Reputation points

2020-11-02T20:52:03.453+00:00

ofcourse I will first implement on Test system...but how can I modify the query to update the datatype of the columns ?
Please advise
EchoLiu-MSFT 14,581 Reputation points

2020-11-06T02:29:01.813+00:00

Do you have any updates？
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

2 answers

Tom Phillips 17,731 Reputation points

2020-11-02T21:24:13.187+00:00
Please see:

https://video2.skills-academy.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver15

ALTER TABLE [dbo].[TestData] ALTER COLUMN [MakeID] varchar(100) NOT NULL;
Please sign in to rate this answer.

1 person found this answer helpful.
Erland Sommarskog 111.1K Reputation points MVP

2020-11-02T23:03:48.167+00:00

In addition to Tom's post: you will have to drop any indexes on these columns. (You will get an error message if you do not.)

Also, when Scott says that the impact can be severe, means that the table will be locked during the duration of the operation.

Rock Hitman 46 Reputation points

2020-11-02T23:49:41.087+00:00

So I did this : Please experts let me know if you see any issues happening in the future ?

ALTER TABLE [dbo].[TestData]
ALTER COLUMN [MakeID] VARCHAR(100) NOT NULL,
[ModelID] VARCHAR(100) NOT NULL,
[VehicleTypeID] VARCHAR(100) NOT NULL,

(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

Guoxiong 8,206 Reputation points

2020-11-03T02:01:16.81+00:00

ALTER TABLE [dbo].[TestData] ALTER COLUMN [MakeID] VARCHAR(100) NOT NULL; GO ALTER TABLE [dbo].[TestData] ALTER COLUMN [ModelID] VARCHAR(100) NOT NULL; GO ALTER TABLE [dbo].[TestData] ALTER COLUMN [VehicleTypeID] VARCHAR(100) NOT NULL; GO

Erland Sommarskog 111.1K Reputation points MVP

2020-11-03T22:27:56.043+00:00

Yes, I can see an issue: that statement will produce an error message. You can only alter one column at a time. So you need to do it like in Guoxiong's post.

Although, there is a possible improvement. If you are on SQL 2016 or later and you have Enterprise Edition, you can add the clause WITH(ONLINE = ON). This will prevent the table from being blocked during the operation. It can still be painful, though, if the table is big.

ALTER TABLE [dbo].[TestData] ALTER COLUMN [MakeID] VARCHAR(100) NOT NULL WITH(ONLINE = ON; GO ALTER TABLE [dbo].[TestData] ALTER COLUMN [ModelID] VARCHAR(100) NOT NULL WITH(ONLINE = ON; GO ALTER TABLE [dbo].[TestData] ALTER COLUMN [VehicleTypeID] VARCHAR(100) NOT NULL WITH(ONLINE = ON ;

Rock Hitman 46 Reputation points

2020-11-12T07:41:12.723+00:00

Hi Erland, the table got updated fine but I also see somehow the table is also referencing Type internally.
Types ->UserDefined Data Types.

CREATE TYPE [dbo].[TestDataType] AS TABLE( [MakeID] [int] NOT NULL, [Make] [varchar](200) NOT NULL, [ModelID] [int] NOT NULL, [Model] [varchar](200) NOT NULL, [Year] [int] NOT NULL ) GO

After updating the data types in the table, it is still complaining about data type error. On troubleshooting I see this above Type is been called internally.
How can i update the Type (datatype) . Please advise

Erland Sommarskog 111.1K Reputation points MVP

2020-11-12T22:30:04.203+00:00

What statement(s) did you run? What was the exact error message you got?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
EchoLiu-MSFT 14,581 Reputation points

2020-11-03T02:48:44.027+00:00
If you are not sure whether the modification is correct, you can check the final data type with the following code:

select sql_variant_property(columnname,basetype) from tablename

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our [documentation][1] to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--https://video2.skills-academy.com/en-us/answers/questions/142427/users-always-get-connection-timeout-problem-when-u.html
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

change datatype in a table

2 answers

Your answer