add a column specified location in Table..

[surya]

hello sir,
i have a table emp ,it has three fields one is empno int ,second is
ename varchar(20).
and last is salary ,

emp

empno ename salary
----------- ------------ ---------
1000 suresh 10000
1001 ramesh 20000


i want to add a column name is 'doj varchar ' between columns 'ename'
and 'salary'.
i dont want a add last only , i want to add a column only between
'ename' and 'salary'
give me some example
thanks u sir

[Razvan Socol]

Re: add a column specified location in Table..

Hello,

Currently, this is not possible using a single statement. You can vote
for this suggestion here:

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=ed13e43a-a55a-45ee-98fb-b56f330a2cfa

In the meantime, you can use Enterprise Manager or Management Studio to
make this modification (which recreates the table and copies the data,
a pretty dangerous thing to do in a production environment). See:

http://www.aspfaq.com/show.asp?id=2528

Razvan

[Jens]

Re: add a column specified location in Table..

Hi,

there is no need to put the columns in a table in a correct order,
because you can sort them via the select or a view as you want to.
There is further no way to put a column "in between" because there is
no need to. If you really want to do this (but I would suggest you not
to do this) you have to recreate the table with the new column in
between and copy alle the data from the old table to the new table.


HTH, Jens Suessmeyer.

---

http://www.sqlserver2005.de

---

[Erland Sommarskog]

Re: add a column specified location in Table..

Jens (Jens@sqlserver 2005.de) writes:[color=blue]
> there is no need to put the columns in a table in a correct order,[/color]

How do you know that? If Surya says he wants to add a column in a place
in the table, it is probably because that this is his requirement.

There are several reasons why you want to add new column anywhere in the
column list. We frequently add columns (and drop) to our tables, and it
would be a complete mess, if column order was historic. I try arrange
columns in a logical order, so that it's easier to read the database
documentation, so it's easier to view data with "SELECT *", which we
use a lot when looking at data from Query Analyzer.
[color=blue]
> There is further no way to put a column "in between" because there is
> no need to.[/color]

Crap. This is a very good feature that is missing from SQL Server, and
if you see the link that Razvan posted, you find that that suggestion
has attracted quite a few votes.
[color=blue]
> If you really want to do this (but I would suggest you not
> to do this) you have to recreate the table with the new column in
> between and copy alle the data from the old table to the new table.[/color]

Yes, that is what we do today. ALTER TABLE as it is today is essentially
worthless; it can be used only exceptionally.

The important thing to keep in mind when doing this is to remember to
recreate indexes, triggers and constraints. And to move referencing
foreign keys.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Anith Sen]

Re: add a column specified location in Table..

>> How do you know that?

Anyone with familiarity with data management fundamentals would know for a
fact that assigning positional significance to columns in a relational table
is wasteful.

The header of a relational table is a set of typed column names and elements
of a set have no order. Imposing ordering to the elements is pointless since
they are already identifiable by name. Moreover such ordering significance
when introduced in queries would make them more complex, since the users are
forced to remember the column order along with the column name with no real
advantage.
[color=blue][color=green]
>> If Surya says he wants to add a column in a place in the table, it is
>> probably because that this is his requirement.[/color][/color]

If Surya is aware of the fact that columns are identified by column names,
and positional arrangement of columns provides only superficial aesthetic
benefits, he would not have asked this in the first place.
[color=blue][color=green]
>> There are several reasons why you want to add new column anywhere in the
>> column list. We frequently add columns (and drop) to our tables, and it
>> would be a complete mess, if column order was historic.[/color][/color]

Some may lack the discipline to formalize all the schema changes and/or
document them appropriately. But that does not mean everyone is facing such
mess. Of course you don't expect everyone to be in one, do you :-)?
[color=blue][color=green]
>> I try arrange columns in a logical order, so that it's easier to read the
>> database documentation, so it's easier to view data with "SELECT *",
>> which we use a lot when looking at data from Query Analyzer.[/color][/color]

