alter Table Order

Question

alter Table Order

aprasy

Mr or Mrs. 500

Points: 571
More actions
March 5, 2009 at 11:22 pm

#131439

Hi All,
I have a table called employee. These having 4 columns which is
ID, name , address, phone. Now i want to include a column name gender in the table
which i need to place in between the name and address field. I want to do this changes using sql script. Can anyone help me out
Thanks

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

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

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello,

The following thread provides a script solution (and also explanations of why you should normally not need to worry about maintaining column order in a table).

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/f1281533-ad45-4bdc-867c-a9369a02de80/

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

The bigger question would be - why is it so important to have it "in the middle?" The display order, or column seq# has nothing to do with how the data is actually stored. The data gets fetched and ordered based on what the query at hand requests.

So in short - why try to reorder the columns?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

aprasy Mr or Mrs. 500 Points: 571 More actions · Answer 3

Sorry Guys i actually faced this question in the interview that's what i clarified with you.

I too know that, it is not a possible thing. I thought of it's might have answer I might not know. That's what i have asked!!!

Anyways

Thanks

ab5sr SSCarpal Tunnel Points: 4056 More actions · Answer 4

So, tell the guy first off that you can move the data out of the table, bcp, SELECT INTO, or otherwise, create the new table, and then move it back in. There is no supported method to do this. Matter of fact, tell him that if you look at the operation running profiler that sql server does the same thing. After this, then tell him that ordinal positions matter nothing in the database, and moreover, all of the newest versions of ORM (nHibernate, Entity Framework, etc.) care little of column order. It's there only for his visual comfort and edification; SQL Server is not a hierarchical database and data isn't stored sequentially or positionally in a file.

Thanks much,