Changing the data type or size of a column

Last modified: March 28, 2025

When you change the data type or size of the column, the data can be truncated during the deployment.

Solution: To avoid deployment errors and ensure data integrity, you need to create a migration script:

Generate a migration script to update values
Modify the column data type

Note

The comparison engine of the tools doesn’t handle all cases automatically, which is why the cases mentioned above must be resolved manually.

The screenshot displays the Schema Comparison document and the scripts generated by Schema Compare Engine and a migration script, which should be created manually to perform errorless data migration.

Scripts

The table provides source and target table script examples to illustrate the case:

Source table script example Target table script example

Source table script example	Target table script example
`CREATE TABLE [dbo].[Product] ( [ProductNumber] [INT] PRIMARY KEY ,[ProductPrice] [DECIMAL](5, 2) NULL ,[ProductName] [VARCHAR](25) NULL ) GO INSERT INTO [dbo].[Product] ([ProductNumber], [ProductPrice], [ProductName]) VALUES (1125, 234.56, 'screwdriver'); INSERT INTO [dbo].[Product] ([ProductNumber], [ProductPrice], [ProductName]) VALUES (1126, 89.71, 'hummer'); GO`	`CREATE TABLE [dbo].[Product] ( [ProductNumber] [INT] PRIMARY KEY ,[ProductPrice] [VARCHAR](10) NULL ,[ProductName] [VARCHAR](25) NULL ) GO INSERT INTO [dbo].[Product] ([ProductNumber], [ProductPrice], [ProductName]) VALUES (1125, '234,56', 'screwdriver'); INSERT INTO [dbo].[Product] ([ProductNumber], [ProductPrice], [ProductName]) VALUES (1126, '89,71', 'hummer'); GO`

CREATE TABLE [dbo].[Product] (
  [ProductNumber] [INT] PRIMARY KEY
 ,[ProductPrice] [DECIMAL](5, 2) NULL
 ,[ProductName] [VARCHAR](25) NULL
)
GO

INSERT INTO [dbo].[Product] ([ProductNumber], [ProductPrice], [ProductName])
  VALUES (1125, 234.56, 'screwdriver');
INSERT INTO [dbo].[Product] ([ProductNumber], [ProductPrice], [ProductName])
  VALUES (1126, 89.71, 'hummer');
GO

CREATE TABLE [dbo].[Product] (
  [ProductNumber] [INT] PRIMARY KEY
 ,[ProductPrice] [VARCHAR](10) NULL
 ,[ProductName] [VARCHAR](25) NULL
)
GO

INSERT INTO [dbo].[Product] ([ProductNumber], [ProductPrice], [ProductName])
  VALUES (1125, '234,56', 'screwdriver');
INSERT INTO [dbo].[Product] ([ProductNumber], [ProductPrice], [ProductName])
  VALUES (1126, '89,71', 'hummer');
GO

The table provides examples of scripts generated by the Schema Comparison engine and those that should be generated manually for errorless data migration to illustrate the case:

Script generated by the Schema Comparison engine Script that should be generated manually for errorless data migration

Script generated by the Schema Comparison engine	Script that should be generated manually for errorless data migration
`ALTER TABLE [dbo].[Product] ALTER COLUMN [ProductPrice] [DECIMAL](5,2) GO`	`-- migration script: UPDATE [dbo].[Product] SET [Product].[ProductPrice] = REPLACE([ProductPrice],',','.') GO -- migration script. ALTER TABLE [dbo].[Product] ALTER COLUMN [ProductPrice] DECIMAL(5,2); GO`

ALTER TABLE [dbo].[Product]
  ALTER COLUMN [ProductPrice] [DECIMAL](5,2)
GO

-- migration script:

UPDATE [dbo].[Product]
SET [Product].[ProductPrice] =
REPLACE([ProductPrice],',','.')
GO

-- migration script.

ALTER TABLE [dbo].[Product]
  ALTER COLUMN [ProductPrice] DECIMAL(5,2);
GO