It is the tail wagging the dog. Your documentation should follow the schema,
not the other way around. If you have poor documentation practices, consider
rectifying it by using better source code configuration/ management
protocols. "Easy to read the doc" is not really a reason to add something
that can be superfluous.
[color=blue][color=green]
>> This is a very good feature that is missing from SQL Server,...[/color][/color]

Not sure if many had found it missing, but as for a DDL at the logical level
it is as simple as shortcut that transparently drops & recreates the table.
There are considerable physical model implications with changing column
order, esp. if there are clustered indexes on the columns that are affected
by the change. Even if it is a heap, any underlying implementations of
constraints, defaults or rule would have to be bound differently if the
columns changed are somehow affected by them.

Having said all the above, I know that SQL deviates from relational model to
some extent. And column order is inherently a part of SQL and is ingrained
in so many aspects of the language as a whole. While positional significance
of the columns in SQL is seemingly relevant esp. with metadata & catalog
management, as a good development practice, it is better to avoid relying on
column order in data definition, manipulation and data management in
general.

--
Anith

[Erland Sommarskog]

Re: add a column specified location in Table..

Anith Sen (anith@bizdatas olutions.com) writes:[color=blue][color=green][color=darkred]
>>> How do you know that?[/color][/color]
>
> Anyone with familiarity with data management fundamentals would know for
> a fact that assigning positional significance to columns in a relational
> table is wasteful.[/color]

Sorry, Anith, but that is just a piece of crap. Developing database systems
is more than just relational theory. It's a about common software-
engineering principles as well.

For the SQL operations as such the order has no importance, but from that
saying that it's wasteful to care about order, is plainly ignorant.
[color=blue][color=green][color=darkred]
>>> I try arrange columns in a logical order, so that it's easier to read
>>> the database documentation, so it's easier to view data with "SELECT
>>> *", which we use a lot when looking at data from Query Analyzer.[/color][/color]
>
> It is the tail wagging the dog. Your documentation should follow the
> schema, not the other way around. If you have poor documentation
> practices, consider rectifying it by using better source code
> configuration/ management protocols. "Easy to read the doc" is not
> really a reason to add something that can be superfluous.[/color]

This was the most stupid I've read in a long time. I can document the
columns in any order I like. In fact documentation and tables come from
the same source - the data-modellling tool. And, yeah, the data-modelling
tool permits me to insert columns in any place. Maybe you should sue Sybase
for permitting me to do that.
[color=blue]
> Not sure if many had found it missing, but as for a DDL at the logical
> level it is as simple as shortcut that transparently drops & recreates
> the table. There are considerable physical model implications with
> changing column order, esp. if there are clustered indexes on the
> columns that are affected by the change. Even if it is a heap, any
> underlying implementations of constraints, defaults or rule would have
> to be bound differently if the columns changed are somehow affected by
> them.[/color]

First you said that column does not matter, because we address columns
by name and position, and now you says it matters a whole because there
may be a poor implementation?

What it is all about at the end of the day, is that I want my developers
to read the documentation for a table in order that is logical for how
the table looks today, not for how the table looked ten years ago, and
all columns added since then.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Jens]

Re: add a column specified location in Table..

[color=blue]
> How do you know that? If Surya says he wants to add a column in a place
> in the table, it is probably because that this is his requirement.[/color]

Not every requirement means that this should be implemented, many
requirements are sourced by the lack of knowledge of the people.
[color=blue]
> There are several reasons why you want to add new column anywhere in the
> column list. We frequently add columns (and drop) to our tables, and it
> would be a complete mess, if column order was historic. I try arrange
> columns in a logical order, so that it's easier to read the database
> documentation, so it's easier to view data with "SELECT *", which we
> use a lot when looking at data from Query Analyzer.[/color]

If the only reason is to get the data in QA the right way, why don´t
you craete a view for each table suing *your* selection order as you
are prefering to it. Although some flavour of displaying data are the
same, it is truly possible that the one developer want to see the
columns in the the colum1,column2, column3..column 32 and another one
want to see col
column32..colum 1,column2, column3. Who will decide what sort is *right*
by default ?

[color=blue]
> Crap. This is a very good feature that is missing from SQL Server, and
> if you see the link that Razvan posted, you find that that suggestion
> has attracted quite a few votes.[/color]

First of all, thanks for the polite and detailed explanation in the
first sentence. :-) As I already said, only because some people are
voting for something, that doesn´t always mean that this is useful.
[color=blue][color=green]
> > If you really want to do this (but I would suggest you not
> > to do this) you have to recreate the table with the new column in
> > between and copy alle the data from the old table to the new table.[/color]
>
> Yes, that is what we do today. ALTER TABLE as it is today is essentially
> worthless; it can be used only exceptionally.[/color]

Good, that we share the same thought in one place :-)


-Jens Suessmeyer.

---

http://www.sqlserver2005.de

---

[David Portas]

Re: add a column specified location in Table..

Erland Sommarskog wrote:[color=blue][color=green]
> > Not sure if many had found it missing, but as for a DDL at the logical
> > level it is as simple as shortcut that transparently drops & recreates
> > the table. There are considerable physical model implications with
> > changing column order, esp. if there are clustered indexes on the
> > columns that are affected by the change. Even if it is a heap, any
> > underlying implementations of constraints, defaults or rule would have
> > to be bound differently if the columns changed are somehow affected by
> > them.[/color]
>
> First you said that column does not matter, because we address columns
> by name and position, and now you says it matters a whole because there
> may be a poor implementation?[/color]

Regretably standard SQL requires column ordering in tables. The world
isn't going to solve that problem any time soon. Unfortunately SQL
Server exacerbates the problem by making no distinction between
physical and logical ordering of columns. I think this is the problem
that Anith is referring to.

If we are to have a new feature for fixing logical column order then I
think we must have an independent method to specify physical order as
well. Physical order should not follow logical order. Separating the
two would remove the requirement to rebuild physical structures when
some logical user requirement changes.

Unfortunately, making it easier to re-order columns will also show up
some weaknesses in SQL Server features. I'm talking about the
proprietary features that are not invariant to column order. I don't
even know a complete list of those problem points. If we want Microsoft
to support logical column ordering for apparently "cosmetic"
documentation purposes (in other words for things that shouldn't affect
end user functionality) then we'd better be very sure which features
are to be defined over logical column order and which over physical
order. That is not a trivial issue and it is the reason why I'm glad
that for the moment re-ordering columns is "hard" to do.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[Erland Sommarskog]

Re: add a column specified location in Table..

David Portas (REMOVE_BEFORE_ REPLYING_dporta s@acm.org) writes:[color=blue]
> Regretably standard SQL requires column ordering in tables. The world
> isn't going to solve that problem any time soon. Unfortunately SQL
> Server exacerbates the problem by making no distinction between
> physical and logical ordering of columns. I think this is the problem
> that Anith is referring to.
>
> If we are to have a new feature for fixing logical column order then I
> think we must have an independent method to specify physical order as
> well. Physical order should not follow logical order. Separating the
> two would remove the requirement to rebuild physical structures when
> some logical user requirement changes.[/color]

I'm not really sure that I understand this. Why would I need a method to
specify the physical order on the page? The only time you would be able
to see this order is you do DBCC PAGE. Or are you thinking of
sys.columns.col umn_id? That is logical order for me.

Already today the physical order is different from logical order to some
extent. If memory serves, all-fixed length columns comes before the
variable-length columns. And a column may be entirely missing on a page,
if it was added with ALTER TABLE as nullable without a default value.
[color=blue]
> Unfortunately, making it easier to re-order columns will also show up
> some weaknesses in SQL Server features. I'm talking about the
> proprietary features that are not invariant to column order. I don't
> even know a complete list of those problem points.[/color]

