Menu
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.
513
You can add a not null column at the time of table creation or you can use it for an existing table. In the above table, we have declared Id as int type that does not take NULL value. If you insert NULL value, you will get an error.
The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.
The basic syntax of an ALTER TABLE command to add a NOT NULL constraint to a column in a table is as follows. ALTER TABLE table_name MODIFY column_name datatype NOT NULL; The basic syntax of ALTER TABLE to ADD UNIQUE CONSTRAINT to a table is as follows.
You must therefore use NOT NULL for all columns that cannot legitimately contain nulls. If you specify that a column is NOT NULL , you are defining a constraint that ensures that that the column can never hold or accept NULL , so you can't accidentally leave the value out.
(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)
109
Ask yourself why adding a column would have a massive impact. Perhaps you have queries that use SELECT *? Find out why the impact would be significant - then consider those to be bugs, and fix them.
Most of the time, adding a column should not break anything. Adding a NOT NULL column will affect anything that does an INSERT, but otherwise, there should be little impact if your database is properly designed.
EDIT after NOT NULL update
The solution is obvious: add the column as NULL, update the data to include non NULL values for every row, then alter the column to be NOT NULL.
41
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
