Insert column in specific position using SQL
lmcphee
I want to add a column to this table with an SQL command, but I want that
column added as the 2nd column - not the 5th column.
Or can I add it, and then change column order after with SQL command?
Thanks,
LMcPhee
Aaron Bertrand [SQL Server MVP]
NO.
The way Enterprise Manager does it is drop the table and re-create it. I
don't recommend this approach if your table is larger than a typical
spreadsheet.
Why does column order matter?
Immy
"lmcphee" <lmc...@discussions.microsoft.com> wrote in message
news:2DBE1E37-73AA-4011...@microsoft.com...
Tõnu
One of the basic rules behind relational databases is that physical order of
records in table is insignificant. Following that rule, the order of records
in SQL query is undefined by definition ... well of course, because of
technical implementeation the recordset usually comes in particlular order
but I would not build my application to rely on this order but rather use
explicit sorting in SQL
Best regards,
Tonu
"lmcphee" <lmc...@discussions.microsoft.com> wrote in message
news:2DBE1E37-73AA-4011...@microsoft.com...
Jim Underwood
Never use "select * from" or "insert into table values (...)".
Always specify the columns that you need returned, and the columns that you
need to insert.
Relying on the order of columns in the table will cause you all sorts of
problems. Following the above rules makes the column order insignificant,
and prevents errors caused by someone adding or removing a column from a
table or view.
You should be able to query this news group on "Tabel column order" for many
discussions on the topic.
"lmcphee" <lmc...@discussions.microsoft.com> wrote in message
news:2DBE1E37-73AA-4011...@microsoft.com...
sloan
...
If you're doing it for asthetics, (aka, you want to see the column in
enterprise manager near the beginning
Then use Ent Manager. Heed the warning about table size.
If you're doing it for technical reasons (aka, the "select *", then you're
making a big mistake somewhere.
My company has a coding standard.
Insert into MyTable
Select * from OtherTable
is expressly forbidden.
Insert into MyTable ( a , b , c )
Select d , e , f from OtherTable
is the standand.
"lmcphee" <lmc...@discussions.microsoft.com> wrote in message
news:2DBE1E37-73AA-4011...@microsoft.com...
Erland Sommarskog
> One of the basic rules behind relational databases is that physical
> order of records in table is insignificant. Following that rule, the
> order of records in SQL query is undefined by definition ... well of
> course, because of technical implementeation the recordset usually comes
> in particlular order but I would not build my application to rely on
> this order but rather use explicit sorting in SQL
how they go ahead when they design their databases. The scribble down the
names on the column on small piece of paper, and then throw this pieces
of paper into a bowl and shake it around. Then they draw one piece of paper,
one at a time, and then add that column to the CREATE TABLE statement.
After all, column order does not matter, so you really need to make it
random, so that none of your co-workers believe that the order means
something.
You don't do that? For a new table you lay out the columns in a logical
meaningful order? With PK columns first in logical order? So why would
this not apply when you add new columns? (Save that it takes a little more
work.)
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.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
Jim Underwood
You are correct that it is nice to have columns in a certain order when you
look at a table. Particularly having the keys first. The problem is folks
that "need" to have columns in a certain order are usually doing it to suit
dangerous programming practices.
If you understand that you don't "need" the columns in a particular order,
and you don't write code expecting them in a particular order, then there is
no harm in making sure they are in a visually friendly order in your table.
I think the point is that it is not necessary, and if you learn to depend on
the order, you will get into all kinds of trouble.
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns97C1BB8EC...@127.0.0.1...
Anith Sen
Nice logic! Is that how, in your view, inferences are drawn from existing
premises ?
Column order is insignificant does not mean columns must be randomly
ordered. It simply emphasizes that there is no absolutely need to tie any
integrity or manipulation logic to column positions.
>> You don't do that? For a new table you lay out the columns in a logical
>> meaningful order?
Some do, some don't.
>> With PK columns first in logical order?
I know several who don't. I am sure several others here too. Unless there is
an explainable physical reason, esp. with indexes and/or space consumption,
many don't.
>> So why would this not apply when you add new columns?
Simply put, it offers nothing useful. What integrity benefits does it
provide? Any structural advantages? How about manipulative advantages? Any?
So why?
Why do we have to assume everyone out there tend to think of tables
displayed on a computer screen to be formatted like spreadsheets? The only
argument I have seen from you before is "easy to document"/diagram and "easy
to read on a computer screen" which are trivial considering overall
advantages of ignoring the positional significance of columns.
--
Anith
Raymond D'Anjou
In developement, I create my table columns in a "logical" order.
Once the database is in production though, if I have another column to add,
to the bottom it goes.
Erland seems to believe strongly in keeping that "logical" order.
Without using EM though, it's a pain adding a column in a specific place (I
wonder how Erland does it).
If that table has a couple of million rows or more, the pain is only worse.
"Anith Sen" <an...@bizdatasolutions.com> wrote in message
news:Omazoged...@TK2MSFTNGP05.phx.gbl...
Jim Underwood
if millions of rows come into play. I take his comments as defending the
practice, not necessarily requiring it. Personally I like to see columns in
a particular order, but I don't lose any sleep over adding new columns to
the end.
"Raymond D'Anjou" <rda...@canatradeNOSPAM.com> wrote in message
news:ehmYL1ed...@TK2MSFTNGP03.phx.gbl...
Aaron Bertrand [SQL Server MVP]
> if millions of rows come into play. I take his comments as defending the
> practice, not necessarily requiring it. Personally I like to see columns
> in
> a particular order, but I don't lose any sleep over adding new columns to
> the end.
Right. I think Erland thinks that adding columns to the end later, instead
of where they may logically have belonged were they thought of at design
time, is analogous to randomly selecting the column order. This is not the
case at all, and I don't think any of us is advocating that column order is
completely irrelevant. But to most of us, it is not important enough to
justify completely rebuilding the table.
A
Michael C
standard SQL anyway, and can be avoided if you've got a lot of time to kill :)
Erland Sommarskog
> I suspect that Erland does this selectively, and might choose not to do
> it if millions of rows come into play. I take his comments as defending
> the practice, not necessarily requiring it. Personally I like to see
> columns in a particular order, but I don't lose any sleep over adding
> new columns to the end.
is difficlt to read, and does not agree with the table definition. And
while SELECT * does not have any place in production code, it's great
when running ad hoc queries to have some certain order of the column
that is not just merely historical.
Our tool for generating update script always generate a template
for reloading the table - obviously since it was developed for SQL 6.0
originally. But there is a second point for creating the table anew:
if I run the CREATE TABLE script, I know that what I have in the
database is what I have in SourceSafe.
Erland Sommarskog
> Right. I think Erland thinks that adding columns to the end later,
> instead of where they may logically have belonged were they thought of
> at design time, is analogous to randomly selecting the column order.
> This is not the case at all, and I don't think any of us is advocating
> that column order is completely irrelevant. But to most of us, it is
> not important enough to justify completely rebuilding the table.
Well, given all the funny arguments that these questions regularly
are met with one has to assume that people who use them take them
seroiusly, and really design their database in random order.
Now, as it turns out that is not what people are doing. The bad arguments
are only a cover-up for a missing feature in SQL Server.
Erland Sommarskog
> Column order is insignificant does not mean columns must be randomly
> ordered. It simply emphasizes that there is no absolutely need to tie any
> integrity or manipulation logic to column positions.
Well, the argument from many here is that column *does* matter.
To wit, it's so much easier to add a column at the end, that you
are a blithering idiot that if you try something else.
> Why do we have to assume everyone out there tend to think of tables
> displayed on a computer screen to be formatted like spreadsheets? The
> only argument I have seen from you before is "easy to document"/diagram
> and "easy to read on a computer screen" which are trivial considering
> overall advantages of ignoring the positional significance of columns.
Trivial? OK, an exercise: take a news paper, a pair of scissors and
cut the news paper into pieces and throw it around. Now read the
newspaper. After, according to what you just said, that's trivial,
not the least considering the advantage of ignoring the positional
significance of pages and columns in the newspaper.
Hugo Kornelis
>Aaron Bertrand [SQL Server MVP] (ten...@dnartreb.noraa) writes:
>> Right. I think Erland thinks that adding columns to the end later,
>> instead of where they may logically have belonged were they thought of
>> at design time, is analogous to randomly selecting the column order.
>> This is not the case at all, and I don't think any of us is advocating
>> that column order is completely irrelevant. But to most of us, it is
>> not important enough to justify completely rebuilding the table.
>
>Well, given all the funny arguments that these questions regularly
>are met with one has to assume that people who use them take them
>seroiusly, and really design their database in random order.
Hi Erland,
I take these arguments seriously. But I don't spend extra time ensuring
that columns are in a random order.
If I manually create a table, I just type the columns without regard for
order - but because I'm a human, that usually results in a somewhat
logical ordering. That's how my brain works. (It also helps me ensure
completeness). If I add columns later, I use ALTER The initial logical
ordering is a by-product of how my brains work, not a goal. No need to
maintain it.
Howver, most of my tables are generated by my casetool. This tool
generates the columns in a mostly-alphabetic order. And it uses ALTER
TABLE to add extra columns when needed.
>
>Now, as it turns out that is not what people are doing. The bad arguments
>are only a cover-up for a missing feature in SQL Server.
Do you add ORDER BY NEWID() to yoour order-insensitive queries just to
prove that you really don't care about the ordering?
(BTW, I do understand your arguments and I do recognise that some people
prefer a logical ordering - it's just that I don't care enough to do
extra work for manuallly created tables and to add logic to my casetool
to preserve a logical ordering. Obivously, your priorities are
different).
--
Hugo Kornelis, SQL Server MVP
Hugo Kornelis
>Anith Sen (an...@bizdatasolutions.com) writes:
>> Column order is insignificant does not mean columns must be randomly
>> ordered. It simply emphasizes that there is no absolutely need to tie any
>> integrity or manipulation logic to column positions.
>
>Well, the argument from many here is that column *does* matter.
>To wit, it's so much easier to add a column at the end, that you
>are a blithering idiot that if you try something else.
Hi Erland,
I don't think anybody has been calling anyone a blithering idiot.
If I add a column, I don't care where it ends up (at least not enough to
warrant taking extra actions to influence the position). ALTER TABLE
just happens to add it to the end. If it would put it elsewhere, I'd
accept it just as well.
If your, or anyones priorities are different, than just go ahead. But
you should be aware that reordering the columns of a million-row table
on a high-availability DB won't make you friends... (Yes, I know that
yoou know this - I included the warning for others).
>> Why do we have to assume everyone out there tend to think of tables
>> displayed on a computer screen to be formatted like spreadsheets? The
>> only argument I have seen from you before is "easy to document"/diagram
>> and "easy to read on a computer screen" which are trivial considering
>> overall advantages of ignoring the positional significance of columns.
>
>Trivial? OK, an exercise: take a news paper, a pair of scissors and
>cut the news paper into pieces and throw it around. Now read the
>newspaper. After, according to what you just said, that's trivial,
>not the least considering the advantage of ignoring the positional
>significance of pages and columns in the newspaper.
Awful analogy. A human is not an RDBMS and a newspaper is not an RDB.
If you tell me what would be analogous to a view that restores the
pieces of paper into their original order, you'd have a slightly better
analogy...
Erland Sommarskog
> I don't think anybody has been calling anyone a blithering idiot.
Look, about anytime anyone asks about adding a column somewhere else
than at the end of the table, he is met with more or less open hostility,
and metaphysical drivel about column order does not matter for relational
integrity.
A proper simple answer could be:
There is no syntax for this. You will have to create a new table,
and move over the data. Don't forget to migrate triggers, indexes
and foreign keys, both the table's own keys and referencing FKs.
You can use the Table Designer in Enterprise Manager or Management
Studio to generate this for you. However, the Table Designer has
several severe flaws in its modification scripts, making this a
dangerous operation. Personally, I feel that it is not worth the
effort, so I recommend that you put the last column of the table.
A polite and explanatory response without any "why do you want to do
this?".
> If your, or anyones priorities are different, than just go ahead. But
> you should be aware that reordering the columns of a million-row table
> on a high-availability DB won't make you friends...
Could you elaborate on this? I can think of two possibilities why
this could be a problem, but none of them are very convincing:
1) There is code with SELECT * out there, and client code that refers
to fields in recordsets by number, so changing column order could
break code. (This is bad practice, obviously.)
2) Reloading the table takes a lot of time. Yes, and so does many
ALTER TABLE operations. If you reload the table, you can at least
keep it available for read access most of the time. ALTER TABLE
takes it for lunch a long time.
But maybe there is something I have missed?
>>Trivial? OK, an exercise: take a news paper, a pair of scissors and
>>cut the news paper into pieces and throw it around. Now read the
>>newspaper. After, according to what you just said, that's trivial,
>>not the least considering the advantage of ignoring the positional
>>significance of pages and columns in the newspaper.
>
> Awful analogy. A human is not an RDBMS and a newspaper is not an RDB.
You missed the point. RDBMSs are used by humans, and humans are the
entities that consume the table definition.
Michael C
"Erland Sommarskog" wrote:
> Could you elaborate on this? I can think of two possibilities why
> this could be a problem, but none of them are very convincing:
> ...
> But maybe there is something I have missed?
>
3) Applications that take advantage of the ODBC API BulkOperations
functionality require that you specify columns by ordinal position in the
table and not by column name.
Hugo Kornelis
>Hugo Kornelis (hu...@perFact.REMOVETHIS.info.INVALID) writes:
>> I don't think anybody has been calling anyone a blithering idiot.
>
>Look, about anytime anyone asks about adding a column somewhere else
>than at the end of the table, he is met with more or less open hostility,
Hi Erland,
I don't understand this. Could you point out the "more or less open
hostility" in this thread? I see some messages to Imcphee explaining how
to do what he/she wants, either with or without warning, and also some
messages recommending not to do it and/or asking for his/her reason for
this wish.
In the subthread following your reaction, I see no hostility either,
though I do see disagreement between people over the question if the
benefit is worth the cost.
>and metaphysical drivel about column order does not matter for relational
>integrity.
Ehh? I thought that you agreed that the RDBMS doesn't care aboout column
order and that therefor relational integrity is not affected by it? So
why are you now calling it "drivel"? (Both my English-Dutch dictionary
and www.dictionary.com agree that drivel is "stupid or ssenseless
talk").
>
>A proper simple answer could be:
>
> There is no syntax for this. You will have to create a new table,
> and move over the data. Don't forget to migrate triggers, indexes
> and foreign keys, both the table's own keys and referencing FKs.
> You can use the Table Designer in Enterprise Manager or Management
> Studio to generate this for you. However, the Table Designer has
> several severe flaws in its modification scripts, making this a
> dangerous operation. Personally, I feel that it is not worth the
> effort, so I recommend that you put the last column of the table.
>
>A polite and explanatory response without any "why do you want to do
>this?".
And exactly because of the absense of "why do you want to do this", I
dislike this answer.
I'll grant you that wise, well-informed and sensible people *can* come
to the conclusion that order of columns is so important to them that
they are willing to pay a price - the mere fact that you defend this
position is proof enough. But I think that most people who ask this
question here are NOT well-informed. They are unaware of the steps EM
takes to fix column order and they are often also unaware of various
methods to solve their perceived need of a certain column order.
It's somewhat akin to people asking for help with a nested cursor in a
trigger - telling them to move their FETCH down a line or two might
solve their perceived problem, but asking them what they really want to
achieve, and why do think they need a nested cursor in a trigger is
often the first sttep to a much better advice.
>
>> If your, or anyones priorities are different, than just go ahead. But
>> you should be aware that reordering the columns of a million-row table
>> on a high-availability DB won't make you friends...
>
>Could you elaborate on this? I can think of two possibilities why
>this could be a problem, but none of them are very convincing:
>
>1) There is code with SELECT * out there, and client code that refers
> to fields in recordsets by number, so changing column order could
> break code. (This is bad practice, obviously.)
Agreed.
>
>2) Reloading the table takes a lot of time. Yes, and so does many
> ALTER TABLE operations. If you reload the table, you can at least
> keep it available for read access most of the time. ALTER TABLE
> takes it for lunch a long time.
I just ran some tests on a 2 million row table in a test database on SQL
Server 2005 RTM.
Using the modify table dialog to add a NULLable integer column in the
middle of the table took over 50 seconds (timed with my wrist-watch).
During this whole time, the table was locked (tested by submitting a
SELECT COUNT(*) FROM MyTable from a SQLCMD window). I didn't run a
similar test for a column with NOT NULL constraint and DEFAULT but
consider how the generated SQL looks, I expect about the same time.
Using ALTER TABLE to add the same NULLable column at the end of the
table is almost instantaneous. Even if I need the column to be NOT NULL,
using UPDATE to set the values, then ALTERing the column to NOT NULL
takes less time than the complete SSMS operation. Plus, I could have
used a loop to update the values in batches in order to prevent any
table locks from being taken and to ensure that row locks are held for
very short periods only. That would have increased the total time, but
it would have reduced the impact on a high-availability DB.
Test script and output are below (after the rest of my reply)
>
>But maybe there is something I have missed?
Nope, that was all. Quite enough for me. Can you imagine Google being
unresponsive for a few minutes because they are rebuilding their tables?
>
>>>Trivial? OK, an exercise: take a news paper, a pair of scissors and
>>>cut the news paper into pieces and throw it around. Now read the
>>>newspaper. After, according to what you just said, that's trivial,
>>>not the least considering the advantage of ignoring the positional
>>>significance of pages and columns in the newspaper.
>>
>> Awful analogy. A human is not an RDBMS and a newspaper is not an RDB.
>
>You missed the point. RDBMSs are used by humans, and humans are the
>entities that consume the table definition.
Most humans will consume the contents of the table. In most cases, the
order of the data they see need not match the order of columns in the
tables. Front end, canned queries, stored procedures and views can be
used to get the column order that the end user needs to see.
Developers are not working with the data but with the metadata - but
they should not base their work on the RDBMS's reports of the metadata.
Developers should be working from the repository.
As I already said before, I do see how people can consider a "good"
ordering of the columns to be nice. But it really should be a trade-off
where all consequences of rearranging the columns and all alternatives
that can be used instead of changing the column order of the table
itself should be carefully considered. I can't imagine that anyone would
even consider rearranging column order in a multi-GB database with an
uptime of 99.9999% in it's SLA. In smaller databases and databases with
a regular maintenance window - now that's where I can see this
happening.
As promised, here is the code I used to test the performance of adding a
column with custom T-SQL, followed by the output.
CHECKPOINT
DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
go
SELECT COUNT(*) FROM MyTable
go
DECLARE @started datetime
DECLARE @ended datetime
SET @started = CURRENT_TIMESTAMP
ALTER TABLE MyTable
ADD xyz int
SET @ended = CURRENT_TIMESTAMP
SELECT 'Adding NULLable column' AS Action, DATEDIFF(ms, @started,
@ended) AS "duration (ms)"
go
DECLARE @started datetime
DECLARE @ended datetime
SET @started = CURRENT_TIMESTAMP
UPDATE MyTable
SET xyz = 0
SET @ended = CURRENT_TIMESTAMP
SELECT 'Adding values' AS Action, DATEDIFF(ms, @started, @ended) AS
"duration (ms)"
go
DECLARE @started datetime
DECLARE @ended datetime
SET @started = CURRENT_TIMESTAMP
ALTER TABLE MyTable
ADD CONSTRAINT df_xyz DEFAULT 0 FOR xyz
ALTER TABLE MyTable
ALTER COLUMN xyz int NOT NULL
SET @ended = CURRENT_TIMESTAMP
SELECT 'Adding NOT NULL constraint' AS Action, DATEDIFF(ms, @started,
@ended) AS "duration (ms)"
go
ALTER TABLE MyTable
DROP CONSTRAINT df_xyz
ALTER TABLE MyTable
DROP COLUMN xyz
go
-----------
2148192
Action duration (ms)
---------------------- -------------
Adding NULLable column 93
Action duration (ms)
-------------- -------------
Adding values 16906
Action duration (ms)
-------------------------- -------------
Adding NOT NULL constraint 3250
As a reference, here's the code SSMS generated to add the xyz column in
the middle of the table (executing it took 51.6 seconds, according to
the stopwatch in my wristwatch):
/* To prevent any potential data loss issues, you should review this
script in detail before running it outside the context of the database
designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_MyTable
(
WorkOrderID int NOT NULL,
ProductID int NOT NULL,
OperationSequence smallint NOT NULL,
LocationID smallint NOT NULL,
ScheduledStartDate datetime NOT NULL,
ScheduledEndDate datetime NOT NULL,
ActualStartDate datetime NULL,
xyz int NULL,
ActualEndDate datetime NULL,
ActualResourceHrs decimal(9, 4) NULL,
PlannedCost money NOT NULL,
ActualCost money NULL,
ModifiedDate datetime NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.MyTable)
EXEC('INSERT INTO dbo.Tmp_MyTable (WorkOrderID, ProductID,
OperationSequence, LocationID, ScheduledStartDate, ScheduledEndDate,
ActualStartDate, ActualEndDate, ActualResourceHrs, PlannedCost,
ActualCost, ModifiedDate)
SELECT WorkOrderID, ProductID, OperationSequence,
LocationID, ScheduledStartDate, ScheduledEndDate, ActualStartDate,
ActualEndDate, ActualResourceHrs, PlannedCost, ActualCost, ModifiedDate
FROM dbo.MyTable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.MyTable
GO
EXECUTE sp_rename N'dbo.Tmp_MyTable', N'MyTable', 'OBJECT'
GO
ALTER TABLE dbo.MyTable ADD CONSTRAINT
PK_MyTable PRIMARY KEY CLUSTERED
(
WorkOrderID,
OperationSequence
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
Erland Sommarskog
> I don't understand this. Could you point out the "more or less open
> hostility" in this thread?
>>and metaphysical drivel about column order does not matter for relational
>>integrity.
>
> Ehh? I thought that you agreed that the RDBMS doesn't care aboout column
> order and that therefor relational integrity is not affected by it? So
> why are you now calling it "drivel"?
You walk into a car shop, and you tell the salesman "I want a red car". In
reply you get: "Why do you do want a red car??? Red cars don't run faster
than blue or green cars. Nor do they are they any safer than blue or green
cars. Of course, you could buy a blue car and paint it red, but that would
be an extra cost, and besides a colour which is not the original colour
would easier peel off. There is absolutely no reason why you should buy a
red car!". The true story is of course, that he does not have any red cars
in stock.
I don't know about you, but in my book this counts as both hostile and
drivel. The most likely reason I want a red car is simply that I prefer
that colour for esthetic reasons.
And the same applies to column order. For whatever reason people are asking
for that, I think few have even considered relational integrity and all
that. The argument is simply irrelevant to the question.
>>A proper simple answer could be:
>>
>> There is no syntax for this. You will have to create a new table,
>> and move over the data. Don't forget to migrate triggers, indexes
>> and foreign keys, both the table's own keys and referencing FKs.
>> You can use the Table Designer in Enterprise Manager or Management
>> Studio to generate this for you. However, the Table Designer has
>> several severe flaws in its modification scripts, making this a
>> dangerous operation. Personally, I feel that it is not worth the
>> effort, so I recommend that you put the last column of the table.
>>
>>A polite and explanatory response without any "why do you want to do
>>this?".
>
> And exactly because of the absense of "why do you want to do this", I
> dislike this answer.
But why do you need to ask that? There happens to be no red cars in stock,
that is the product has a short-coming, so it's more difficult to implement.
Are you just like that salesguy that you staunchly have to defend the
product, rather than saying "sorry, there is no easy way to do this".
Say that next version of SQL Server ship with
ALTER TABLE tbl ADD COLUMN col1b int NULL AFTER col1
(and assume that this executes swiftly). Will you still feel the need to
question people's motives for wanting to do this?
> But I think that most people who ask this question here are NOT
> well-informed. They are unaware of the steps EM takes to fix column
> order
Yes, they should be informed that in the current version of SQL Server
this is a complex task, and that they may not find it worth the effort. But
there is no reason to question why they want to do it.
> It's somewhat akin to people asking for help with a nested cursor in a
> trigger - telling them to move their FETCH down a line or two might
> solve their perceived problem, but asking them what they really want to
> achieve, and why do think they need a nested cursor in a trigger is
> often the first sttep to a much better advice.
The difference is that cursors most often should be avoided, not the least
in triggers. In this case, the request is valid, but the product has no
good response to it.
> Using the modify table dialog to add a NULLable integer column in the
> middle of the table took over 50 seconds (timed with my wrist-watch).
> During this whole time, the table was locked (tested by submitting a
> SELECT COUNT(*) FROM MyTable from a SQLCMD window). I didn't run a
> similar test for a column with NOT NULL constraint and DEFAULT but
> consider how the generated SQL looks, I expect about the same time.
>
> Using ALTER TABLE to add the same NULLable column at the end of the
> table is almost instantaneous. Even if I need the column to be NOT NULL,
> using UPDATE to set the values, then ALTERing the column to NOT NULL
> takes less time than the complete SSMS operation. Plus, I could have
> used a loop to update the values in batches in order to prevent any
> table locks from being taken and to ensure that row locks are held for
> very short periods only. That would have increased the total time, but
> it would have reduced the impact on a high-availability DB.
It's somewhat strange to compare a trick with ALTER TABLE command with a
script from EM out of the box. A table copy can be run in batches as well.
And not everyone who adds a non-nullable column with ALTER TABLE may
consider the possibility adding the column as nullable, and change it
to NOT NULL later.
There are also other interesting changes you can make. Some time back I
considered of changing an update script so that instead of copying the
table, it would use ALTER TABLE ALTER COLUMN to change a couple of columns
from char(16) to varchar(16). I don't remember the exact timings, since
ALTER TABLE was not instantenous, I had no reason to replace the table copy.
(Which is the default in our update scripts.)
>>But maybe there is something I have missed?
>
> Nope, that was all. Quite enough for me. Can you imagine Google being
> unresponsive for a few minutes because they are rebuilding their tables?
I'm sorry, but what has this to do with anything? Changing tables in a
live database with a high requirement of uptime is a complex business
for which neither a simple update from EM or a plain ALTER TABLE is
satisfactory. It takes very careful planning to implement.
> Most humans will consume the contents of the table. In most cases, the
> order of the data they see need not match the order of columns in the
> tables. Front end, canned queries, stored procedures and views can be
> used to get the column order that the end user needs to see.
>
> Developers are not working with the data but with the metadata - but
> they should not base their work on the RDBMS's reports of the metadata.
> Developers should be working from the repository.
And why should the repository have a different order from the database?
If you read the documentation for a stored procedure, don't you expect
the parameters to be listed in the order they appear in the argument
list?
And a developer does not only look in a repository. He also reviews data
he has inserted or updated, and to this end he typically uses SELECT * or
Open Table.
Furthermore, developers are not the only persons who engage in this
activity. There is support staff as well. (Which in our small shop, very
well can be a developer, by the way.) You have a customer on the phone
asking why the operation he is trying to carry out does not give the
expected result. You suspect that someone might have changed some basic
data. Quickly you can type SELECT * FROM tbl. You can then scroll to the
far right, because that's where the auditing columns always are in our
tables. When the table has 60+ columns, I can tell you will appreciate
that.
> I can't imagine that anyone would even consider rearranging column order
> in a multi-GB database with an uptime of 99.9999% in it's SLA.
If you have that requirement on uptime, I don't think you should considering
changing any tables at all. All changes would have to be by adding new
tables. And even those would have to be very very carefully considered.
Beside that, I like to remind you that the cost of changing column order
today, is only due to a defect in the product, not that the operation itself
is inherently expensive.
Anith Sen
>> In reply you get: "Why do you do want a red car???
Try a buyer demanding to have the right and left headlamps be interchanged
for "aesthetic purpose" or to have the back tires interchanged for
"aesthetic reasons" when there is no wear n' tear on them. Similarly made
headlamps situated would make no difference in its usage and simply
interchanging them would offer no benefits other than the additional
mechanical and electrical work involved in re-wiring the headlamps. Basic
principles of applied mechanics imply that similar tires of same make that
are diametrically equal mounted on each of the rear axle offer the same
results as they would if they were interchanged.
Why would an honest dealer agree to make such changes without questioning
the buyer's motive?
>> I don't know about you, but in my book this counts as both hostile and
>> drivel. The most likely reason I want a red car is simply that I prefer
>> that colour for esthetic reasons.
Do you think the ones making this request know *why* relational model
ignores positional significance to columns? If they don't, I am not
surprised that just it comes across as drivel.
>> Say that next version of SQL Server ship with
>>
>> ALTER TABLE tbl ADD COLUMN col1b int NULL AFTER col1
>>
>> (and assume that this executes swiftly). Will you still feel the need to
>> question people's motives for wanting to do this?
Sure it does. The language is already clogged with such "features" that go
against proven principles. As usual, some of us have enough excuses to use
them anyway.
>> Yes, they should be informed that in the current version of SQL Server
>> this is a complex task, and that they may not find it worth the effort.
>> But there is no reason to question why they want to do it.
When there are no structural, manipulative and integrity advantages for a
change in poster's request, it is sensible to question his intention. The
chances are, the poster is often misguided to believe there should be column
ordering and in some cases, he is unaware of the basics altogether.
Unless there is a physical reason forced by the DBMS in a given
implementation, there is no reason to re-arrange the columns in certain
fashion.
>> Beside that, I like to remind you that the cost of changing column order
>> today, is only due to a defect in the product, not that the operation
>> itself is inherently expensive.
Can you elaborate on this?
--
Anith
Jim Underwood
why they are doing it, and educated as to the dangers of depending on column
order. It would be wrong, IMO, to let them continue using "select *" or
"insert into table values (...)" without explaining the trouble this can
cause.
As long as they understand that, and they understand the overhead and extra
code involved in changing the column order, I see no reason why they should
not do that. Logical order of columns does simplify development,
particularly when you are new to a particular system. Folks just need to
understand that it is strictly an aesthetic issue, and in no way a technical
one, unless you follow bad practices as shown above.
"Anith Sen" <an...@bizdatasolutions.com> wrote in message
news:OnLOB9De...@TK2MSFTNGP02.phx.gbl...
Aaron Bertrand [SQL Server MVP]
> why they are doing it, and educated as to the dangers of depending on
> column
> order. It would be wrong, IMO, to let them continue using "select *" or
> "insert into table values (...)" without explaining the trouble this can
> cause.
Never mind that, they should not become accustomed to changing this for the
wrong reasons, because if they test it on their desktop with 20 rows, they
are likely to get fired when they run it on their production system with 20B
rows.
A
Erland Sommarskog
> the wrong reasons, because if they test it on their desktop with 20
> rows, they are likely to get fired when they run it on their production
> system with 20B rows.
As they would if the did
ALTER TABLE tbl ADD col int IDENTITY
If you are going to change huge tables, you need to learn to master
both ALTER TABLE, and the reload technique, and you will need to be able
to combine them to implement such changes. And most of all, you will need
to learn to test beforehand.
Erland Sommarskog
>>> You walk into a car shop, and you tell the salesman "I want a red car".
>>> In reply you get: "Why do you do want a red car???
>
> Try a buyer demanding to have the right and left headlamps be interchanged
> for "aesthetic purpose" or to have the back tires interchanged for
> "aesthetic reasons" when there is no wear n' tear on them. Similarly made
> headlamps situated would make no difference in its usage and simply
> interchanging them would offer no benefits other than the additional
> mechanical and electrical work involved in re-wiring the headlamps. Basic
> principles of applied mechanics imply that similar tires of same make that
> are diametrically equal mounted on each of the rear axle offer the same
> results as they would if they were interchanged.
Of course, if you are colour-blind you may not understand the difference
between a red and a green car.
I understand that you don't care a dim wit about column order. Fine.
But please don't try to tell me and other people that we are misguided
if we want more order in our tables that you have yours. That is just
plain insulting.
> Sure it does. The language is already clogged with such "features" that go
> against proven principles.
Proven principles? That the best place for a new column is at the
end of the column list?
> When there are no structural, manipulative and integrity advantages for a
> change in poster's request,
And no one has suggested that there is. That's why I call it drivel
when you bring it up.
The advantage is mainly cognitive. And, unfortunately, people with
big technical and theoretical skills in area, completly fail to under-
stand the cognitive aspect of things.
Erland Sommarskog
> I think folks who ask how to force column order in a table should be
> asked why they are doing it, and educated as to the dangers of depending
> on column order. It would be wrong, IMO, to let them continue using
> "select *" or "insert into table values (...)" without explaining the
> trouble this can cause.
I think it's the other way round. People who are obssesed with that
new columns should be at the end of the table, are afraid that SELECT *
where the column access columns number will break.
Aaron Bertrand [SQL Server MVP]
> But please don't try to tell me and other people that we are misguided
> if we want more order in our tables that you have yours. That is just
> plain insulting.
I don't think that's it at all, Erland. I think you are missing my point at
least, and maybe others' as well.
What I'm saying is that there is no absolute need to have columns in a
specific order, and yes it's nice, and yes some people place that very high
on their needs list, but technically it is not necessary. The other thing
that I stand behind is that the cost of doing so be weighed in a
cost/benefit analysis of some kind before blindly being applied. And I
think one last point is that adding a column to the end of the table is not
the opposite of having the columns in the exact desired order... in other
words, those who question the need to do this are not suggesting that the
only answer is to have a program create your CREATE TABLE statement in a
random order -- which you implied earlier in this thread. Nobody is
advocating that (unless they are doing so silently!).
I typically ask why they want to do this, because I think 9 times out of 10
it is for the wrong reasons (catering to SELECT * etc). What I try to do is
bring everything out into the discussion, instead of just telling newbies to
drop the table and re-create it. Because I think that is potentially much
more damaging than asking them why they think they need to do that in the
first place. If you think that's hostile, then I don't know what else to
tell you except to grow thicker skin and be thankful that Celko hasn't
participated yet. :-)
Aaron Bertrand [SQL Server MVP]
>> Never mind that, they should not become accustomed to changing this for
>> the wrong reasons, because if they test it on their desktop with 20
>> rows, they are likely to get fired when they run it on their production
>> system with 20B rows.
>
> As they would if the did
>
> ALTER TABLE tbl ADD col int IDENTITY
If they have a reason to add that column, then they are going to have to go
through that pain at some point no matter what. If they need to have that
column live in a specific position, that's some extra pain that might be
induced for the wrong reasons.
I don't think adding a column is in the same category as adding a column in
a specific spot. As I've said elsewhere, the exact location of the column
does not change how the database operates, only how you interface with it.
Typically the reasons behind adding a column are far more important than the
reasons behind needing that column to live in a specific ordinal position.
I'm not saying that none of the reasons is valid, just that they're not
going to be show-stoppers in most scenarios.
Erland Sommarskog
> point at least, and maybe others' as well.
>
> What I'm saying is that there is no absolute need to have columns in a
> specific order, and yes it's nice, and yes some people place that very
> high on their needs list, but technically it is not necessary. The
> other thing that I stand behind is that the cost of doing so be weighed
> in a cost/benefit analysis of some kind before blindly being applied.
I think it should be very clear from my posts, that I'm fully aware
of this. It should also be clear from my posts, that I consider this to
be a shortcoming in the product.
After all, think if you were only permitted to add new parameters to stored
procedures at the end of the parameter list! Sounds silly? Well, that
was the interface of the so-called Assisted Editor for stored procedures
in beta 2 of SQL Server Management Studio. (This editor was later dropped.)
Or, way back in SQL 6.5, about any table change required a table reload,
as all you could do was to add nullable columns and IDENTITY columns.
After all, there is on absolute need to have columns NOT NULL, so did
you tell people who wanted to add NOT NULL columns, that they should
add a nullable column with a constraint instead?
With the current state of affairs changing column order is an complex
manoeuvre, and I think most people when they learn about the appropriate
steps may abandon the idea. After all, in most cases it is a timid
question about whether it is possible.
Aaron Bertrand [SQL Server MVP]
> manoeuvre, and I think most people when they learn about the appropriate
> steps may abandon the idea. After all, in most cases it is a timid
> question about whether it is possible.
I still think it is a reasonable and not hostile question to ask why, and
spark conversation that may deter the behavior period. There may be a
legitimate reason, but from my experience, the request is usually based on
aesthetics (e.g. I want to say SELECT * and I want this new column to appear
third from the end).
What I want to avoid is the typical response, do it in Enterprise Manager,
or drop the table and re-create it. Because what I am concerned about is
that the newbie will just follow the advice, assuming it is sound, without
knowing that on a larger table this could mean certain doom. I understand
you argue that people need to learn this, but they aren't going to learn it
if they ask this question and we just give them the quick and dirty answer.
Anith Sen
>> between a red and a green car.
Whatever suits your fancy...
>> I understand that you don't care a dim wit about column order. Fine. But
>> please don't try to tell me and other people that we are misguided if we
>> want more order in our tables that you have yours. That is just plain
>> insulting.
Generally, people who claim to professionally work on certain field are
expected to know some fundamentals of the field. So why is it insulting when
challenged on the basics in the understanding or appreciation of relational
model's prohibition on column ordering?
>> Proven principles? That the best place for a new column is at the end of
>> the column list?
No, ignoring positional significance to columns. Positional significance
suggests physical dependence -- what needs to be proven about it?
http://en.wikipedia.org/wiki/Data_independence
In practice, the maintenance implications of doing the change in SQL Server
are not even necessary to mention. Aaron and Jim has already mentioned the
issues with SELECT * and related stuff. Changing schema in already populated
tables with substantial number of rows are not easy at all. The composite
references with columns involved in change, views/sql statements, triggers
using column positions, associated applications using positional
representation of columns ( ADO field indexes ), catalog tables like
sysindexes etc.
>> That's why I call it drivel when you bring it up.
When you have no clue what it is, you call it drivel. Do you know why the
relational model prohibits explicit column ordering in a table?
http://web.onetel.com/~hughdarwen/TheThirdManifesto/Importance-of-Column-Names.pdf
The problem with "aesthetic benefits" is that it caters to individual
tastes. Expecting the DBMS to support such features amounts to supporting
the imaginations of each user of the DBMS, which is quite impractical. It
makes sense to ignore aesthetic benefits in favor of usability in any of --
integrity, manipulation or structure. ( FYI, these three makes the
fundamental components of a logical model, just so that you do not confuse
it with drivel )
http://en.wikipedia.org/wiki/Relational_model
In SQL, certain operations are non-commutative. And the columns in a based
table do not sufficiently insulate themselves from the physical level of
representation. Thus, unless there is a physical model consideration, there
is no need to force column ordering in a table.
The point is that forcing column ordering offers nothing beneficial to
existing DBMS functionality, but may lead to negative consequences to a
myriad of associated utilitarian functionalities. Given that few in the
field are generally aware of the need of referencing columns by names rather
than their positions, it helps to question their intentions, correct any
misunderstandings and offer any potential suggestions.
>> The advantage is mainly cognitive. And, unfortunately, people with big
>> technical and theoretical skills in area, completly fail to under-stand
>> the cognitive aspect of things.
I'll leave it to the people who fit that bill respond to it.
--
Anith
Anith Sen
>> table, are afraid that SELECT * where the column access columns number
>> will break.
Not sure if there are many with such an obsession, but I know quite a few
who consider ordering of column totally irrelevant on grounds in physical
data independence and its implications.
And there are several who by virtue of being sufficiently experienced in
using the product understand the difficulty in enforcing and maintaining the
column order as well.
--
Anith
Aaron Bertrand [SQL Server MVP]
>>> between a red and a green car.
>
> Whatever suits your fancy...
And I really hope I don't meet up with that person at an intersection. :-)
A
Anith Sen
>> it's nice, and yes some people place that very high on their needs list,
>> but technically it is not necessary.
That is the jist of the matter, concisely put. Then we mix it with
politeness and PC nonsense, some relevance to the issue is lost.
--
Anith
Hugo Kornelis
>Hugo Kornelis (hu...@perFact.REMOVETHIS.info.INVALID) writes:
>> I don't understand this. Could you point out the "more or less open
>> hostility" in this thread?
>>...
>>>and metaphysical drivel about column order does not matter for relational
>>>integrity.
>>
>> Ehh? I thought that you agreed that the RDBMS doesn't care aboout column
>> order and that therefor relational integrity is not affected by it? So
>> why are you now calling it "drivel"?
>
>You walk into a car shop, and you tell the salesman "I want a red car". In
>reply you get: "Why do you do want a red car??? Red cars don't run faster
>than blue or green cars. Nor do they are they any safer than blue or green
>cars. Of course, you could buy a blue car and paint it red, but that would
>be an extra cost, and besides a colour which is not the original colour
>would easier peel off. There is absolutely no reason why you should buy a
>red car!". The true story is of course, that he does not have any red cars
>in stock.
Hi Erland,
Not a good analogy, IMO. First, I can't be equated to the salesman,
because I don't sell the car (the DBMS). I don't work for MS, not do I
have any financial interest in their successes or failures. I just
happpen to have been working with SQL Server over most of the last 10
years, and I like sharing my knowledge. If I had been employed by an
Oracle shop 10 years ago, I'd now probably be answering questions in the
Oracle newsgroups.
I will (and have!) criticize SQL Server if I think that it's missing
features or sports the wrong features. I will also advise people
tochoose another DBMS if I feel that SQL Server might not be the best
solution for their problem. All things that a salesman can't do.
But an even more important difference is that the color of a car is only
a cosmetic feature that some people find important while other people
care less. Not so for column order - some people do in fact realise that
changing the order is a mere cosmetical operation, but many people think
they need it for a different reason. Let me try to extend your analogy
(even though I am aware that analogies can never be perfect).
* Let's assume that many people think that red cars are better visible
and as a result safer than cars with other colors.
* Let's also assume that the salesman knows that there is lots of
evidence against this popular misconception.
* And let's assume that red cars are more expensive than other cars
(even though this might not be obvious at first).
With these assumption, would you still be upset if the salesman
questions yoour mootives for asking a red car?
Now, let's take it one step further - what if recent studies indicate
that people driving red cars are actually MORE prone to have accidents,
probably because they think that they are safer and are therefor
unconsiously taking more risks. Now, how would you feel about the
salesman asking you why you want a red car?
Reordering columns for the right reason (the cosmetic one) can be a
valid choice, as long as you are aware of the cost (either downtime or
limited concurrency during the operation). But reordering columns for
the wrong reasons can be another step towards abyss. And I don't want to
be the guy pointing people to abyss - at least not without warning them
of what lies ahead. THAT is why I question the poster's motive if I see
a question about this.
(snip)
>Say that next version of SQL Server ship with
>
> ALTER TABLE tbl ADD COLUMN col1b int NULL AFTER col1
>
>(and assume that this executes swiftly). Will you still feel the need to
>question people's motives for wanting to do this?
I would mainly question the MS SQL Server design team's motive for
choosing to add this extension to the ALTER TABLE syntax instead of
spending their development time on more relevant matters.
But *if* this option would indeed be included and *if* it would indeed
execute swiftly, then that would be one argument less against the idea
of reordering columns in a table.
I'd probably _still_ ask people why they want to do this, or at least
include a firm warning against SELECT * and INSERT without column list,
though.
>
>> But I think that most people who ask this question here are NOT
>> well-informed. They are unaware of the steps EM takes to fix column
>> order
>
>Yes, they should be informed that in the current version of SQL Server
>this is a complex task, and that they may not find it worth the effort. But
>there is no reason to question why they want to do it.
Yes, there is. If I don't ask why they want to do this, how can I offer
alternative suggestions? For a user who wants to do this because he
wants his INSERT INTO Table01 SELECT * FROM Table02 to work I have
oother recommendations thatn for a user who wants to do this so that the
most frequently used columns can be seen without scrolling after
executing SELECT * FROM Table02.
>
>> It's somewhat akin to people asking for help with a nested cursor in a
>> trigger - telling them to move their FETCH down a line or two might
>> solve their perceived problem, but asking them what they really want to
>> achieve, and why do think they need a nested cursor in a trigger is
>> often the first sttep to a much better advice.
>
>The difference is that cursors most often should be avoided, not the least
>in triggers. In this case, the request is valid, but the product has no
>good response to it.
This is where you and I differ - I think that reordering columns, like
cursors, should often be avoided.
To paraphrase your own words - if the next version of SQL Server ships
with cursor optimizations that make them run even faster than setbased
SQL in almost all cases, would you still feel the need to question
people's motives for wanting to use triggers?
(snip)
>It's somewhat strange to compare a trick with ALTER TABLE command with a
>script from EM out of the box. A table copy can be run in batches as well.
Granted. But is it possible to insert a new column in the middle of an
exiisting table without incurring at least SOME extra overhead? I doubt
it, but I'm willing to be proven wrong. How would your code look?
>And not everyone who adds a non-nullable column with ALTER TABLE may
>consider the possibility adding the column as nullable, and change it
>to NOT NULL later.
And some people delete their database, don't have a backup and spend
months retyping data from paper copies. I thought we were discussing how
high-availability DBs are impacted by wanting to place a column
somewhere other that at the end of the column list, not how human errors
and shortcomings can affect availability.
>
>There are also other interesting changes you can make.
Again - what relevance does this have to our discussion?
>> Nope, that was all. Quite enough for me. Can you imagine Google being
>> unresponsive for a few minutes because they are rebuilding their tables?
>
>I'm sorry, but what has this to do with anything? Changing tables in a
>live database with a high requirement of uptime is a complex business
>for which neither a simple update from EM or a plain ALTER TABLE is
>satisfactory. It takes very careful planning to implement.
And one of the things to consider during that stage is if the extra
downtime that would be incurred by placing the new column at the
desirneed spot is worth the benefit.
(snip)
>> Developers are not working with the data but with the metadata - but
>> they should not base their work on the RDBMS's reports of the metadata.
>> Developers should be working from the repository.
>
>And why should the repository have a different order from the database?
I don't say it SHOULD have a different order - but neither will I say
that it SHOULD NOT have a different order. If developers prefer to see
the orders in a logical order, then just go ahead and rearrange them in
the repository.
>If you read the documentation for a stored procedure, don't you expect
>the parameters to be listed in the order they appear in the argument
>list?
Huge difference - stored pprocedures can (and usually are in most
systems I have seen) be called with positional arguments instead of
named arguments. This requires knowledge of the order of arguments for
the coder who programs the procedure call. Tables should never be used
in a way where column order matters.
>And a developer does not only look in a repository. He also reviews data
>he has inserted or updated, and to this end he typically uses SELECT * or
>Open Table.
Or he types the relevant columns in the SELECT *. Or he changes the
column list and/or column order in the Open Table screen. Or he queries
a predefined view instead of the table. All these alternatives incur a
cost, as does planting a new column in the middle of a table - if this
is the reason for the request, the final cost/benefit analysis will have
to be made by the owner of the DB. If the answer to my question about
the reasons for wanting to change the column order indicates thhat this
is the reason, my answer will contain information about the costs and
risks, so that either the persoons asking the question or his boss can
make an informed decision.
>
>Furthermore, developers are not the only persons who engage in this
>activity. There is support staff as well. (Which in our small shop, very
>well can be a developer, by the way.) You have a customer on the phone
>asking why the operation he is trying to carry out does not give the
>expected result. You suspect that someone might have changed some basic
>data. Quickly you can type SELECT * FROM tbl. You can then scroll to the
>far right, because that's where the auditing columns always are in our
>tables. When the table has 60+ columns, I can tell you will appreciate
>that.
Imagine a view that includes only the key columns and the auditing
columns and nothing else - they might appreciate that even more!
(snip)
>Beside that, I like to remind you that the cost of changing column order
>today, is only due to a defect in the product, not that the operation itself
>is inherently expensive.
I don't consider the absense of this option to be a defect in the
product. This functionallity would be a "nice to have" at the very best,
and it should be way down on the priority list of Microsoft's SQL Server
development team.
Steve Dassin
on column position seemingly ignore the plethora of
places where 'ORDER' is mandated but where it is obviously
inconsistent in a relational db?Talk about consistency!
Dealing with an expedient product and using the hunt and
peck method of underscoring what those aspects of 'relational theory'
can be applied to it is well...expedient.Did someone mention
the idea of hypocrisy?
For those that don't follow,the evilness of 'SELECT * FROM ORDERS'
in server is evil by its implementation,an expedient consideration.
To argue that it is relationally untoward is like arguing that
it is possible to be 'a little bit' pregnant.Like pregnancy, you
are or you aren't.The statement is silly, with or without the
columns,in any order.
Mr. Sommarskog may be asking for something that is contrary to
best principles.But the 'idea' of column position,should it be
explicitly implemented in server, will somehow invalidate
servers 'best principles' is laughable.And to those that think
by enforcing a standard that omits '*',that this elevates them
to a 'higher' relational standard in server..well..it is so
much drivel:)
Erland Sommarskog
> Not a good analogy, IMO. First, I can't be equated to the salesman,
> because I don't sell the car (the DBMS).
So what. Change the salesman to someone else who happen to know that
the brand X does not make red cars.
> But an even more important difference is that the color of a car is only
> a cosmetic feature that some people find important while other people
> care less. Not so for column order - some people do in fact realise that
> changing the order is a mere cosmetical operation, but many people think
> they need it for a different reason.
Or you think that they think so, and you must insult them by questioning
their requirements by assuming that they have illegit grounds for the
question. Until you know otherwise, there is no reason to assume that
people are asking for this on a nice-to-have business.
> I would mainly question the MS SQL Server design team's motive for
> choosing to add this extension to the ALTER TABLE syntax instead of
> spending their development time on more relevant matters.
Currently there are 14 items for SQL Server on MSDN Product Feedback
Centre that has attracted more than 20 votes. One of them is about ALTER
TABLE syntax for column order. I think that if the SQL Server team works
on matters for which there is a high customer demand, they are working
on the right things.
> Yes, there is. If I don't ask why they want to do this, how can I offer
> alternative suggestions? For a user who wants to do this because he
> wants his INSERT INTO Table01 SELECT * FROM Table02 to work
While that sort of code is bad practice, the fact that you want to be able
to do this is a strong indication of that two tables should have the same
column order. To wit, apparently the table describe the same entity, and
it would only be confusing if they have different order.
> I don't say it SHOULD have a different order - but neither will I say
> that it SHOULD NOT have a different order. If developers prefer to see
> the orders in a logical order, then just go ahead and rearrange them in
> the repository.
Repository? I'm sorry, but this is just plain nonsense. I don't run
SELECT * to see that my data was inserted/updated correctly against
any repository, I do it against the database.
> Imagine a view that includes only the key columns and the auditing
> columns and nothing else - they might appreciate that even more!
And in the next moment, there are some more columns that I want to review.
So much the use for that view.
Erland Sommarskog
> Generally, people who claim to professionally work on certain field are
> expected to know some fundamentals of the field. So why is it insulting
> when challenged on the basics in the understanding or appreciation of
> relational model's prohibition on column ordering?
I didn't know that the relational model prohibits column ordering. And
I've been working with implementing table changes for many years. But I
think see your problem:
> The point is that forcing column ordering offers nothing beneficial to
> existing DBMS functionality, but may lead to negative consequences to a
> myriad of associated utilitarian functionalities. Given that few in the
> field are generally aware of the need of referencing columns by names
> rather than their positions, it helps to question their intentions,
> correct any misunderstandings and offer any potential suggestions.
Aha! So you don't want to change column order, because you are afraid of
that too many thinks would break!
Thus, the real story is not that column order does not matter. The real
story for you is that column order matters so much that it's dangerous to
change it!
Raymond D'Anjou
I know... column ordering is not important and shouldn't be.
If so, why do all (I use ALL loosely here) databases keep specific column
ordering?
Why doesn't SQL server return columns ordered alphabetically when using
Select * or when opening the table in EM design mode? (then we wouldn't be
having this long-winded post)
Since the order is stored internally, why don't we have a procedure to
change the ordering without recreating the table?
Does SQL server make it difficult because they know a lot of code would
break if it was too simple to change ordering?
In that case, why is it so simple to do in EM?
"Anith Sen" <an...@bizdatasolutions.com> wrote in message
news:OXfYO$GeGHA...@TK2MSFTNGP05.phx.gbl...
Aaron Bertrand [SQL Server MVP]
The physical operation the user has to do is relatively simple. Drag, drop,
save. The actual set of operations that EM does in the background for you
is horrifying. Have you tried running profiler while performing this
operation? It is a very enlightening experience, and I highly recommend it.
Just don't try it on a large table.
Jim Underwood
only, one that would have no relation to the physical database storage, but
allow the DBA/Developer to see a table with a logical column order. The
problem with this, is that so many folks rely on column order for select *
or insert statements, that it would cause a rediculous amount of problems.
A syntax for adding a column in a specific location would be nice, as it
would give this logical view so developers and DBAs could more easily
understand the data they are looking at.
The problem is that most database systems store the columns physically in
the order they appear in the table. True, this goes against proper DB
theory, but the data must be physically stored somehow, and somewhere along
the line someone decided this was the most efficient and logical way to
physically store the data.
If column order is changed, then the physical storage changes to match,
because this is the way database products are built. Allowing a column to
be inserted in a specific location would cause all sorts of storage issues
due to the way the systems work. The syntax would be simple to create, but
the implementation would likely require significant rework to the way the
RDBMS handles the data.
Time has been noted as an issue, but no one has mentioned the storage aspect
of these changes. When you recreate a table in order to force column order,
you need enough space to add the new table. Then you drop the old table,
often creating a lot of fragmented space for every extent of the old table's
storage. I'm not certain how SQL Server handles this space, but I know when
I have done this in Oracle (recreating dozens of tables, with many GB of
data) you had to examine your storage and insure that you had enough extra
space to handle all of the new tables at once, because you could not
guarentee that you could reuse the old space freed up when you dropped the
original tables. There is a huge amount of overhead involved in this, much
more than simply adding a column at the end.
This is not to say that it should not be done, but it is just one more issue
that one needs to be aware of before doing this. It is up to the developer
and the DBAs to determine if the column order is worth all this or not. If
you understand the issues and still choose to do it, then by all means, do
it. If you don't understand the issues, someone had better explain them, or
when you go to run the scripts in production you could very well end up
having to do a full restore of the database (thankfully I learned this
lesson in test, not production) because scripts that ran fine in one
environement failed horribly in another.
"Raymond D'Anjou" <rda...@canatradeNOSPAM.com> wrote in message
news:%23LJeHHP...@TK2MSFTNGP05.phx.gbl...
Raymond D'Anjou
I've seen the script generated when doing this on "complexe" tables.
So simple to do but so convoluted behind the scenes.
Typical example of a "got you".
"Aaron Bertrand [SQL Server MVP]" <ten...@dnartreb.noraa> wrote in message
news:OMi8eLPe...@TK2MSFTNGP03.phx.gbl...
Erland Sommarskog
> This is not to say that it should not be done, but it is just one more
> issue that one needs to be aware of before doing this. It is up to the
> developer and the DBAs to determine if the column order is worth all
> this or not. If you understand the issues and still choose to do it,
> then by all means, do it. If you don't understand the issues, someone
> had better explain them, or when you go to run the scripts in production
> you could very well end up having to do a full restore of the database
> (thankfully I learned this lesson in test, not production) because
> scripts that ran fine in one environement failed horribly in another.
se, but has to with implementing changes to a live database. This is a very
complex and difficult operation, that requires careful planning.
True, if you for one reason opt to reload a table than using ALTER syntax,
you increase the complexity of the operation. However, just as it can be
fatal to make the change from EM and press save, it can be just as fatal
to say ALTER TABLE to add a fixed-length column with a default value on
a large table for which there is an availability requirement.
And all this have very little to do with column order per se.
Erland Sommarskog
> I know... column ordering is not important and shouldn't be.
> If so, why do all (I use ALL loosely here) databases keep specific column
> ordering?
> Why doesn't SQL server return columns ordered alphabetically when using
> Select * or when opening the table in EM design mode? (then we wouldn't be
> having this long-winded post)
I think you make some very good points, Raymond. When you read this thread
and scrutinze the arguments of those who yell that column order should not
matter, the real issue appears to be that column order matters so much
that it's dangerous to change it.
> Since the order is stored internally, why don't we have a procedure to
> change the ordering without recreating the table?
> Does SQL server make it difficult because they know a lot of code would
> break if it was too simple to change ordering?
I guess that the reason you easily cannot change column order with a
system procedure or DDL, is that they have not come around to it. Up
to SQL 6.5 the only thing you could do with ALTER TABLE was to add a
nullable column or an IDENTITY column. (And the last thing performed badly.
I tried it once on 350000 rows table. After 15-20 minutes I restarted
SQL Server, bulked out the data, ran a Perl script on the BCP file,
recreated the table and shoved it back in. In less than 15 minutes.)
> In that case, why is it so simple to do in EM?
I guess because it's a natural operation to perform in a GUI. The data-
modelling tool that I use, PowerDesigner, also permit me to rearrange
columns, and I would expect that other major tools such as ERwin and
Embrocadero as well.
Another story is that pressing Save from a data-modelling tool to run
the change in a live database is an extremely poor idea, no matter how
the change is implemented. In case of Enterprise Manager and Management
Studio, one should also be aware of that the table-reload scripts they
create has a couple very serious flaws.
Erland Sommarskog
> Time has been noted as an issue, but no one has mentioned the storage
> aspect of these changes. When you recreate a table in order to force
> column order, you need enough space to add the new table. Then you drop
> the old table,
Yes, but keep in mind that this also applies if you say
ALTER TABLE tbl ADD newcol int NOT NULL DEFAULT 98
SQL Server will essentially have move all rows to accodomate for the new
column. (Although there are some possible shortcuts that are not available
when you create a new table. Specifically, non-clustered indexes are not
affected, if the table has a clustered index.)
Anith Sen
>> that column order should not matter, the real issue appears to be that
>> column order matters so much that it's dangerous to change it.
Well, let me see if this can be clarified with emphasis on two distinct
points.
#1. Good data management principles suggest ignoring column order in base
tables, derived tables, view and all logical data manipulation operations.
This is due to the fact that positional significance of columns results in
physical dependence -- it biases logical representation, diminishes its
generic utility and makes the expressiveness and optimization of certain
queries difficult.
#2. However, SQL requires column ordering in some of its logical operations
(like UNION, OUTER JOINS etc.) and other language constructs like views,
derived tables, triggers etc. Also, certain issues like lack of closure,
nested references etc. cause some limitations to quick changes to existing
schema without user involvement. Moreover, it amplifies the problem by not
sufficiently separating the logical ordering of columns view its physical
ordering. Thus, SQL products cannot implement all physical level changes
without affecting the logical representation and vice versa. Changing
logical order of columns in a table exemplifies this issue.
Due to #1, which is a generic reason, since we should strive for physical
data independence, logical operations should avoid relying on column
ordering. It brings nothing useful to the arena of data management.
Due to #2, which is a product/language specific reason, we consider limiting
changes to structures that are already physically dependant. Moreover the
nature of the language itself, introduces lot of additional complexity that
we have to deal with.
When someone posts a question asking how to change column order, the chances
are he is unaware of #1. So we tell him/her that it is generally not useful.
Since we already know the potential difficulties involved in such a change,
we suggest him not to do it, detailing the implementation issues due to #2.
Having a well defined syntax to change the column order makes little
difference to either #1 or #2. But it can be misunderstood by many who are
unaware of #1 as a license for shuffling columns and its implementation can
be difficult due to issues related to #2.
My predilection would be to focus our efforts elsewhere.
--
Anith
Jim Underwood
would be physically stored.
It is good to know that SQL Server is claiming new space for the existing
columns rather than just using pointers to the new column.
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns97C6AD8D3...@127.0.0.1...
Erland Sommarskog
> #1. Good data management principles suggest ignoring column order in base
> tables, derived tables, view and all logical data manipulation operations.
> This is due to the fact that positional significance of columns results in
> physical dependence -- it biases logical representation, diminishes its
> generic utility and makes the expressiveness and optimization of certain
> queries difficult.
Agreed.
(Although the physical column is not really that interesting. In SQL
Server the physical order is usually somewhat different from the column
order in the CREATE TABLE statement, since all fixed-length columns
comes before all variable-length columns.)
> Due to #1, which is a generic reason, since we should strive for physical
> data independence, logical operations should avoid relying on column
> ordering. It brings nothing useful to the arena of data management.
It could be interesting to see exactly which operations where column
order matters for processing:
1) SELECT * - we all agree that is is baaaad in application code. I would
however like to qualify this a bit. There are some contexts where
SELECT * probably is defensible: a) in the definition of a partitioned
view (where all tables should be alike, and repeating the column list
in the view definition introduces a source of error). b) SELECT *
from a temp table/table variable created in the same procedure.
c) maybe in ultra-low-end applications where data is presented in
Excel or similar, and the total development effort is < 100 h.
To this comes of course all ad hoc queries that you run in test
or support situations, and where the code is not application code.
2) INSERT without a column list. Really baaaaaaad!
3) ORDER BY 1, 2 - mainly harmless, but I mainly use in ad hoc-queries
when I'm lazy (and not with SELECT *!)
4) columns_updated() - a feature which is really unrobust, and I can
easily resist using it.
5) Bulk-copy operations. This is probably the most significant area in
SQL Server, as up to SQL 2000 there was no way to avoid relying on
column order. It is possible in SQL 2005 (I think) if you use an
XML format file. But the nature of BCP is such is that if don't use
a format file if you can avoid it.
> When someone posts a question asking how to change column order, the
> chances are he is unaware of #1. So we tell him/her that it is generally
> not useful. Since we already know the potential difficulties involved in
> such a change, we suggest him not to do it, detailing the implementation
> issues due to #2.
I guess that this is where we disagree. I figure that people who actually
use SELECT * in their code - and we know a lot of people do! - would be
less prone to change the column order. Of course, they could
be in the situation that two databases has the same tables, but with
different column order. But the question is usually about adding a
new column order.
So my assumption is that people who ask for this just want this, because
it makes their work easier. Maybe they are really doing an ultralow-end
application. Maybe they are just in the early design stage, and just
fiddlng around.
It is worth noting that if column order does not matter for the
relational processing as such, then ordering columns so that related
data appears together when you run ad hoc-queries, PK columns to
the left, audit columns to the right, is perfectly harmless.
rusty wallace
The answer is yes and no. You can recreate the data from the table by
using the original table as reference, but it's improbable that in a
business environment you would ever do this with and enormous database
because you would/could crash the server. Also, if I understand
correctly, this method can disrupt linkage and foreign keys.
I've only been using SQL for about 3 days, so what do I know...
If you're just fooling around with it like me, this is probably all you
need; wanting to just reorganize the data to make debugging a little
easier for you to read.
SQL> create table test (col1 number, col2 number, col3 number);
Table created.
SQL> describe test; Name Null?
Type ----------------------------------------- --------
------------------- COL1
NUMBER COL2 NUMBER COL3
NUMBER
SQL> create table test2 as select col3, col2, col1 from test;
Table created.
SQL> drop table test;
Table dropped.
SQL> alter table test2 rename to test;
Table altered.
SQL> describe test; Name Null?
Type ----------------------------------------- --------
------------------- COL3
NUMBER COL2 NUMBER COL1
NUMBER
SQL>
*** Sent via Developersdex http://www.developersdex.com ***
SQL Guru
Both sides of this "debate" are completelty valid, so no one should feel
that their opinion is either right OR wrong. It is a perfectly valid
desire to have columns, not views, in an order that makes sense to the
application or developer (i.e. alpha, by data type, index order, etc.).
It is also perfectly correct to say ordinal position is utterly
irrelevant technically.
Edgar (Codd) was a dear friend, and I am confident in the belief that
he would have appreciated improvements to his ideas, including defining
ordinal positions easily and dynamically. The "relational algebra"
behind his magnum opus in no way makes such a request illogical.
Ironically the answer to this is extremely simple, and no the solution
does not involve updating system tables (bad idea), or require table
rebuilding and loading (slow!!). Our team has done it for years, and
yes, our technical customers appreciate the results. Customers,
afterall, are the only thing that truly matter in such debates!
Cheers!
Raymond D'Anjou wrote:
> *I agree.
> In developement, I create my table columns in a "logical" order.
> Once the database is in production though, if I have another column
> to add,
> to the bottom it goes.
> Erland seems to believe strongly in keeping that "logical" order.
> Without using EM though, it's a pain adding a column in a specific
> place (I
> wonder how Erland does it).
> If that table has a couple of million rows or more, the pain is only
> worse.
>
> "Anith Sen" <an...@bizdatasolutions.com> wrote in message
> news:Omazoged...@TK2MSFTNGP05.phx.gbl...
> >
> > Nice logic! Is that how, in your view, inferences are drawn from
> existing
> > premises ?
> >
> > Column order is insignificant does not mean columns must be
> randomly
> > ordered. It simply emphasizes that there is no absolutely need to
> tie any
> > integrity or manipulation logic to column positions.
> >
> >
> > Some do, some don't.
> >
> >
> > I know several who don't. I am sure several others here too. Unless
> there
> > is an explainable physical reason, esp. with indexes and/or space
> > consumption, many don't.
> >
> >
> > Simply put, it offers nothing useful. What integrity benefits does
> it
> > provide? Any structural advantages? How about manipulative
> advantages?
> > Any? So why?
> >
> > Why do we have to assume everyone out there tend to think of
> tables
> > displayed on a computer screen to be formatted like spreadsheets?
> The only
> > argument I have seen from you before is "easy to document"/diagram
> and
> > "easy to read on a computer screen" which are trivial considering
> overall
> > advantages of ignoring the positional significance of columns.
> >
> > --
> > Anith
> > *
--
SQL Guru
------------------------------------------------------------------------
Posted via http://www.codecomments.com
------------------------------------------------------------------------
J.B. Moreno
> Another story is that pressing Save from a data-modelling tool to run
> the change in a live database is an extremely poor idea, no matter how
> the change is implemented. In case of Enterprise Manager and Management
> Studio, one should also be aware of that the table-reload scripts they
> create has a couple very serious flaws.
Could you expand upon that? I was thinking of letting EM generate the
script for me based on changing dev, and then having it applied to
production when the DB wasn't being used.
(I know it's a really late followup, but I couldn't find any mention of
problems with their reload scripts other than your post, maybe my
search skills are off today).
--
J.B. Moreno
Erland Sommarskog
As I mentioned, there are a number of serious bugs with the script the
EM generates. But I think it's safe, if you apply the following steps:
1) First review that the script, includes only the changes you intend
to make. There are situations where EM can include changes that you
decided to abandon.
2) Remove all BEGIN and COMMIT TRANSACTION except for the first BEGIN
and the last COMMIT. EM uses too short transactions, which can leave
your database in a state where constraints are missinga and the like.
3) Remove all "go" in the script. But add first a batch of "SET XACT_ABORT
ON", and a final batch (precdeed by go): "IF @@trancount > 0 ROLLBACK
TRANSACTION.
4) Put all statements that are depending of previous DDL in
EXEC(). This is about everthing in the script, except calls to
sp_rename.
5) Change all WITH NOCHECK to WITH CHECK. Else constraints are re-
enabled/re-added without being checked, which means that the
optimizer cannot trust them.
Also beware that EM generates most changes with creating a new version
of the table, and copying over data. Sometimes this is the best, or
even the only way, but there are also plenty of situations where ALTER
TABLE is far more painless. EM only uses ALTER TABLE when it would be legal
in SQL 6.5.
Note that all these issues applies to Management Studio as well.
J.B. Moreno
Erland Sommarskog <esq...@sommarskog.se> wrote:
> As I mentioned, there are a number of serious bugs with the script the
> EM generates. But I think it's safe, if you apply the following steps:
Thanks. For documentary reasons (particularly for those that may come
after me), I think it's best to have the column order be logical and
consistent, but I didn't want to destroy the database in an attempt to
make it easier to understand...
--
J.B. Moreno
prashant kotwal
but hope this will help u..
alter table tblAccountsMaster add strDeptCode12 nvarchar(10)
select
strAccountCode,strAccountName,strOperational,strType,strDeptCode12,
strTaxonPurchase,strRRGRN,strEmployee,strTaxonSales,strDebtor,strGroupCo
de,
intPreYearGrpCode,intMSGrpCode,strCashflowCode,strDeptCode,strBranch,str
UserCreated
dtCreatedDate,strUserModified,dtLastModified
into tblAccountsMaster12 from tblAccountsMaster
drop table tblAccountsMaster
select * into tblAccountsMaster from tblAccountsMaster12
drop table tblAccountsMaster12
select * from tblAccountsMaster
Mike C#
"prashant kotwal" <prashant...@gmail.com> wrote in message
news:eDmekfah...@TK2MSFTNGP04.phx.gbl...
There is no syntax for adding a column to a table in a specific ordinal
position. A new table must be created, populated from the old table, and
the old table dropped and recreated, then the data copied back, as you did
here.
Mark Lane
you might need your columns to appear in a specific order in the table.
Use the "AFTER" keyword... for example "ALTER TABLE `Blah` ADD COLUMN
`Blahh` ... AFTER `Blahhh`"
Aaron Bertrand [SQL Server MVP]
> you might need your columns to appear in a specific order in the table.
> Use the "AFTER" keyword... for example "ALTER TABLE `Blah` ADD COLUMN
> `Blahh` ... AFTER `Blahhh`"
I think you said "need" but meant to say "want"... anyway have you tried
your proposed syntax on SQL Server?
Mariano Gomez
the benefit of having columns in a certain order in a table? Is there any
scientific or mathematical reasons that would lead to believe the query
optimizer will behave differently?
Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com
Hugo Kornelis
>I am a Dynamics GP MVP, not a SQL Server one, but for my own benefit, what is
>the benefit of having columns in a certain order in a table? Is there any
>scientific or mathematical reasons that would lead to believe the query
>optimizer will behave differently?
Hi Mariano,
No, there is absolutely no difference for the optimizer, or for any
other part of the database. The only reason why people want to have
columns in a different order is to simplfy understanding of the output
of a SELECT *.
The "bad" reason to want this, is when SELECT * is used in production
code and the client references columns by position instead of by name.
This is actually two bad practices in one... :)
The "good" (well, sort of) reason to want this is that troubleshooting
often calls for quick inspection of the contents of a table. I am guilty
of using SELECT * or Rightclick / Open table for quickly checking the
contents of tables myself. And though I have no problem finding the
columns I need, or typing them by hand if they really are swamped, I do
understand how it would be convenient for some people to have the
columns in some logical order.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Erland Sommarskog
> I am a Dynamics GP MVP, not a SQL Server one, but for my own benefit,
> what is the benefit of having columns in a certain order in a table? Is
> there any scientific or mathematical reasons that would lead to believe
> the query optimizer will behave differently?
If you buy a new CD, where you put it? At the end of the CD pile, or you
insert it for instance by order, so you can find it more easily later?
There are plenty of good reasons why should be able to put a column
on a desired place in a table. Else, why do all data-modelling tools
give you that option?
When you start working with a new database documentation, do you want to
see the columns documented in the order they were added through the years?
Or would you prefer an order that gave a logical flow to the description?
For instance, with the PK columns first?
Once you have read the documentation, and start to play around in the
system and you do a couple of SELECT *, would you like to see columns
in a different order than in the documentation?
No, for performance, science and mathematics column order does not matter,
but for people it does.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
--CELKO--
This depends on your SQL product. The optimizers do not care. But in
older versions of DB2 you got better storage allocations with the
VARCHARs at the end of the table declaration. I cannot remember the
details, but Craig Mullins (www.craigsmullins.com) mentioned this to
me when he did a read of one of my manuscripts. But apparently this
did not matter until you get a huge DB.
Tony Rogerson
with the OS the order of columns in the table did matter.
That was back in the late 80's / early 90's.
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:955a68a9-b04b-48f2...@w1g2000prk.googlegroups.com...
Mariano Gomez
query optimizer, I could see your point from a pure organizational and data
retrieval stand point.
Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com
Mariano Gomez
Thanks for the explanation. I can see how having columns organized in a
logical manner facilitate quick reading when visually inspecting data.
Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com
omnia kahla
Aaron Bertrand [SQL Server MVP]
> think the "INSERT INTO <TABLE> VALUES(..)"
> needing the columns in a particular order makes it worth to have an
> "INSERT COLUMN AFTER SOMECOLUMN" feature via SQL.
You should never rely on this lazy construct. If you write a statement that
assumes the order of columns, what happens to your statement when some joker
adds a new column in the middle? Your VALUES() list no longer matches the
table definition. At least if new columns go at the "end" (and are either
nullable or have a default constraint), a properly constructed INSERT
statement will continue to function.
> For instance, in access database that is a simple operation you can do
> using the GUI.
You can do it using the UI in SQL Server too. However it drops the table
and re-creates it. That should be fun if your table has 50b rows.
> So dont you think it would be nice to have the feature of being able to
> add columns in a particular order?
No. It is better to construct your INSERT statements properly (in which
case the columns can be listed in any order).
A
Pablo Pico
think the "INSERT INTO <TABLE> VALUES(..)"
needing the columns in a particular order makes it worth to have an
"INSERT COLUMN AFTER SOMECOLUMN" feature via SQL.
For instance, in access database that is a simple operation you can do
using the GUI. One time i had to drop a column and add it again to the
table becuase there was no other way to to swithcing from "NOT NULL" to
"NULL" using ALTER COLUMN.
Then i realized that all my sql insert intos were sensitive to the
column order (Yes i know you would consider it a mistake) but it was
actually the very first time i had trouble with it since i did not have
access to the database using the microsoft access software but only SQL.
So i had to rewrite all my insert intos after realizing that the other
way was to duplicate the columns, copy the data, drop the columns, and
add them again in the particular order, and of course drop and add all
the constraints again...
So dont you think it would be nice to have the feature of being able to
add columns in a particular order?
Why are we able to write insert into instruction without telling the
column order when we have no good control over it via SQL?
It should not be that complicated to insert a column!