| Author |
Topic |
|
Rauken
Posting Yak Master
108 Posts |
 Posted - 2005-05-25 : 04:45:30
|
When I use ALTER TABLE Invoice ADD Desc VARCHAR(500) NULL the field ends up last in the column definition. Is it possible to place it where I want?I have 20 columns and I want to insert this column as number 15. The reason is that my application is generating the gui so the columns must be in a specific order. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-05-25 : 04:55:19
|
| The order of the column at the table does not matter. In Select Statement select the columns in the order that you wantOtherwise use Enterprise Manager to add columnMadhivananFailing to plan is Planning to fail |
 |
|
|
Rauken
Posting Yak Master
108 Posts |
Posted - 2005-05-25 : 05:08:49
|
| Unfortunately in my case it does matter. It's a .NET app, using an O/R mapper for creating the datalayer. The gui is autogenerated using Reflection in .NET. I guess I have to do it manually in Enterprise manager. |
|
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2005-05-25 : 05:49:22
|
Not necessarilyBy adding a column anywhere apart from the end will rebuild the entire table etc. not very good if your table contains millions of recordsWhy not add the column to the end of the table & then create a simple view based on that table in the column order you require and point your app at the view rather than the base tableAndyBeauty is in the eyes of the beerholder  |
|
|
|
Rauken
Posting Yak Master
108 Posts |
Posted - 2005-05-25 : 07:27:16
|
| I know this solution is horrible. The great architect of the project came up with this solution and now we are really stuck when trying to change the layout. Of course the architect has already left the project and moved on :-) It's a little bit complicated to describe how it's built but autogenerating the gui is a really bad thing to do, that's for sure.Yes adding a view would be nice but does the homebuilt o/r mapper support views or stored procedures? No.. :-) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-05-25 : 07:39:47
|
Might want to point out to the higher-ups that the O/R mapper wasn't such a great idea after all.  Might also want to turn on Profiler while it runs and watch some of the things it generates too.Not to mention that the login(s) used by the app are probably sysadmin or dbo and if you wanted to you could drop the entire database from within your app. |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-05-25 : 09:41:44
|
| If it is autogenerating, then it should not matter what order the columns are in. Your "architect" is a sham.*need more coffee* |
|
|
|
chetankelkar
Starting Member
5 Posts |
Posted - 2005-05-26 : 02:01:41
|
| Hi,I am not sure how this works with SQL Server but with Mysql it is possible to determine where exactly we want to add the new column / field. The following is the syntax when it comes to MySql : -ALTER TABLE `dbname`.`tablename` ADD `newfieldname` INT(11) NOT NULL AFTER `fieldnameyouwant`;See the AFTER part -- you can determine where exactly to place the field.Although this is not a good idea - Once your database is designed it doesnt make sense to me to go back and add fields. In cases where such a need is really required, I would rather define another table and store data there and join it when wanted.I am not sure how this works with SQL Server 2000. I would love ot understand if there is something for SQL Server.Brgds,ChetanChetan Kelkar |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-05-26 : 02:30:39
|
| same suggestion as andy,create the view and refer your app to that view, it's the same select mechanism, you don't have to worry about the order of columns in your base table since you can re-create the view whenever necessaryor create an sp and call the sp instead of doing sql injections--------------------keeping it simple... |
|
|
|
Rauken
Posting Yak Master
108 Posts |
Posted - 2005-05-26 : 05:10:57
|
| Using a O/R mapper is nice. You don't have to write database code for typical CRUD functions (Create, Read, Update, Delete). Just click on the button in the o/r mapper and it creates the datalayer for you. We have come to the conclusion to get rid of the existing GUI (Win) and re-create it in asp.net and not autogenerating it, we will use an o/r mapper and one who supports sp's and views. We will decide on a name standard for the control on the form, for example txt_Invoice_Address. When saving we send the form to a class which will run through all the controls txt means string, next is table name and finally column name, then do the CRUD stuff. This gives us the flexibility to design our gui still using common CRUD functionality. We are still in planning phase but it will be fun to do this right :-) The is a sql site and people here might just work with sql server. I've been fortunate to learn database design, sql early in my programming career. I've noticed that many of my programmers colleagues really hate databases (even our guru's). Strange databases, that are so much fun!! ;-) |
|
|
|
|
Add column