Home > DeveloperSection > Forums > Altering a SQL Server table without impact
lois waisbrooker
lois waisbrooker

Total Post:70

Points:494
Posted on    May-04-2013 1:55 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 605  View(s)
Rate this:
Hi Everyone!

I have a table with some 30 columns, already used in the application extensively. i.e Select, Insert and Update operations for this table written in many different 

ways(in whatever ways the developers thought they were comfortable) in number of stored procedures and UDFs. I'm now handed with a task to extend the functionality 

for which the table serves and I'm in need to add additional detail to the table(generally can be assumed as an additional column to the table). Adding additional 

column to the table is a massive and inefficient task I don't want to do considering the impact it will cause elsewhere.

Another way i can think of now is creating a new table with foreign key to the main table and maintaining the records in the new table. I'm skeptical of this way too. 

What is the effective way to handle this sort of modifications in the schema of the table?

Using SQL Server 2000 in case it's needed.

Edit:

Unfortuantely, column should not accept NULL values. Missed this crucial info indeed

Impacts i think which can occur due to already implemented poor practices are,

1) "SELECT *" and binding to some datagrid directly to front end. (very very low probable)

2) using Column numbers to fetch from dataset or datatable instead of column names in front end when using "SELECT *"

3) "Insert into" with values given sequentially instead of with column names.

By some way, if i can make the column to accept "NULL" values(by tweaking requirements a bit) any impact due to the above points?

I'm doubtful of analysisng existing code because number of SPs and functions using this table can run into hundreds.

Thanks in advance! 


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    May-04-2013 8:49 AM

Hi Lois!

Build a new table with all the columns you need, call it whatever you want.
Create a view, name it the same as the old table, and have it return all the columns the old table used to.
???
$
(yes, I know that this might be confusing for maintenance because a lot of DBAs use a naming convention for views: V_Viewname. I never got into naming a SQL object after what type of object it is and don't see the benefit of such a convention)


Don't want to miss updates? Please click the below button!

Follow MindStick