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.
| Author |
Topic |
|
ujjaval
Posting Yak Master
108 Posts |
 Posted - 2005-12-27 : 18:33:24
|
| Hi,I have a table which has two columns first_name and last_name. I want to add a column between these two columns as middle_name. I just want to know how can I set this ordinal_position using SQL query at the time of adding this column?thanks in advance.ujjaval |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-12-27 : 19:07:29
|
| Youll have to drop and recreate the table to alter this ordinal position. You can just drag and drop the column in EM, though it will only drop and recreate the table behind the scenes anyways :)Nathan Skerl |
 |
|
|
ujjaval
Posting Yak Master
108 Posts |
Posted - 2005-12-27 : 22:06:45
|
| Hi,thanks for reply. I can add a column at specific ordinal position.What I have done is, I had primary and foreign key constraints on the table. I dropped the constraints. I renamed the table. Then I created a new table with the new schema. I inserted the data from the renamed table to the new table. I added primary and foriegn key constraints back. But when I dropt the primary key constraint first time, it gives me following message:The clustered index has been dropped.Index (ID = 9) is being rebuilt.Index (ID = 10) is being rebuilt.Index (ID = 11) is being rebuilt.Index (ID = 12) is being rebuilt.Index (ID = 13) is being rebuilt.What does this message mean? Does this harm the functionality or interaction with database through front-end anyway??Also, I have stored procedures for the table. Do I have to recompile those stored procedures again?thanks,Ujjaval. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-12-27 : 22:44:21
|
| >>Does this harm the functionality or interaction with database through front-end anyway??noWhen a table has a clustered index, the key of that index is stored with nonclustered indexes as a "row pointer". When you dropped the clustered index you "pulled the rug out from under" the other indexes so sql server dropped and re-created them with a new "row pointer". When dropping and recreating indexes you should drop all nonclustered first, then drop the clustered, then create the clustered and finally create the nonclustered.It's a lot of trouble to satisfy your sense of order. The column order really doesn't matter. You can always SELECT the columns in whatever order you want when you display the data. You can also create a view using whatever column order you want and leave the table as is.Be One with the OptimizerTG |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-12-28 : 01:28:54
|
| There is no point in having a column in any particular position in a table. When selecting them use that column in the position that you want to have. Use View or Stored Procedure. Also in your Front End application, dont access the columns by their ordinal position like rs(0),rs(1),etc instead use Column name rs("col") so that you will be at safer side if the column is added at any position in a tableMadhivananFailing to plan is Planning to fail |
|
|
|
yonabout
Posting Yak Master
112 Posts |
Posted - 2005-12-28 : 06:55:09
|
| >>Also, I have stored procedures for the table. Do I have to recompile those stored procedures again?No, As long as the names of the tables and columns the stored procedures are referencing have not changed there's no need to recompile the procedure.Cheers,Yonabout |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-28 : 10:47:50
|
| What Maddy said: Avoid making applications reliant on the position of columns within the table.having said that, I too would want the "middle name" column to be between the "first name" and the "last name" - rather than right at the end of the column list!Also, just adding a column to an existing table (i.e. without rename / create new / copy data over / drop original) apparently leads to inefficient space utilisation within the table, so its not a bad idea anyway.I do this in Enterprise Manager's "Design Table" GUI tool, but instead of letting it make the change I use the button to "generate change script" so that I have run the script myself [and store the script with a sequential filename so that I then have a complete set to store for Change Control and to run on QA / Production servers etc.Kristen |
|
|
|
|
|
|
|
|
adding a column at specific ordinal position