How does adding a column impact dependent objects? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How does adding a column impact dependent objects?

Author

Topic

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2004-12-15 : 14:21:59

If I alter a table's structure to ADD a column by either executing ALTER TABLE or by dropping and recreating the table, what is the impact to dependent objects such as stored procedures, triggers and views? I'm trying to determine if it is always necessary to drop and recreate the dependent objects. I believe in older version of SQL Server, such as 6.0, it was necessary to drop & recreate, but I'm not sure if I need to do this in SQL 2000. The only reason I can think of is I may wish to produce a new query plan and therefor run sp_recompile, but from a functionality point of view a new column shouldn't break any dependent objects.

What are your thoughts?

Thanks, Dave

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-15 : 14:27:40

Should be ok.
It was a problem if you dropped an object and created a new one with the same id - sometimes the views, sps... wouldn't know anything had changed and get very confused.

I would still advise a restart of the server when you do this but it is nowhere near as important as it used to be.

A sp_recompile on the object and dbcc freeproccache, dbcc drop_clean_buffers is a good idea though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2004-12-15 : 14:50:00

I ran a few tests where I created a table, created a stored proc referencing the table, dropped the table, created a new table unrelated to the first, recreated the original table with a new column and re-ran the stored procedure. Despite the object id changing, the stored procedure picked up the change in the table structure. Also, the new 'unrelated' table did not pick up the previous tables object id. It was assigned a completely different id.

create table dba1 (field1 int, field2 int)
go

create procedure fpsp_1
as
begin
select * from dba1
end
go

exec fpsp_1

select name, id from sysobjects where name like 'dba%'

drop table dba1
go

create table dba2 (field1 varchar(10)) -- See if the ID is reused

create table dba1 (field1 int, field2 int, field3 int null) -- Add col

insert into dba1 values (1,2,3)

select name, id from sysobjects where name like 'dba%'

exec fpsp_1
go

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-15 : 16:49:45

Views may be affected as they create entries in syscolumns when they get created. If you have a view definition with a SELECT * in it then you'd need to recreate the view.

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2004-12-16 : 08:00:28

quote:
Originally posted by DBADave

If I alter a table's structure to ADD a column by either executing ALTER TABLE or by dropping and recreating the table, what is the impact to dependent objects such as stored procedures, triggers and views?

What are your thoughts?

Thanks, Dave

You may have to change the insert/update code in the front end logic...

------------------------
I think, therefore I am

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2004-12-16 : 08:02:12

quote:
Originally posted by robvolk

Views may be affected as they create entries in syscolumns when they get created. If you have a view definition with a SELECT * in it then you'd need to recreate the view.

Not necessary to recreate the view. sp_refreshview should do the job..

------------------------
I think, therefore I am

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2004-12-16 : 08:50:49

quote:
Originally posted by ravilobo

quote:
Originally posted by DBADave

If I alter a table's structure to ADD a column by either executing ALTER TABLE or by dropping and recreating the table, what is the impact to dependent objects such as stored procedures, triggers and views?

What are your thoughts?

Thanks, Dave

I don't believe I would need to change any code. Since I am adding a column and not deleting or changing a datatype all could should not be affected. I believe the internal column ids are automatically updated. If I am not correct please let me know.

Thanks, Dave
You may have to change the insert/update code in the front end logic...

------------------------
I think, therefore I am

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2004-12-16 : 09:59:11

When you add a new column it needs to be considered while inserting the data in future. The respective code need to be changed.

------------------------
I think, therefore I am

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2004-12-16 : 12:54:19

quote:
Originally posted by ravilobo

When you add a new column it needs to be considered while inserting the data in future. The respective code need to be changed.

------------------------
I think, therefore I am

I understand what you're saying. The development area is handling such changes. My main concern is whether or not dependent objects not requiring modification need to be recompiled. From what I've been reading on the Internet and BOL is sounds like dependent objects are recompiled automatically.

Thanks, Dave

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2004-12-17 : 01:07:45

Yes. You are right on that.

------------------------
I think, therefore I am

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources