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 |
|
ravl13
Starting Member
38 Posts |
 Posted - 2011-09-09 : 11:22:33
|
| Greetings,I have been creating tables in a database by right clicking "Tables" in the object explorer and picking "New Table...". So I create the columns in the table with the resulting wizard, and save the table.Now, later I realize that I need an "address3" column (I already have 1 and 2). So I go back into Design view for the table and add an "address3" column. It is currently the last column in the table, since I created it after all the others. I am able to save the table.But having "address3" as the last column (column 14) is pretty stupid. I would like to move it next to "address2" (column 5). However, when I click and drag the address3 column next to address2 in design view and try to save, the save fails and I get the following message:“Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.”I don't know where I would find this "Prevent saving changes" setting/option, so I would like to know where I can find that option to see if it is on. But I would also like to know if rearranging the order of columns in a table is simply not possible in SQL Server 2008?I can of course recreate the table I suppose, since there is no data in it, but if the table had contained data in it, it would be a hassle to move the data to another. I'm eventually going to hand this database off to people who are not particularly good with SQL, and would like to create tables that can have their columns rearranged, if that is even possible, so that they do not have to recreate tables or move data if they find out they need another column in a table, and want to change its column order.NOTE: I am admin of the database, so this is not a permissions-related issue.Thanks for your time,-Robert V. |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-09-09 : 11:43:35
|
It shouldn't matter which order the columns are in to you. (as a database admin anyway). Though I agree it's not the prettiest!This ad hoc approach isn't the best way to design a database and I suspect that you are running into these problems because your database is highly denormalised?If you really need to present the information in a specific way then you could write a view over the table.All of your inserts and selects should be using specific column names and not stuff likeSELECT *FROM ....orINSERT fooSELECT .... The setting that is actually preventing you from saving the change is in:"Tools -> Designers -> Prevent saving changes that require table re-creation."But you should be very wary about letting people just reorder the columns. Column order in a db should never matter to end user.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-09 : 12:52:15
|
| People will have differing views no doubt!I like to have columns that relate to one-another adjacent in the table schema. Most tools that I use display the columns in sequence-order, so if picking all the Address columns its handy if they are contiguous in the schema design ...... "Most tools" no longer including setting up Foreign Keys in SSMS Table Designer which has, unilaterally it seems, decided to now show the columns in alphabetical order in the picklist which, given that I always put my PKey columns at the top of the table schema's sequence-order is a PITA ... |
|
|
|
ravl13
Starting Member
38 Posts |
Posted - 2011-09-09 : 13:19:00
|
| Thank you very much Transact Charlie! That setting is useful to turn off right now so I can reorganize tables. I realize it may be risky to leave that setting off, but as Kristen says it can be handy as well. This database I'm working with is eventually just going to be a single table to be freely edited and queried by Management studio. Thanks for your help :) |
|
|
|
|
|
|
|
|
[Solved] Saving changes to a table not permitted