Changing the Column Position of a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Changing the Column Position of a table

Author

Topic

REDDY
Starting Member

43 Posts

Posted - 2003-11-04 : 17:19:30

For some reason I need to change the column positions of a big table with 1 million rows and 128 columns on production server.What is the best way to do this??

I tried with EM it is taking too long time( I guess it is dropping the table and recreating).

IS it advisable to update 'colid' of syscolumns table to accomplish the above task??

Regards
Reddy

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-04 : 17:35:19

NO DO NOT PLAY WITH SYSTABLES!!!!

If you look at what EM is doing, you will find it :

Creates an identical table
Copies all your data in
Dumps all the constraints
Drops and recreates the table
Copies the data back in
Recreates the constraints

SO, you can let EM do it, or write that script yourself.

Two questions :

1. Why do you have 128 columns ?
2. Why does column order matter ? You can order in a select statement.

Both of those things usually point to a design problem.

Damian

REDDY
Starting Member

43 Posts

Posted - 2003-11-04 : 18:16:33

Thank you very much for your suggestion..

Regarding 128 columns on the table ,I have no answer,since this table exists
before I joined this company and Lot of applications are pointing to this table
so at present no plans of denormalising the table,

About the column order,since we have too many columns(128) it is becoming difficult
for us to locate the column names while developing the applications,if they are
grouped together it will be easy for developers to locate them in EM.

Any way I will go ahead with EM

Regards
Reddy

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-11-04 : 18:22:15

Reddy,

Why not create a VIEW?

DavidM

"SQL-3 is an abomination.."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-04 : 18:28:59

REDDY,

EM is doing the steps that Merkin mentioned. That is how you change column positions. So yes it will be slow on large tables. DavidM's solution is best if you can not afford the slowness in production. Just put the columns in the correct order in the view and it'll look like you changed the position. Of course developers would need to call the view and not the table then.

Tara

REDDY
Starting Member

43 Posts

Posted - 2003-11-04 : 18:32:16

David,
How does it solve the purpose??could you please explain??

thanks
Reddy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-04 : 18:34:17

REDDY, here is why:

Here is your table:

CREATE TABLE Table1
(
ColumnA INT NOT NULL,
ColumnB INT NOT NULL,
ColumnC INT NOT NULL,
ColumnD INT NOT NULL,
)

Now let's say that you want ColumnD to come right after ColumnA, then:

CREATE VIEW Table1_view
AS
SELECT ColumnA, ColumnD, ColumnB, ColumnC
FROM Table1

Tara

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-11-04 : 18:35:48

Reddy,

As Tara said, you put the columns in the correct order in the VIEW and reference the view..

There should be no performace impact

DavidM

"SQL-3 is an abomination.."

REDDY
Starting Member

43 Posts

Posted - 2003-11-04 : 18:50:10

Thank you very much David and Tara...

View should solve the probelm.. only disadvantage I could see is to update the view, if there is any column added/modified to the base table.

But that can be taken care very easily.

once again thanks a lot for quick responses.

Regards
Reddy

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-05 : 01:03:36

If the only problem is that the developers have problems understanding the columns then create a data dictionary for them. This can just be a table with the columns and a description and an ordering field which they can query.
Sounds like your system might need something like this anyway.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources