About adding a new column...

[Bruno Panetta]

Suppose I have a table with the following columns: Year, SalesInEurope,
SalesInAmerica, TotalSales. I want to add a new column called
SalesInAsia, say, but I want it to appear before TotalSales. How can
this be achieved?
Thanks,

Bruno

[David Portas]

Well first of all I would strongly recommend you change the design of
your table. It's a mistake to represent data in column names because it
makes your data much harder to manipulate and maintain. Basic design
sense would indicate is that the "region" is an attribute and
"America", "Asia", etc are values. In other words one would normally
expect to see ONE column for Region and one column for Sales Value.
Presenting different regions as columns is something you should do in a
report, not in a table.

To answer your specific question, there is no single command to add a
column at a particular ordinal position. The only way to do that in a
table is to re-create the table, or maybe in this case to rename the
columns and repopulate the data. In a production application table
column order is mostly unimportant - the order is determined by the
column order in a SELECT statement, not in a table. If you feel you
have a good reason to change the column order in the table on a
non-production system then you could try using the table designer in
Enterprise Manager. EM will allow you to re-order the columns and
re-create the table for you. EM will also generate the script for you
if you want to see how it's done.

Hope this helps.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

David Portas (RE************ *************** *@acm.org) writes:

In a production application table column order is mostly unimportant -

Yes, if no humans ever look at the database - which is highly unlikely
if the system is in production.

I just get so tired of this. Everytime someone asks about placing a column
in a certain order in a table, there is always someone knowledgable who
needs to tell that person he does not need to do that.

Column order *does* matter, because it makes it so easy to work with.
The fact that SQL Server does not provide any simple command to change
order is no reason to tell people they want the wrong thing.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[David Portas]

I agree with most of what you said. What I mean by "mostly unimportant"
is that for most people most of the time the column order returned by
SELECT * is not important enough to justify the effort and impact of
recreating a table. That's my experience anyway.

The fact that SQL Server does not provide any simple command to change
order is no reason to tell people they want the wrong thing.

I don't agree with this. There is good reason to tell people they are
wrong. Implied column ordering is a serious failing of SQL and has lead
to much bad code and incorrect results. I want to discourage anyone
from building column order dependencies into their systems. I would do
so even if SQL Server made it a trivial task to re-order columns - in
fact I'd be even more vocal about it if there were a trivial solution!
I agree that there is a case for physically ordering columns for
support and development purposes but I don't want people to confuse
that service delivery issue with their business's presentational
requirements.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

David Portas (RE************ *************** *@acm.org) writes:

I agree with most of what you said. What I mean by "mostly unimportant"
is that for most people most of the time the column order returned by
SELECT * is not important enough to justify the effort and impact of
recreating a table. That's my experience anyway.
I would agree that with the current state of the art, that rearranging
columns is a bit too much hassle. Then again, if I were to take over
maintenance were column order were accidental, and particularly different
in different in different instances of the same schema, I might consider
it. (After all, I have the tools to do this rather easily. It is just
the time to run it that could be expensive.)
I don't agree with this. There is good reason to tell people they are
wrong. Implied column ordering is a serious failing of SQL and has lead
to much bad code and incorrect results. I want to discourage anyone
from building column order dependencies into their systems. I would do
so even if SQL Server made it a trivial task to re-order columns - in
fact I'd be even more vocal about it if there were a trivial solution!
I agree that there is a case for physically ordering columns for
support and development purposes but I don't want people to confuse
that service delivery issue with their business's presentational
requirements.

One should not forget that there actually users out there whose only
application is Open Table in Enterprise Manager. I don't like it myself,
but there was a huge outcry when Microsoft tried to leave it out from
Management Studio in SQL 2005.

Of course, using SELECT * in combination with references to columns by
number in client code is extremely bad practice.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[BD]

I am asked to do this on a very regular basis.

The logic I normally go through is as follows:

-delete trigger for the original table (if it exists);
-delete all views for the original table, if they exist (they will keep
referencing the original table even after it is renamed)
-rename the original table to table_name_old (executing the native
sp_rename stored procedure)
-create the new table in the correct structure
-create new default value constraints unless you're okay with system
generated names for default value constraints
-insert into new_table (all columns except for new one) as select (all
columns) from original table
-recreate view(s) if applicable
-recreate trigger if applicable
*then confirm all is well before dropping the old table.