I should probably know this, but enlighten me. What are you thinking of?
[color=blue]
> If we want Microsoft to support logical column ordering for apparently
> "cosmetic" documentation purposes (in other words for things that
> shouldn't affect end user functionality)[/color]

There are a whole lot of features in SQL Server which have little direct
effect on end-user experience, but which make life simpler for the
developer. TRY-CATCH for instance.

And saying that column order would not be benefit to end users is probably
not very accurate. There are more sites out there than we like to know
where end users watch table data by running SELECT * or functions like
Open Table, and where the column has very direct impact on their
experience.

I like to add that there quite a few arguments for keepin a strict régime
on column order. For instance, if you want to copy data between two tables
with the same logical schema with BCP, you appreciate very much if column
order is the same, as this saves you the hassle or writing a format file.
[color=blue]
> then we'd better be very sure which features are to be defined over
> logical column order and which over physical order. That is not a
> trivial issue and it is the reason why I'm glad that for the moment
> re-ordering columns is "hard" to do.[/color]

The physical order is something that never have to concern me, unless
I start to read database pages directly.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Erland Sommarskog]

Re: add a column specified location in Table..

Jens (Jens@sqlserver 2005.de) writes:[color=blue]
> If the only reason is to get the data in QA the right way, why don´t
> you craete a view for each table suing *your* selection order as you
> are prefering to it. Although some flavour of displaying data are the
> same, it is truly possible that the one developer want to see the
> columns in the the colum1,column2, column3..column 32 and another one
> want to see col
> column32..colum 1,column2, column3. Who will decide what sort is *right*
> by default ?[/color]

We have 800 tables in the database. Are you seriously suggesting that
we should add 800 views only because it's difficult to change column
order? That is definitely not going to make developers less confused.
[color=blue]
> First of all, thanks for the polite and detailed explanation in the
> first sentence. :-) As I already said, only because some people are
> voting for something, that doesn´t always mean that this is useful.[/color]

Of course, it does not mean that it is useful to you. But just because
it's not useful to you, there is no reason to try to cram down your
opinion down their throats. It's not at all helpful to tell people
"you don't need that". If you believe that Surya could solve this under-
lying problem in a better way, you could ask for the reason, to be
able to address the problem. But dismissing his question out of hand
is just plain insincere and about as impolite as my response.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[David Portas]

Re: add a column specified location in Table..

I did actually think that column_id matched physical order. Maybe I'm
wrong about the ordering on the page but at the very least SQL Server
does maintain the value of column_id separate from the column position
displayed in the GUI. In the following example the order is preserved
but the value of column_id doesn't match what is displayed by SELECT *
or the information_sch ema (3 instead of 2).

CREATE TABLE dbo.t1 (col1 INT, col2 INT, col3 INT);
ALTER TABLE dbo.t1 DROP COLUMN col2;

SELECT name, column_id
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo. t1');

Result:

name column_id
------ -----------
col1 1
col3 3

(2 row(s) affected)


You are probably right that physical order is unimportant today but if
SQL Server ever gets a wider range of options for physical data
structures it may become relevant. Then we'll need a separate syntax
for physical order perhaps.

Below is a list of some features that are sensitive to column order in
SQL Server 2005. This is just TSQL. There are others in SSIS and
possibly some in AS and elsewhere too. Of course it's easy to say that
these should respect whatever logical order is defined by DDL
statements - I assume they will. The point is that re-ordering columns
can break a lot of legacy code. Admittedly it's sloppy code that would
break in most cases but it is documented behaviour nevertheless.

Now suppose changing the column order does break something that depends
on column_id (COLUMNS_UPDATE D for example). Can I always recover the
prior state as easily as I screwed it up? Take your BEFORE and AFTER
suggestion for example. Would that allow me to move col3 to position 2
and at the same time give it a column_id of 3 or 4? I don't see how. So
I guess I have to resort to RESTORE DATABASE for that one. No worse
than the current situation maybe, but now that nice easy syntax looks
like something that needs to be well protected from tampering and used
with almost as much reluctance as you would drop and recreate the
table.

