How to change position of one column in a table?

[vladibo]

How to change position of one column in a table?

Thanks in advance.

 

[Katy44]

Why do you need to do this? If it's for viewing the contents, sort that out in your query. There should be no particular 'order', except they are usually as you created them. What you could do is back up the data, drop the table, and recreate with the fields in the desired order, but there shouldn't really be a need to.

 

[vladibo]

The question is that I add a column to an existing table and there is no way (like I could in MySQL) to add in a specific position, so it appears at the end. Of cource this is not a magor problem but I want to see columns in my table grouped in a logical way. I am surprised that this is such a problem to define column order for a powerful database as MSSQL

 

[cLFlaVA]

There is no way to do this in SQL Server, other than by re-creating the table, and re-populating the table. This is something that has baffled me as a MySQL user.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[TJRTech]

I don't understand why column order is important, unless of course you do a lot of: SELECT *

and, then, yes, I guess it is important.

But, no one does SELECT * in their code, right?

TR

 

[vladibo]

No but I just want to see my table in the diagram looking properly, of cource I won't use SELECT *

Anyway, thanks for the answers

 

[cLFlaVA]

It's never important, even with a SELECT *, because, unless you're viewing database data from the query analyzer, you can reference columns in any order you wish.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[SQLBill]

If you REALLY must do (JUST SAY NO), use Enterprise Manager. It makes it easy, since you don't have to know the steps. I've never done this myself, but I've seen other posts about it. EM will create the new table, copy the data and delete the old table and then rename everything for you.

-SQLBill

 

[vladibo]

Basiclu I can create temporary table copy there the data and recreate old but I thought there should be easier way.

 

[SQLBill]

Again, using Enterprise Manager is easier because it does the work for you and shouldn't make mistakes. (Right click on the table and use the Design option)

Check out some of these:

THREAD183-636773
THREAD183-761730
THREAD183-889392
THREAD183-892873

There were more, but you can get them by doing a search on the phrase "column order".

-SQLBill

 

[vladibo]

Yes I know SQLBill,

But all changes in my company are created with SQL, in order to have cntralized control for the schema changes and I can use other tools.

However thanks for the discussion, I think I see the topic much clearer than before...