What is the proper approach when we add a new column into an existing table?
For example, I already have columns like, Foo1, Foo2, Bar1, Bar2. Now I want to add a new column called Foo3.
What is standard approach (if that is such thing) when I want to add a column with similar name?
I see 2 choices:
- Create temp table with new structure where new column is next to columns with same name, copy data to new table from existing table and drop existing table and rename temp table. Bit complex process but makes database fields more readable.
- Add new column in end. More simpler operation. But column names may not be clearly understood if they in very end.
To get some reference, we are using Database Projects to source control database changes and have a nicer GUI for app developers to make database changes. And we are using some kind of ORM to interact with database so nobody is querying database using database object names.
Update: I have couple of indexes on some of existing columns. But columns that have similar names (including one that I want to add) are not part of any index.