I would recommend making a SQL script for this process, and keeping it
as a template for future use.

I also would recommend SQL scripts over EM or any other GUI: All that
GUIs will ultimately do is provide a nice pretty set of screens and
buttons, and then generate a SQL script which you may or may not see.
Getting used to coding the SQL script will eliminate the middle-man,
and allow you to comfortably replicate the same process again and
again.

I can fire you a sample script with the logic that I use if you like.

[Greg D. Moore \(Strider\)]

"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** ************@12 7.0.0.1...

David Portas (RE************ *************** *@acm.org) writes:

In a production application table column order is mostly unimportant -
Yes, if no humans ever look at the database - which is highly unlikely
if the system is in production.

I just get so tired of this. Everytime someone asks about placing a column
in a certain order in a table, there is always someone knowledgable who
needs to tell that person he does not need to do that.

Column order *does* matter, because it makes it so easy to work with.
The fact that SQL Server does not provide any simple command to change
order is no reason to tell people they want the wrong thing.

I can see your point of view (and often wish in EM I could see columns in
the order I want) but to a point have to disagree with it.

Let me pull a Celko here, but I think getting into the mindshift of thinking
of a table and realizing that order of columns should not be a physical
attribute of the table.

The minute folks assume they are, they start accepting code like select *
from bar and assuming that * will return columns in a specific manner. This
burned us on a code change a year or so ago where due to the way the schema
was changing (partly due to replication) the order of the columns DID in
fact change and of course the programmers who wrote the code in the height
of the dotcom era saved 30 seconds (or about 2 days "internet time" :-) by
typing * instead of a proper column list.

This was fine for 3-4 years until suddenly production code on a high volume
website broke.

Ideally Enterprise Manager could access some sort of metadata that would
store the desired view of columns, independent of the physical layout of the
table.


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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

BD (bo*********@ho tmail.com) writes:

I also would recommend SQL scripts over EM or any other GUI: All that
GUIs will ultimately do is provide a nice pretty set of screens and
buttons, and then generate a SQL script which you may or may not see.
Getting used to coding the SQL script will eliminate the middle-man,
and allow you to comfortably replicate the same process again and
again.

You can use EM to get a script, that you can work from. But beware
that the script that EM generates has several flaws, that you need to
fix:

o Remove all BEGIN TRANSACTION and COMMIT TRANSACTION, except the first
BEGIN and the last COMMIT.

o Remove all GO. Instead wrap all the ALTER and CREATE TABLE statements
in EXEC.

o Add SET XACT_ABORT ON first in the script.

o Replace all WITH NOCHECK in the script WITH CHECK.

o Review the script carefully, so that it does not include changes to
do not intend to make. Yes, EM, may add such changes.

The first three points has to do with the transaction scope. You may
not always want a transaction, if you have large tables. But in such
case, you must be prepared to restore a backup if the script fails.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Greg D. Moore (Strider) (mo************ ****@greenms.co m) writes:

I can see your point of view (and often wish in EM I could see columns in
the order I want) but to a point have to disagree with it.

Let me pull a Celko here, but I think getting into the mindshift of
thinking of a table and realizing that order of columns should not be a
physical attribute of the table.

The minute folks assume they are, they start accepting code like select
* from bar and assuming that * will return columns in a specific manner.
This burned us on a code change a year or so ago where due to the way
the schema was changing (partly due to replication) the order of the
columns DID in fact change and of course the programmers who wrote the
code in the height of the dotcom era saved 30 seconds (or about 2 days
"internet time" :-) by typing * instead of a proper column list.

This was fine for 3-4 years until suddenly production code on a high
volume website broke.

Yes, SELECT * does not belong in application code. (Unless it's some
simple throw-away thing like keep track of the local sports club.)

Just like, it is not good practice to call a routine (be that a C++
function of stored procedure) with 20 parameters with positional actual
parameters. Yet, few would argue that it's a good idea to have the
parameters in random order.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Hugo Kornelis]

On 13 Sep 2005 17:06:25 -0700, BD wrote:

I am asked to do this on a very regular basis.

The logic I normally go through is as follows: (snip)I would recommend making a SQL script for this process, and keeping it
as a template for future use.

Hi BD,

If you make a generic template, then you might wish to include dropping
and recreation of foreign key constraints that reference the table to be
changed. You omitted that from your shortlist.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)