Problem in resizing the varchar columns inside a table

_{Friday, June 24, 2016 4:30 AM}

Hi Guys,

I stumbled into problems when I try to reduce the size of some column in the a few table of my database.

I have the following table:-

CREATE TABLE [dbo].[BZR_AUD_CLS_MTA_C](
     [CLS_SHT_NM] [varchar](200) NOT NULL,
     [CLS_FLD_NM] [varchar](200) NOT NULL,
     [CLS_FULL_NM] [varchar](400) NOT NULL,
     :

     :

    [CRT_BY] [varchar](80) NULL,
    [CRT_DT] [datetime2](0) NULL,
    CONSTRAINT [PK_BZRAUDCLSMTAC] PRIMARY KEY CLUSTERED
  (
    [CLS_SHT_NM] ASC,
    [CLS_FLD_NM] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

The Alter Table Statement:-

ALTER TABLE BZR_AUD_CLS_MTA_C ALTER COLUMN CLS_FLD_NM varchar(50)

The Errors:-

Msg 5074, Level 16, State 1, Line 470
The object 'PK_BZRAUDCLSMTAC' is dependent on column 'CLS_FLD_NM'.

Msg 4922, Level 16, State 9, Line 470
ALTER TABLE ALTER COLUMN CLS_FLD_NM failed because one or more objects access this column.

My Questions:-

1) Do I need to drop the indexes, primary key and unique constrainst of the table before I can alter the column width ?

2) How can I resize a bigger size column of the same datatype to a smaller one?

Thanks.

Thank You

_{Friday, June 24, 2016 5:12 AM ✅Answered}

Hi KRGuy,

As described at ALTER TABLE (Transact-SQL), you can change the length, precision, or scale of a column by specifying a new size for the column data type in the ALTER COLUMN clause. If data exists in the column, the new size cannot be smaller than the maximum size of the data. Also, the column cannot be defined in an index, unless the column is a varchar, nvarchar, or varbinary data type and the index is not the result of a PRIMARY KEY constraint.

Here you need to delete the existing PRIMARY KEY constraint and then re-create it.

ALTER TABLE [dbo].[BZR_AUD_CLS_MTA_C] 
    DROP CONSTRAINT [PK_BZRAUDCLSMTAC]; 
GO

ALTER TABLE [dbo].[BZR_AUD_CLS_MTA_C] 
    ALTER COLUMN [CLS_FLD_NM] [varchar](50) NOT NULL
GO

ALTER TABLE [dbo].[BZR_AUD_CLS_MTA_C] 
    ADD CONSTRAINT [PK_BZRAUDCLSMTAC] PRIMARY KEY CLUSTERED ([CLS_SHT_NM], [CLS_FLD_NM]); 
GO

Sam Zha
TechNet Community Support

_{Friday, June 24, 2016 5:35 AM | 1 vote}

Hi KRGuy,

During decreasing the size of a character field let's say from varchar(100) to varchar(50), you may experience data truncation error which will cause data loss for field values with more than 50 characters as expected.

Perhaps you can better check maximum size for target fields and develop your decision how to manage these values

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

_{Friday, June 24, 2016 9:37 AM}

Hi All,

I can suppress the error message by setting SET ANSI_WARNINGS OFF

when I am resizing a bigger column to a smaller size color.

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

SET ANSI_WARNINGS OFF

ALTER TABLE [dbo].[BZR_AUD_CLS_MTA_C]
ALTER COLUMN [CLS_FLD_NM] [varchar](50) NOT NULL

SET ANSI_WARNINGS ON

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Thank You