Column ordering cannot be seen as something that has minimal production
impact under the current state of SQL Server. So I'd say there's a lot
of other stuff to fix with a higher priority than modifying column
order. And maybe if a few of those things were fixed then we'd care
less about column order anyway.

SELECT
SELECT INTO
INSERT
CREATE VIEW (with SELECT *)
UNION
CHECKSUM(*)
BINARY_CHECKSUM (*)
COLUMNS_UPDATED ()
WITH
OUTPUT
sys.columns.col umn_id
information_sch ema.ordinal_pos ition
BCP
BULK INSERT

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[Erland Sommarskog]

Re: add a column specified location in Table..

David Portas (REMOVE_BEFORE_ REPLYING_dporta s@acm.org) writes:[color=blue]
> I did actually think that column_id matched physical order. Maybe I'm
> wrong about the ordering on the page, but at the very least SQL Server
> does maintain the value of column_id separate from the column position
> displayed in the GUI. In the following example the order is preserved
> but the value of column_id doesn't match what is displayed by SELECT *
> or the information_sch ema (3 instead of 2).[/color]

I would say that column_id reflects logical order, although the gap is a
bit confusing. I guess it has to do with cascading updates to other
system tables.

By the way, find a table with a mix of columns on SQL 2000 and look at
offset and xoffset in syscolumns for that table. Not that I know whether
that information means anything.
[color=blue]
> You are probably right that physical order is unimportant today but if
> SQL Server ever gets a wider range of options for physical data
> structures it may become relevant. Then we'll need a separate syntax
> for physical order perhaps.[/color]

Ouch! I rather not think of it. :-)
[color=blue]
> Below is a list of some features that are sensitive to column order in
> SQL Server 2005. This is just TSQL. There are others in SSIS and
> possibly some in AS and elsewhere too. Of course it's easy to say that
> these should respect whatever logical order is defined by DDL
> statements - I assume they will. The point is that re-ordering columns
> can break a lot of legacy code. Admittedly it's sloppy code that would
> break in most cases but it is documented behaviour nevertheless.[/color]

Interesting, sloppy code is one reason why you may want to do this. Say
that that the code is littered with SELECT * and on top of that
client code refers to columns by numeric index, not by name.

As long as you only have one database, ALTER TABLE may be good. But
assume that you have several databases, for instance because the
application is a product sold to customers. If you rely on ALTER TABLE
of today, and a an update script is run out of order or some other
accident occurs at one site, you get chaos. If you can specify where to
insert a new column, you have a little more control. (This is not one
of the stronger arguments, though. You could say that that application
design deserves chaos.)
[color=blue]
> Now suppose changing the column order does break something that depends
> on column_id (COLUMNS_UPDATE D for example). Can I always recover the
> prior state as easily as I screwed it up? Take your BEFORE and AFTER
> suggestion for example. Would that allow me to move col3 to position 2
> and at the same time give it a column_id of 3 or 4?[/color]

columns_updated might be a case. But this feature appears to hopelessly
difficult to use, that I have always stayed away from it.

Then again, since this feature depends so strongly on columns, say
that you added two columns to a table in the wrong order, and you
did not discover until two weeks later. Admit that an ALTER TABLE MOVE
COLUMN would come in handy here! :-)
[color=blue]
> SELECT
> SELECT INTO
> INSERT
> CREATE VIEW (with SELECT *)
> UNION
> CHECKSUM(*)
> BINARY_CHECKSUM (*)
> COLUMNS_UPDATED ()
> WITH
> OUTPUT
> sys.columns.col umn_id
> information_sch ema.ordinal_pos ition
> BCP
> BULK INSERT[/color]

Of these checksum and binary_checksum could possibly to depend on physical
order to some extent, but it's doubtful whether it is a good idea to
compare values from two databases.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx