| Author |
Topic |
|
Crespo24
Village Idiot
144 Posts |
 Posted - 2003-01-21 : 09:04:22
|
Hi folks,When I use the ALTER command on SQL-Server 2000 to add a new column. SQL Server always adds the column to the end of the table.So if I have a table consisting of 3 columnsA B Cand I say ALTER TABLE ADD D varchar(100)I get A B C Dnow that is great but instead of getting the new column at the END of the table. I want it to add it on the left hand side instead.So we have something likeD A B CIs there a way in SQL-Server to specifiy where the new column is to be added?Thanks in advance. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-21 : 09:12:34
|
| No, you'd have to specify the column order in the SELECT clause:SELECT D, A, B, C FROM myTableOr create a view with the proper column order, and modify it as necessary when adding columns.DO NOT try to change the values in syscolumns, you will corrupt the table and potentially FUBAR the entire database. |
 |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-01-21 : 09:16:42
|
quote:
I want it to add it on the left hand side instead. So we have something like D A B C
If this is so you can look at it in EM then just use em to move them around.A select statement can also put your columns in that order.Can't think of another way without modifying system tables (bad!)or dropping and recreating the table while storing the data in a temp table. |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
|
|
Crespo24
Village Idiot
144 Posts |
Posted - 2003-01-21 : 09:23:48
|
| Yes.. but the problem is not really in the select statement.Of course I could use a SELECT statement to determine the order of the columns but it is rather cumbersome if I have a table of 20+ columns. I would rather use SELECT *The reason I ask this question is because I have a set of tables which don't have a KEY column so when I ALTER them to add the new column. It is adding it way to the end of the table.Thanks for your and Robs advice though.Appreciated. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-21 : 09:25:09
|
Think of it this way: if TWO people are giving the same advice, then it's probably GOOD advice. (unless one of the two people is me)  And if it is bad advice, well, at least you didn't post it first. You can claim you were just tagging along.  |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-01-21 : 09:28:18
|
| If your queries care about a column's ordinal position in a row, you are doing something wrong.EDIT: if you are using SELECT * in a production query you are dong something wrong, too ... (well, with one exception ... that is if you are nr and you are correlating a subquery, then SELECT * is ok) ... save yourself the future headache and always enumerate the columns you are working with.Jay White{0}Edited by - Page47 on 01/21/2003 09:31:11 |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-21 : 09:31:46
|
| Only thing I can think of where this might matter is bcp for which (and I would advise anyway) you can use a view.In fact you can use a view to change the order of the columns (just rename the table and make a view on it with the old table name).You could also update the colid in syscolumns but I wouldn't advise that.==========================================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. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-21 : 09:37:42
|
quote:
Yes.. but the problem is not really in the select statement. Of course I could use a SELECT statement to determine the order of the columns but it is rather cumbersome if I have a table of 20+ columns. I would rather use SELECT * The reason I ask this question is because I have a set of tables which don't have a KEY column so when I ALTER them to add the new column. It is adding it way to the end of the table.
The problem is that you're confusing structure with presentation. The convenience of using SELECT * is just that, a convenience. As Jay pointed out, don't rely on it in a production environment. Be specific and precise in the columns you need in a SELECT statement. It is much easier to change a SELECT than it is to ALTER a table.Also, you have a far bigger problem not having a key on your table. It is not an idle design decision to be figured out later. If you haven't fully worked out the design of the table yet, it's too early to be concerned with the order of the columns in their presentation. |
|
|
|
Crespo24
Village Idiot
144 Posts |
Posted - 2003-01-21 : 09:40:28
|
quote:
If your queries care about a column's ordinal position in a row, you are doing something wrong.EDIT: if you are using SELECT * in a production query you are dong something wrong, too ... (well, with one exception ... that is if you are nr and you are correlating a subquery, then SELECT * is ok) ... save yourself the future headache and always enumerate the columns you are working with.Jay White{0}Edited by - Page47 on 01/21/2003 09:31:11
No no NO!You should know my by now.. I have a habbit of asking silly questions!It doesn't really matter where the position of the column is to be honest. At the end of the day I can use the column anyway, BUT it is purely for my visual pleasure. Like I said.... when I have a table consisting of 10+ columns, it would be nice to have the MEMBER_KEY column at the begining not the end of the table!I can solve it by recreatig my table, but that is going to involve editing my entire set of packages for this.Now I know.. you will be blaming me for my design methods! SIGH.. I told you I am crap SQLer  |
|
|
|
Crespo24
Village Idiot
144 Posts |
Posted - 2003-01-21 : 09:43:43
|
quote:
quote:
Yes.. but the problem is not really in the select statement. Of course I could use a SELECT statement to determine the order of the columns but it is rather cumbersome if I have a table of 20+ columns. I would rather use SELECT * The reason I ask this question is because I have a set of tables which don't have a KEY column so when I ALTER them to add the new column. It is adding it way to the end of the table.
The problem is that you're confusing structure with presentation. The convenience of using SELECT * is just that, a convenience. As Jay pointed out, don't rely on it in a production environment. Be specific and precise in the columns you need in a SELECT statement. It is much easier to change a SELECT than it is to ALTER a table.Also, you have a far bigger problem not having a key on your table. It is not an idle design decision to be figured out later. If you haven't fully worked out the design of the table yet, it's too early to be concerned with the order of the columns in their presentation.
You should tell this to the Administrators who send me the data!I received a set of data and they used Forename+Surname as a unique identifier etc etc..I guess you're right though.. it is my fault.. should of thought about the member_key column right at the start. Now I have to go through my DTS packages and edit them all over again! FFS!  |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-01-21 : 09:50:20
|
quote:
it would be nice to have the MEMBER_KEY column at the begining not the end of the table!
No. This is not a silly, stupid, little, inconsequential question. This is a HUGE BIG deal. This is very very very important. This is the single biggest misunderstood fact about a table. This is massive. This is why conventional procedural language programmers screw up databases.There is no beginning or end of a table. There is not first row or last row. There is not first column or last column.Codd's 1st rule ... the information rule ... explains that information in a database is represented in one and only one way, specifically, by values in columns within rows in tables.Lose the self-deprecating humor, man ... strive to understand the fundamentals of your profession.Jay White{0} |
|
|
|
Crespo24
Village Idiot
144 Posts |
Posted - 2003-01-21 : 09:57:35
|
quote:
quote:
it would be nice to have the MEMBER_KEY column at the begining not the end of the table!
No. This is not a silly, stupid, little, inconsequential question. This is a HUGE BIG deal. This is very very very important. This is the single biggest misunderstood fact about a table. This is massive. This is why conventional procedural language programmers screw up databases.There is no beginning or end of a table. There is not first row or last row. There is not first column or last column.Codd's 1st rule ... the information rule ... explains that information in a database is represented in one and only one way, specifically, by values in columns within rows in tables.Lose the self-deprecating humor, man ... strive to understand the fundamentals of your profession.Jay White{0}
I think we all suffer from FORGETTING the fundementals of whatever we do, but I guess I don't concentrate enough.Like I said.. it was more of a design issue ( the member_key should have been the first column to add after I found out that they used forename+surname as a unique id in their excel spreadsheet!!!!)I realise that there is no such thing as begining/end etc, and it is determined by how YOU want to display the data on your screen. Hell it is all shit data until you add a few column headings etc.Thanks for your advice though.. I guess I was hoping for some handy little tweak that allows you to change the default column order. |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-01-21 : 10:18:43
|
| changing the column order could mess up INSERT INTO Table VALUES () statements ... since it relizes upon the ORDINAL_POSITION of the column ... use EM to go into the table design ... highlight the column you want to move and then click-n-drag it to the position you want ... i tried it ... it works :) |
|
|
|
Robwhittaker
Yak Posting Veteran
85 Posts |
Posted - 2003-01-21 : 10:28:36
|
| I just had a play with this, and through EM, if you right click on a table and select design table, you can then right click on the top column and select insert column, this then enables you to add a column "at the start" although behind the scenes I suspect its still going on the end.If you then go to QA and do a select * the column you've just added appears at the start. |
|
|
|
Crespo24
Village Idiot
144 Posts |
Posted - 2003-01-21 : 10:48:52
|
quote:
I just had a play with this, and through EM, if you right click on a table and select design table, you can then right click on the top column and select insert column, this then enables you to add a column "at the start" although behind the scenes I suspect its still going on the end.If you then go to QA and do a select * the column you've just added appears at the start.
Hmmmm... yes I also tried that.... but I was hoping for a T-SQL solution.. I really hate using EM to be honest with you.Anyway.. thank you all for your help and apologies for my silly questions. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-01-21 : 11:20:06
|
Hold up, if you really hate using the EM, then what possible matter is it where the column is in the table?  -------Moo. |
|
|
|
Robwhittaker
Yak Posting Veteran
85 Posts |
Posted - 2003-01-21 : 11:33:17
|
| I think it's just to look "nice" or make your tables consitent. I know if I do a select *, an sp_help, or use the object browser in QA, it's nice to see the key as the first column(s), it's where I expect it to be. Obviously it doesn't make a blind bit of difference, but when writing queries and using it to join tables it just savings hunting around for which column you should be using. |
|
|
|
Crespo24
Village Idiot
144 Posts |
Posted - 2003-01-21 : 11:38:08
|
quote:
I think it's just to look "nice" or make your tables consitent. I know if I do a select *, an sp_help, or use the object browser in QA, it's nice to see the key as the first column(s), it's where I expect it to be. Obviously it doesn't make a blind bit of difference, but when writing queries and using it to join tables it just savings hunting around for which column you should be using.
EXACTLY... |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-02-02 : 16:59:29
|
| ahem...HEY EVERYBODY, GIVE CRESPO A BREAK!right, now crespo, what you could do is:select *into #tempfrom CRESPOGOdrop table CRESPOGOselect D, A, B, Cinto CRESPOfrom #tempbecause in the end, computers are only here to make us all feel better when we go home at night, I luv yas orl--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
darinh
Yak Posting Veteran
58 Posts |
Posted - 2003-02-02 : 17:09:07
|
quote:
ahem...HEY EVERYBODY, GIVE CRESPO A BREAK!right, now crespo, what you could do is:select *into #tempfrom CRESPOGOdrop table CRESPOGOselect D, A, B, Cinto CRESPOfrom #tempbecause in the end, computers are only here to make us all feel better when we go home at night, I luv yas orl--I hope that when I die someone will say of me "That guy sure owed me a lot of money"
Of course if you drop a table it might be tricky to select into it. It might be better to truncate it |
|
|
|
Next Page
|
ALTER TABLE command