Adding Columns to table in the proper order

Question

Adding Columns to table in the proper order

Karen Rice

SSC Enthusiast

Points: 152
More actions
February 2, 2004 at 11:10 am

#163001

I'm adding columns to a table (using ALTER TABLE - ADD) but I would also like to insert the new columns in the middle of the table. I found that I could use the ORDINAL_POSITION in the INFORMATION_SCHEMA.columns table but this means I have to adjust all columns from the insertion point to the end of the table. Is there any way in insert the column in a particular order/position using the ALTER TABLE or another easier way?

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

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

gljjr SSChampion Points: 10080 More actions · Answer 1

First off, I've never worried about column ordering. If I need to add a column I just add it and never look back. Since all my database access is usually through SPs the column order doesn't matter. I do all my insert statements listing all the columns I want to insert into. I do all selects explicitly listing each column in the order I want.

Now on to your question, without dropping and recreating the table there is not really any safe way to make sure the column order is the way you want it to be. I believe this is what Enterprise Manager will do when you add a column to the middle. I would not mess around with changing the ordinal value in the syscolumns table. That is just asking for trouble.

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

WebPhil Ten Centuries Points: 1303 More actions · Answer 2

Enterprise Manager creates a temporary table, inserts all of the records, drops the original table and then renames the temp table when you add in the middle

It shouldn't make any difference to performance if the fields are in a strange order though, just what you see when you select all fields to view in Enterprise Manager

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Like Gary said, there is actually no real need for inserting a column at a specific position.

However, you should place fixed-length columns first, followed by variable-length ones. Within the variable length ones non nullables before nullables. This should be best for SQL Server's storage engine. For its relational engine it all the same.

Anyway, if you insist on the specific position, use WebPhil's suggestion. In EM you can also do your changes to the table, but instead of saving the changes you can let SQL Server generate a script that you can schedule for off-peak usage.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Yes, it does, but at a cost of 2 bytes overhead, IIRC.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

DCPeterson SSCoach Points: 15266 More actions · Answer 5

The premise of the original post is troubling. If your database has any meaningful ordering of rows or columns, you have made a HUGE blunder.

If, however, you want to order the columns to take advantage (or avoid problems) specific to a given DBMS (in this case SQL Server) then just use EM as suggested.

Even though the end result may be the same, those two cases are not equal. The first points to a fundamental misunderstanding of data management. The second is merely recognizing, and accomodating the reality of the SQL Server storage engine.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/