Addition of a Column in the Middle of a Table

[davism]

All,

Other than logical grouping - what is the value of inserting a column in the middle of a table?

For instance:

Col1: FirstName
Col2: LastName
Col3: DOB
Col4: SSN

If I wanted to add a column for MiddleName, the logical grouping to have the FirstName, MiddleName and LastName would be there if I put it in between Col1 and Col2.

But other an logical grouping is there any other benefit? What is the difference if I just add Col5: MiddleName?

Any information would be greatly valued.

Thanks

 

[starsky51]

The 'physical' order of the columns in a table won't make any difference to performance. The only instance I can think of where this could cause a problem is if you have an existing query like this

SELECT * FROM tbl1 ORDER BY 1, 2

which references the columns by location, but this is really bad practice.

It makes sense to put it in between FirstName and LastName.

 

[Qik3Coder]

To chain on what starsky51 said:
Without dropping the table you can't reorder them, and you shouldn't care.

Another area where you have to be careful of is, if you have a nested table function and 1 uses a * and another doesn't. Table functions save in some weird format where they are positional and not named.

If you have an enclosing view that you add MiddleName to the middle of, and then it is called by a table function you can get some WEIRD behavior (That takes a loooong time to debug).

Lodlaiden


You've got questions and source code. We want both!

 

[davism]

Thanks for the information guys! An impact analysis is in order there.

Any idea on what is a good tool to view dependencies across data bases on the same server because doesn't a view dependencies constrain it to only the dependencies in that one database?

 

[SQLBill]

To add another view...adding a column in the middle can cause a performance hit while the work is being done. SQL Server doesn't just add the new column in between the rest. Instead it creates a new table with the columns in the order you want, copies all the data to the new table, drops the original table, copies the new table into a table with the original name, then drops the temporary table. Whew! (I think I've gotten that right). It does all that 'behind the scenes', but with large tables it can impact performance.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Qik3Coder]

Just add the column to the end.
Don't try to make it look you thought of everything up front.

Change the order in your presentation layer, which is the safest place, because it's the final consumer.

Lodlaiden

You've got questions and source code. We want both!

 

[davism]

SQLBill, yeah the use to the TEMPDB is concerning if we put it in a logical grouping. If we did that, I would probably do a:

BCP to create a format file delimited with something
BCP OUT the table to a file using the format file.
TRUNCATE the table
Modify the format file to include the new column
Import to excel and add the colmn or write a prg depending on size
BCP IN the file created

However, if it's added at end then we don't have to do that but we lose the logical grouping and if anything else. It's just a matter of if the "if anything else" is compelling enough.

Then we have an impact analysis to see what is all using it.

 

[JoeAtWork]

Then we have an impact analysis to see what is all using it.

Sounds like a waste of time, if you ask me. Why don't you just add the field to the end and have done with it?

You've already been told several bad things that can happen if you insert in the middle. Compare this to what "good" would come from it - the answer is nothing. Your "logical grouping" argument does not make sense. Your users do not open data directly do they? They use some sort of interface, right? And that interface displays the columns in some logical order, right? E.g. you draw a new textbox on a form or web page that has the MiddleName data put in it, you make a report that orders the columns FirstName, MiddleName, LastName, etc. Who cares if in the database the field is actually at the end - nobody ever sees that?

 

[davism]

JoeAtWork, et al.

Yeah, I was just listing the pro's (that I potentially know of)and any con's.

Obviously, the users were going to use the table. However, it wouldn't be exposed to them for the logical grouping aspect or any other reason except through an interface or library use. Any other use of the data by a user where the schema may be provided would be an OLAP and not an OLTP.

JoeAtWork, I don't know why you think that it wouldn't make sense especially from an IT or developer/engineer prospective. Logical grouping is good for that as well for an off-the-cuff type situation for quick understanding or recognition of what columns can be used. Example: if a table is being created from scratch, one would put firstname, middlename and lastname together. It makes sense and is logical grouping. Now, if that doesn't happen then that would not make sense.

But we're talking about adding it AFTER the table has already been create and the naming aspects were just an example.

To all that provided input, based on any responses on this I have not seen anything compelling enough to warrant the amount of change for the logical grouping. A view or a change in the SELECT of an SP will justify. There is, just as in the logical grouping aspect, an impact analysis of change that needs to be done regardless not only on the DB artifacts and dependencies but also the code.

All-in-all, fun stuff.

Thanks again to everybody in providing the information.

Greatly appreciated.

 

[davism]

One other question...

Will reducing the impact of a INSERT some other table with a SELECT * be impacted if that field is made to allow NULLs?

Or what are some of the impacts of using a nullable field? Should that even be done?

 

[gmmastros]

If you make the column not-nullable, there certainly will be an impact on other aspects of your code. Your current code would not accommodate the new column so the row would be inserted without regard to the new column and would therefore be null, which would violate the not-null constraint.

On the other hand, if you allow the column to be null and you have code that spits out all the data, your front-end code will need to be able to accommodate nulls.

Your best approach may be:

1. alter the table to add the new column and set it to allow nulls with a default value of <empty string>.
2. Update the data in the table to set the value of the new column to an empty string [Update table set NewColumn = ''].
3. Add a not-null constraint to the new column.

Now, when you have an insert that does not include the new column, the default will take over and set the value to empty string. Any code you change to include the new column will obviously insert data (or empty string) to the column. Any selects you make on the new column will return data or an empty string, but never null.

It's entirely possible that I may have missed a situation, but this should take care of most (if not all) circumstances.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

Thanks for the thoughts on that. I was going to set it to null like some of the other fields that are already there. To be consistent with that.