ALTER TABLE - ADD column
-
I have the table table_x:
col1 INT PRIMARY KEY
col2 VARCHAR
I want to add col3 between col1 and col2. In MySQL it's done with the following query:
ALTER TABLE table_x ADD col3 VARCHAR( 10 ) NOT NULL AFTER col1 ;
In sql server all i can do is to add the column to the end of table. Is there a way to insert a new column in the middle or to move a column to left/right?
-
You can't do that in sql server unless you rebuild the whole table. Anyhow, column order should be chosen at the select... and actually the only time that it matters is when the application presents it. That's probabely why they didn't include that feature in sql server.
-
What puzzels me is that in enteprise manager i can do it.
The reason i need to do it in batch mode is that i want to have the update sql script for the old version db.
-
You can do it through EM, but instead of saving your change, press "Save change script". Then you can use that script later & build upon it if you want.
-
Quoting myself >> "You can't do that in sql server unless you rebuild the whole table."
This is exactly what sqlserver does when you hit save.
-
problem solved
the update script is what i needed.
thanks to all
-
Yes, the table gets rebuilt .... I just meant to show that a script could be generated of the process and run outside of EM.
-
Where the column is in the table creation DDL are located visually really does not matter. The columns for the actual row itself are stored in the following (very over-simplified format):
- page header
- fixed length column map
- fixed length columns (not nullable)
- variable length column map
- variable length columns (all varchar and nullable columns)
So if you want th emost efficient design, use fixed length non-nullable columns and make your key/index columns the first columns in the DDL (an added bonus is you get 'update in place'). If this sounds 'old school', it is !. Some things just do not change - like basic DBMS page formats ( MS/SQL, Sybase,DB2) at least until we hit the next generation of datbases.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Where can I get more info on that rudy??
-
Here's a start:
BOL - Index Tab - Estimating table size and allocating pages
Some of the actual page structure stuff I have to dig for. If I remember it was in v7 BOL the last time I looked.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply