question

Jonathan Barber avatar image
Jonathan Barber asked

SQL alter column with included columns

Here is the scenario

CREATE TABLE tableA ( id bigint, f_id bigint, column1 varchar(255) NOT NULL CONSTRAINT [DF_test] DEFAULT (''), column2 varchar(100) )

CREATE NONCLUSTERED INDEX [IX_Test] on dbo.tableA ( f_id ASC ) INCLUDE ( column1, column2 )

Ok nice features. Now, lets say I want to alter column1 to be nvarchar(1000)

ALTER TABLE tableA ALTER COLUMN column1 nvarchar(1000) NOT NULL

I get some errors:
Msg 5074, Level 16, State 1, Line 2

The object 'DF_test' is dependent on column 'file_description_txt'.

Msg 5074, Level 16, State 1, Line 2

The index 'IX_test' is dependent on column 'column1'.

Msg 4922, Level 16, State 9, Line 2

ALTER TABLE ALTER COLUMN file_description_txt failed because one or more objects access this column.

Ok, so the way around this is to drop the default constraint and the index because of the included columns:

IF EXISTS(select name from sys.columns where object_id=object_id('tableA') and name='column1' and max_length=255) BEGIN ALTER TABLE tableA DROP CONSTRAINT DF_test

DROP INDEX [IX_test] ON [dbo].[tableA]                     
                    
ALTER TABLE tableA                    
ALTER COLUMN column1 nvarchar(1000) not null                    
                    
                    
CREATE NONCLUSTERED INDEX [IX_test] ON [dbo].[tableA]                     
(                    
	f_id ASC                    
)                    
INCLUDE (                     
       column1,                    
       column2                    
     )                    
ALTER TABLE tableA                    
ADD CONSTRAINT DF_test                    
DEFAULT ('') FOR column1                    

END

I hope this helps someone in the future.

sql-server-2005t-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
RickD avatar image
RickD answered

But this makes sense either way. Imagine wanting to change column1 in the table, then imagine that column1 has 50,000,000 rows, do you really want to wait for an index rebuild and the constraint itself to rebuild? Could be a long wait.

I would say this is more a failsafe feature.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.