New columns or new table alltogether?

I would love to help you with this issue, but the description of the problem is not clear. do you want to add rows or columns? rows are horizontal, columns are vertical. you start the description saying you need new columns, and then at the end it says create new table with all the extra "rows" ....

Thank you for your response but unfortunately I the question I have a mistake. In the final line where I say "create a new table with all the extra rows needed" I meant to say columns. You see the problem we have is that we have to insert new columns (fields in the table) and so far we are doing this manualy through our program. The answer I need is in terms of efficiency. What would be faster.
Thank you very much

Hi friend,

Pls make it very sure that you don't keep changing your database structure every now and then. This includes adding or removal of columns and changing the field sizes, etc.

And, why do u need to create a new table, add the columns into it and then transfer data. If you could add the column directly to the existing table then that's the best.

Problem with new table is -
If original table is t1, then you would create a temptable tTemp. Add cols to tTemp. Copy t1 to tTemp.

Now you have to drop t1, so that tTemp could be converted to t1. Just look at the no. of actions you would need to take. And, what happens if some user (if many people are there) is trying to get data from the database after you have dropped t1.

Dropping a table means that all the links and dependencies that u had created on the table would be lost. This applies to any kind of newer database.

So, its better to alter the existing one itself.

Regards,
Nise

Dear Friend, Altering the same table will be more efficient rather than creating the temp table on your own.
Actually SQL Server performs the same tasks on its own when you a add a new column to a existing table. That is it creates a temporary table with the new data structure and shifts data. But it SQL Server does this task more efficiently for you. And above all while altering the structure, please see that no users are connected through various other applications and accessing the data in the table you want to Alter.
Regards,

Prashant Bajaj

You should consider a third option: create an additional table with only the primary key of the existing table and the new rows. That way, the old table is never touched, and you can easily create a new view wich joins the old table with the new columns.

Additionally, you save some space and runtime speed if most records in the old table would get a default value, or a null for the new column, whereas with a seperate table, only records with meaningfull values for the new columns will be present in the new table.

Greetings, Geert.

This question was auto closed due to inactivity