Altering column width | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Altering column width

Hi i want to alter column width of one column in a table. Does it make any impact on current running activities? eg: varchar(2) to varchar(5) for column emp_name.Please advice

You will not be able not alter if that column is A column with a text, image, ntext, or timestamp data type.
The ROWGUIDCOL for the table. A computed column or used in a computed column. A replicated column. Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or larger than the old size. Used in statistics generated by the CREATE STATISTICS statement. First remove the statistics using the DROP STATISTICS statement. Statistics automatically generated by the query optimizer are automatically dropped by ALTER COLUMN. Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint. Used in a CHECK or UNIQUE constraint, except that altering the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed. Associated with a default, except that changing the length, precision, or scale of a column is allowed if the data type is not changed. Other than this I can’t see any other issues

What is the record volume on this table?
Make sure to update statistics on this table in order to update the information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
To minimize the impact on runnin activities don’t use EM!!! Use Query Analyzer instead
set nocount on
create table #test
(
c1 varchar(2)
)
insert into #test values(‘ab’)
insert into #test values(‘abcde’)
alter table #test
alter column c1 varchar(5)
go
insert into #test values(‘abcde’)
select * from #test
drop table #test
set nocount off Server: Msg 8152, Level 16, State 9, Line 7
String or binary data would be truncated.
The statement has been terminated.
c1
—–
ab
abcde I would also do this during off-peak hours. —
Frank
SQL Server MVP
http://www.insidesql.de

Sathya, this table has lakhs of records. thts y i asked any impact on current data or table. There is only one procedure which insert data to this table .no other appli or procedure accessing it.
This is the query i am supposed to run
ALTER TABLE useractivity ALTER COLUMN [program_name] [varchar] (80) NOT NULL currently its [program_name] [varchar] (30) only. Please advice. What you told me abt update statistics??
Follow as suggested by Frank to use QA instead of EM. In order to update the change of the schema UPDATE STATISTICS is essential, other wise you may get performance degradation due to this change. Its better to avoid such hassle before in hand. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>