add new column to a table

Question

Post reply

add new column to a table

sqlfriend

SSC Guru

Points: 52781
More actions
September 8, 2011 at 5:58 pm

#257137

I have a critical database. And now I would like to add a column to the table, is it good to add as the last column of the table, or insert into between other columns by logic meaning.
I would like to insert the column to a place, but afraid it will break the code/procedures.
I will certainly add the column to procedures but think if add it as last column it may has less affect,- in case I miss some procedures , is that right?
In our database, we use select specific columns but not *.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

hunt Right there with Babe Points: 780 More actions · Answer 1

In such case there wont be much issues( if select * is not used any where).

If you are to keep the new column in between the existing columns , hope you need to rebuild the table.

If the new column is oging to have NULL values for the existing rows in the table and the column of variable type better to add at the end for space consumption.

thanks
sarat 🙂
Curious about SQL

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 2

sqlfriends (9/8/2011)
I have a critical database. And now I would like to add a column to the table, is it good to add as the last column of the table, or insert into between other columns by logic meaning.
I would like to insert the column to a place, but afraid it will break the code/procedures.
I will certainly add the column to procedures but think if add it as last column it may has less affect,- in case I miss some procedures , is that right?
In our database, we use select specific columns but not *.

Isn't there a place where you could test this first to see if any app code breaks?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

sqlfriends (9/8/2011)
I have a critical database. And now I would like to add a column to the table, is it good to add as the last column of the table, or insert into between other columns by logic meaning.
I would like to insert the column to a place, but afraid it will break the code/procedures.
I will certainly add the column to procedures but think if add it as last column it may has less affect,- in case I miss some procedures , is that right?
In our database, we use select specific columns but not *.

If you don't use * it should not be a big deal.

However, SQL Server does not let you add a column in a specific position: it always adds new columns at the end.

SSMS allows you to add the column wherever you want, but it drops/recreates the table behind the scenes. It could take ages.

In a relational database the order of the columns has no meaning. Don't complicate things just to add the column in a specific position: you can always create a view to see the column in the desired position when you issue SELECT * (I guess this is the only reason why you don't want to add it as last).

-- Gianluca Sartori

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

sqlfriends (9/8/2011)
I have a critical database. And now I would like to add a column to the table, is it good to add as the last column of the table, or insert into between other columns by logic meaning.

The only way to add a column somewhere other than at the end is to create a new table with the desired structure, copy all the data over, drop drop the old table then recreate all the indexes and constraints.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

GilaMonster (9/9/2011)
sqlfriends (9/8/2011)
I have a critical database. And now I would like to add a column to the table, is it good to add as the last column of the table, or insert into between other columns by logic meaning.
The only way to add a column somewhere other than at the end is to create a new table with the desired structure, copy all the data over, drop drop the old table then recreate all the indexes and constraints.

Not worth the effort, IMHO. I would leave it at the end.

Another way could be add the column at the end, rename the table and create a view with the original table name and columns re-ordered. Could break scripts that assume that the object is a table (TRUNCATE, REINDEX, queries on metadata and the like).

-- Gianluca Sartori