0

Consider this inital table I have created in Oracle 10G:

╔═════════════════════════════════╗
║  CUSTOMER_ID ACC_NO ACC_BALANCE ║
╠═════════════════════════════════╣
║         100    200        1000  ║
║         101    150        4000  ║
║         102    350        2000  ║
║         103    450        2500  ║
║         104    550        2200  ║
╚═════════════════════════════════╝

Now I want to add another column customer_name into the table. I used:

ALTER TABLE BANK_ACCOUNT 
  ADD (CUSTOMER_NAME VARCHAR2(30));

and the column is being inserted as the last column in the table whereas I want the column to be added to the table as the second column. Now the SQL code I mentioned is unable to do so. So how can I add the column at a specified position? Is it even possible in SQL?

SQB
  • 3,926
  • 2
  • 28
  • 49
Mistu4u
  • 5,132
  • 15
  • 53
  • 91
  • @Meistro: Albeit I know nothing about `SQL Server`, just so that you know, I am working in Oracle 10g. – Mistu4u Sep 15 '13 at 05:22

1 Answers1

1

It really doesn't matter where the column is physically since select will allow you to specify the order (logically) in which they're retrieved.

And, if you're using select * which doesn't let you specify the order, you probably shouldn't be. There are precious few situations where you should be doing that (DB analysis tools, for example), most of the time it's better to select the individual columns you want, even if you want them all. This allows you to catch schema changes quickly so you can adapt your programs to them.

In any case, SQL itself makes no guarantees about the order in which columns are returned unless you explicitly list them. select * may give them to you in ordinal order today and alphabetic order tomorrow. Even if a particular implementation allows you to do it (by creating a new table with the column in the "right" place and copying the data across, or providing an SQL extension like alter table T insert column C1 before C2), I'd advise against it. It won't be portable to other implementations and I prefer to have my code as portable as practicable.

In addition, beyond what you can specify in SQL, a DBMS should be able to totally control how it stores data. It may be that your primary key is a composite of the seventh and forty-second column and it may be more efficient to have them at the front of the physical records.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • Yes, indeed I thought about that way around the problem. But to me, it seems like instead of looking into the problem and mending it, we are like trying to avoid it by going around it, I mean, wouldn't it be more logical that I should change the original structure because `Cusomter_Name` should always come after `customer_id`. If I don't do that for every view I have to write all the column names in specific order in the `select` statement. – Mistu4u Sep 15 '13 at 05:28
  • @Mistu4u, that may seem logical to _you_ but not necessarily everyone else :-) Example, listing customers and their IDs in alpha order would make more sense in coming out "name" then "id". That's why the rows themselves have no enforced structure beyond the _existence_ and _types_ of columns. Bottom line is, the table simply stores the data, it's not responsible for presentation of the data (including column ordering). – paxdiablo Sep 15 '13 at 05:32
  • This answer is not related to the question. It is an opinion about the unimportance of the physical column order, but that was not the question. – dr fu manchu Dec 04 '18 at 22:28
  • drfumanchu: sometimes the best answer to the question "I want X" is "no, you really don't and here's why". For example, "I want to write an accounting package in machine code" or "I want to write an operating system in COBOL" or "I want to write *anything* in Perl" :-) This question is one of those cases since the physical order of columns in a table has zero effect on how the data can be presented. – paxdiablo Dec 05 '18 at 00:54
  • @paxdiablo that is true. But maybe @Mistu4u was not asking about physical but logical ordering of the columns (unfortunately he did not tell us about his motivation). There is a practical reason for changing the logical column order: developer's convenience. You might want to see the most relevant columns in the viewport without horizontal scrolling, using a tool like Oracle SQL Developer which issues `SELECT *` statements by default. – dr fu manchu Jan 02 '19 at 14:22
  • @drfumanchu, Indeed the idea is to see the most important columns first according to the developers convenience, here the physical ordering is unimportant. – Mistu4u Jan 02 '19 at 14:39