Alter Table - Add Colum - Ordinal Position?

I am using SQL Server 2000. As part of a new development project I find that
I frequently need to add new columns/fields to existing tables using SQL
Scripts in Query Analyzer. I would like to add the new column at a specific
Ordinal Position but when I use the standard Alter Table Add ... syntax for
adding a new column the column always gets added to the end of the table.
Accomplishing this task is relatively easy using Enterprise Manager, you
simply select the column where you want the new column to be placed and click
"Insert" and the new column gets inserted above the selected column. Is
there a way to add the column in a specific ordinal location from Query
Analyzer? If so, what is the syntax to make this happen. For example, I
want to add a new column ([columntwo] [int] NULL,) between [columnone] and
[columnthree] to the table layout below:
ColumnOne
ColumnThree
ColumnFour
Using standard "alter table' syntax [columntwo] get added to the bottom.
ColumnOne
ColumnThree
ColumnFour
ColumnTwo
I want it to look like this:
ColumnOne
ColumnTwo
ColumnThree
ColumnFour
I realize I can simply create a new table with the properly ordered fields,
copy the data over to the new table and then drop the existing table but this
is approach is not optimal.
--
So Much - Yet - So LittleTry running Profiler when you insert the column in Enterprise Manager. I
think you will find it does exactly what you said you don't want to do -
creates a new table, inserts the data from the old table, and then droops
the old table. The right thing to do here is to write your SQL statements
to not depend on a particular column order so that you can use Alter Table.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"SwingVoter" <swingvoter@.nospam.nospam> wrote in message
news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>I am using SQL Server 2000. As part of a new development project I find
>that
> I frequently need to add new columns/fields to existing tables using SQL
> Scripts in Query Analyzer. I would like to add the new column at a
> specific
> Ordinal Position but when I use the standard Alter Table Add ... syntax
> for
> adding a new column the column always gets added to the end of the table.
> Accomplishing this task is relatively easy using Enterprise Manager, you
> simply select the column where you want the new column to be placed and
> click
> "Insert" and the new column gets inserted above the selected column. Is
> there a way to add the column in a specific ordinal location from Query
> Analyzer? If so, what is the syntax to make this happen. For example, I
> want to add a new column ([columntwo] [int] NULL,) between [columnone] and
> [columnthree] to the table layout below:
> ColumnOne
> ColumnThree
> ColumnFour
> Using standard "alter table' syntax [columntwo] get added to the bottom.
> ColumnOne
> ColumnThree
> ColumnFour
> ColumnTwo
> I want it to look like this:
> ColumnOne
> ColumnTwo
> ColumnThree
> ColumnFour
> I realize I can simply create a new table with the properly ordered
> fields,
> copy the data over to the new table and then drop the existing table but
> this
> is approach is not optimal.
> --
> So Much - Yet - So Little|||Hi,
How many rows there in your table?
Creating a new table and dropping old one is only optimal option. This can
be achieved through Enterprose Manageralso , right click the table => choose
"Design Table" and drag and drop the new cloumn created by you from the end
position to desired Ordinal Position. But go for this only if the table rows
are only a few hundreds or else it will hang.
Thanks,
Sree
"Roger Wolter[MSFT]" wrote:
> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
> >I am using SQL Server 2000. As part of a new development project I find
> >that
> > I frequently need to add new columns/fields to existing tables using SQL
> > Scripts in Query Analyzer. I would like to add the new column at a
> > specific
> > Ordinal Position but when I use the standard Alter Table Add ... syntax
> > for
> > adding a new column the column always gets added to the end of the table.
> > Accomplishing this task is relatively easy using Enterprise Manager, you
> > simply select the column where you want the new column to be placed and
> > click
> > "Insert" and the new column gets inserted above the selected column. Is
> > there a way to add the column in a specific ordinal location from Query
> > Analyzer? If so, what is the syntax to make this happen. For example, I
> > want to add a new column ([columntwo] [int] NULL,) between [columnone] and
> > [columnthree] to the table layout below:
> > ColumnOne
> > ColumnThree
> > ColumnFour
> >
> > Using standard "alter table' syntax [columntwo] get added to the bottom.
> > ColumnOne
> > ColumnThree
> > ColumnFour
> > ColumnTwo
> >
> > I want it to look like this:
> > ColumnOne
> > ColumnTwo
> > ColumnThree
> > ColumnFour
> >
> > I realize I can simply create a new table with the properly ordered
> > fields,
> > copy the data over to the new table and then drop the existing table but
> > this
> > is approach is not optimal.
> > --
> > So Much - Yet - So Little
>
>|||Hi Roger,
ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
mysql does is there any option in Sql Server 2005.
Thanks,
Sree
"Roger Wolter[MSFT]" wrote:
> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
> >I am using SQL Server 2000. As part of a new development project I find
> >that
> > I frequently need to add new columns/fields to existing tables using SQL
> > Scripts in Query Analyzer. I would like to add the new column at a
> > specific
> > Ordinal Position but when I use the standard Alter Table Add ... syntax
> > for
> > adding a new column the column always gets added to the end of the table.
> > Accomplishing this task is relatively easy using Enterprise Manager, you
> > simply select the column where you want the new column to be placed and
> > click
> > "Insert" and the new column gets inserted above the selected column. Is
> > there a way to add the column in a specific ordinal location from Query
> > Analyzer? If so, what is the syntax to make this happen. For example, I
> > want to add a new column ([columntwo] [int] NULL,) between [columnone] and
> > [columnthree] to the table layout below:
> > ColumnOne
> > ColumnThree
> > ColumnFour
> >
> > Using standard "alter table' syntax [columntwo] get added to the bottom.
> > ColumnOne
> > ColumnThree
> > ColumnFour
> > ColumnTwo
> >
> > I want it to look like this:
> > ColumnOne
> > ColumnTwo
> > ColumnThree
> > ColumnFour
> >
> > I realize I can simply create a new table with the properly ordered
> > fields,
> > copy the data over to the new table and then drop the existing table but
> > this
> > is approach is not optimal.
> > --
> > So Much - Yet - So Little
>
>|||I don=B4t know why you need ordinal positions. Since youcan use the name
of a column picking the columns by the ordinal positions should not be
needed anymore, if you use a view for displaying the data or a
selection string,you can "reorder" the columns on your own, on the fly.
EM does this odd drop and create for alter, using QA does not, so if
there are many rows in your table use QA for this, rather than EM.
HTH, Jens Suessmeyer.|||Sreejith G wrote:
> Hi Roger,
> ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
> mysql does is there any option in Sql Server 2005.
> Thanks,
> Sree
>
No, there is no such option in SQL Server 2005.
As Roger explained, if you apply good practices like never using SELECT
* in your code then physical column order is relatively unimportant. As
far as end users are concerned, column order is determined by the
column list in a SELECT statement, not by the physical placement on
disc.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||:) you guys are right... there is no need of ordnial position. But just think
of a design with 1000+ tables. And having employees data in a table with
ordinal position,
1.Created Date
2.EmpFirstName
3.EmpLastName
4.UpdatedDate
5.DOB
6.Sex
7.EmpMiddleName
8.Location
And on the run you added few feilds and messed up all your tables like this.
In future a datawarehouse analyst might try to map OLTP feilds to OLAP design
to satisfy kep performance indicators and they might get blown away seeing
such positioning of table feilds.
Always below one make you feel good :)..
1.EmpFirstName
2.EmpMiddleName
3.EmpLastName
4.DOB
5.Sex
6.Location
7.Created Date
8.UpdatedDate
Thanks,
Sree
"Jens" wrote:
> I don´t know why you need ordinal positions. Since youcan use the name
> of a column picking the columns by the ordinal positions should not be
> needed anymore, if you use a view for displaying the data or a
> selection string,you can "reorder" the columns on your own, on the fly.
> EM does this odd drop and create for alter, using QA does not, so if
> there are many rows in your table use QA for this, rather than EM.
> HTH, Jens Suessmeyer.
>|||:) you guys are right... there is no need of ordnial position. But just think
of a design with 1000+ tables. And having employees data in a table with
ordinal position,
1.Created Date
2.EmpFirstName
3.EmpLastName
4.UpdatedDate
5.DOB
6.Sex
7.EmpMiddleName
8.Location
And on the run you added few feilds and messed up all your tables like this.
In future a datawarehouse analyst might try to map OLTP feilds to OLAP design
to satisfy kep performance indicators and they might get blown away seeing
such positioning of table feilds.
Always below one make you feel good :)..
1.EmpFirstName
2.EmpMiddleName
3.EmpLastName
4.DOB
5.Sex
6.Location
7.Created Date
8.UpdatedDate
Thanks,
Sree
"Jens" wrote:
> I don´t know why you need ordinal positions. Since youcan use the name
> of a column picking the columns by the ordinal positions should not be
> needed anymore, if you use a view for displaying the data or a
> selection string,you can "reorder" the columns on your own, on the fly.
> EM does this odd drop and create for alter, using QA does not, so if
> there are many rows in your table use QA for this, rather than EM.
> HTH, Jens Suessmeyer.
>|||> ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
> mysql does is there any option in Sql Server 2005.
You can submit this product enhancement request directly at
http://lab.msdn.microsoft.com/productfeedback/. Also specify the business
case for why this feature is important to you.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:1AD84F7E-9603-48DF-812E-D332EC40DC49@.microsoft.com...
> Hi Roger,
> ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
> mysql does is there any option in Sql Server 2005.
> Thanks,
> Sree
> "Roger Wolter[MSFT]" wrote:
>> Try running Profiler when you insert the column in Enterprise Manager. I
>> think you will find it does exactly what you said you don't want to do -
>> creates a new table, inserts the data from the old table, and then droops
>> the old table. The right thing to do here is to write your SQL
>> statements
>> to not depend on a particular column order so that you can use Alter
>> Table.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
>> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>> >I am using SQL Server 2000. As part of a new development project I find
>> >that
>> > I frequently need to add new columns/fields to existing tables using
>> > SQL
>> > Scripts in Query Analyzer. I would like to add the new column at a
>> > specific
>> > Ordinal Position but when I use the standard Alter Table Add ...
>> > syntax
>> > for
>> > adding a new column the column always gets added to the end of the
>> > table.
>> > Accomplishing this task is relatively easy using Enterprise Manager,
>> > you
>> > simply select the column where you want the new column to be placed and
>> > click
>> > "Insert" and the new column gets inserted above the selected column.
>> > Is
>> > there a way to add the column in a specific ordinal location from Query
>> > Analyzer? If so, what is the syntax to make this happen. For example,
>> > I
>> > want to add a new column ([columntwo] [int] NULL,) between [columnone]
>> > and
>> > [columnthree] to the table layout below:
>> > ColumnOne
>> > ColumnThree
>> > ColumnFour
>> >
>> > Using standard "alter table' syntax [columntwo] get added to the
>> > bottom.
>> > ColumnOne
>> > ColumnThree
>> > ColumnFour
>> > ColumnTwo
>> >
>> > I want it to look like this:
>> > ColumnOne
>> > ColumnTwo
>> > ColumnThree
>> > ColumnFour
>> >
>> > I realize I can simply create a new table with the properly ordered
>> > fields,
>> > copy the data over to the new table and then drop the existing table
>> > but
>> > this
>> > is approach is not optimal.
>> > --
>> > So Much - Yet - So Little
>>|||From a programming and data usability perspective you are correct. There is
no programming need for ordinal position however we have some tables with a
lot of fields and we want to keep "like" fields together for ease of mapping
and ease of visual recognition. It is much more difficult for a designer to
determine missing entities from the layout if the fields are scattered in an
non-logical order. As you can see from the list below in may cases simply
using a system table view and ordering by [name] is not helpful because the
similiar fields may not sort in that manner. It seems that there would be a
way to simply change the ordinal position in one of the master table as part
of the alter table script. Has anyone used this approach.
1.FirstName
2.MiddleName
.
7.City
8.State
Alter Table Add [LastName]
9.LastName
So Much - Yet - So Little
"Sreejith G" wrote:
> :) you guys are right... there is no need of ordnial position. But just think
> of a design with 1000+ tables. And having employees data in a table with
> ordinal position,
> 1.Created Date
> 2.EmpFirstName
> 3.EmpLastName
> 4.UpdatedDate
> 5.DOB
> 6.Sex
> 7.EmpMiddleName
> 8.Location
> And on the run you added few feilds and messed up all your tables like this.
> In future a datawarehouse analyst might try to map OLTP feilds to OLAP design
> to satisfy kep performance indicators and they might get blown away seeing
> such positioning of table feilds.
> Always below one make you feel good :)..
> 1.EmpFirstName
> 2.EmpMiddleName
> 3.EmpLastName
> 4.DOB
> 5.Sex
> 6.Location
> 7.Created Date
> 8.UpdatedDate
> Thanks,
> Sree
>
> "Jens" wrote:
> > I don´t know why you need ordinal positions. Since youcan use the name
> > of a column picking the columns by the ordinal positions should not be
> > needed anymore, if you use a view for displaying the data or a
> > selection string,you can "reorder" the columns on your own, on the fly.
> > EM does this odd drop and create for alter, using QA does not, so if
> > there are many rows in your table use QA for this, rather than EM.
> >
> > HTH, Jens Suessmeyer.
> >
> >|||It seems that there would be a script that is available to simply change the
ordinal position of the field for that table in the master table as part of
the alter table script. I am not sure of the impacts of this method. Has
anyone used this approach.
--
So Much - Yet - So Little
"Roger Wolter[MSFT]" wrote:
> Try running Profiler when you insert the column in Enterprise Manager. I
> think you will find it does exactly what you said you don't want to do -
> creates a new table, inserts the data from the old table, and then droops
> the old table. The right thing to do here is to write your SQL statements
> to not depend on a particular column order so that you can use Alter Table.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
> >I am using SQL Server 2000. As part of a new development project I find
> >that
> > I frequently need to add new columns/fields to existing tables using SQL
> > Scripts in Query Analyzer. I would like to add the new column at a
> > specific
> > Ordinal Position but when I use the standard Alter Table Add ... syntax
> > for
> > adding a new column the column always gets added to the end of the table.
> > Accomplishing this task is relatively easy using Enterprise Manager, you
> > simply select the column where you want the new column to be placed and
> > click
> > "Insert" and the new column gets inserted above the selected column. Is
> > there a way to add the column in a specific ordinal location from Query
> > Analyzer? If so, what is the syntax to make this happen. For example, I
> > want to add a new column ([columntwo] [int] NULL,) between [columnone] and
> > [columnthree] to the table layout below:
> > ColumnOne
> > ColumnThree
> > ColumnFour
> >
> > Using standard "alter table' syntax [columntwo] get added to the bottom.
> > ColumnOne
> > ColumnThree
> > ColumnFour
> > ColumnTwo
> >
> > I want it to look like this:
> > ColumnOne
> > ColumnTwo
> > ColumnThree
> > ColumnFour
> >
> > I realize I can simply create a new table with the properly ordered
> > fields,
> > copy the data over to the new table and then drop the existing table but
> > this
> > is approach is not optimal.
> > --
> > So Much - Yet - So Little
>
>|||Hi,
This may be a silly question, what are the drawbacks to altering the
column order (colorder) through the syscolumns table?
- Ben|||Hi,
As Roger metioned, In SQL server, you need to create a new table, inserts
the data from the old table, and then drop the old table.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: Alter Table - Add Colum - Ordinal Position'
>thread-index: AcYtkhaya59bWEyOR5ylpD9wDQSW8A==>X-WBNR-Posting-Host: 207.59.213.130
>From: "=?Utf-8?B?U3dpbmdWb3Rlcg==?=" <swingvoter@.nospam.nospam>
>References: <91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com>
<#JBE6aTLGHA.3164@.TK2MSFTNGP11.phx.gbl>
>Subject: Re: Alter Table - Add Colum - Ordinal Position'
>Date: Thu, 9 Feb 2006 08:01:29 -0800
>Lines: 66
>Message-ID: <C2BE3CB0-839A-4CE9-9FDE-96BC31931745@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:420560
>X-Tomcat-NG: microsoft.public.sqlserver.server
> It seems that there would be a script that is available to simply change
the
>ordinal position of the field for that table in the master table as part
of
>the alter table script. I am not sure of the impacts of this method. Has
>anyone used this approach.
>--
>So Much - Yet - So Little
>
>"Roger Wolter[MSFT]" wrote:
>> Try running Profiler when you insert the column in Enterprise Manager.
I
>> think you will find it does exactly what you said you don't want to do -
>> creates a new table, inserts the data from the old table, and then
droops
>> the old table. The right thing to do here is to write your SQL
statements
>> to not depend on a particular column order so that you can use Alter
Table.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "SwingVoter" <swingvoter@.nospam.nospam> wrote in message
>> news:91DDD1FB-961D-4976-9C36-C2EC3AE02C66@.microsoft.com...
>> >I am using SQL Server 2000. As part of a new development project I
find
>> >that
>> > I frequently need to add new columns/fields to existing tables using
SQL
>> > Scripts in Query Analyzer. I would like to add the new column at a
>> > specific
>> > Ordinal Position but when I use the standard Alter Table Add ...
syntax
>> > for
>> > adding a new column the column always gets added to the end of the
table.
>> > Accomplishing this task is relatively easy using Enterprise Manager,
you
>> > simply select the column where you want the new column to be placed
and
>> > click
>> > "Insert" and the new column gets inserted above the selected column.
Is
>> > there a way to add the column in a specific ordinal location from Query
>> > Analyzer? If so, what is the syntax to make this happen. For
example, I
>> > want to add a new column ([columntwo] [int] NULL,) between [columnone]
and
>> > [columnthree] to the table layout below:
>> > ColumnOne
>> > ColumnThree
>> > ColumnFour
>> >
>> > Using standard "alter table' syntax [columntwo] get added to the
bottom.
>> > ColumnOne
>> > ColumnThree
>> > ColumnFour
>> > ColumnTwo
>> >
>> > I want it to look like this:
>> > ColumnOne
>> > ColumnTwo
>> > ColumnThree
>> > ColumnFour
>> >
>> > I realize I can simply create a new table with the properly ordered
>> > fields,
>> > copy the data over to the new table and then drop the existing table
but
>> > this
>> > is approach is not optimal.
>> > --
>> > So Much - Yet - So Little
>>
>|||> This may be a silly question, what are the drawbacks to altering the
> column order (colorder) through the syscolumns table?
Not a silly question at all, but a simple answer: Corrupt table.
Syscolumns isn't only for the engine in what order to retrieve the columns. But also it defines in
what order the columns are physically stored on disk (in pages, row structures).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <vanevery@.gmail.com> wrote in message
news:1139501596.454222.136580@.g47g2000cwa.googlegroups.com...
> Hi,
> This may be a silly question, what are the drawbacks to altering the
> column order (colorder) through the syscolumns table?
> - Ben
>|||A similar suggestion has already been submitted to the Product Feedback
Center. You can vote on this suggestion here:
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=ed13e43a-a55a-45ee-98fb-b56f330a2cfa
Razvan|||Tibor,
Thank you very much! This information makes the rest of the
information presented in this thread much more justifiable!|||On Thu, 9 Feb 2006 07:50:34 -0800, SwingVoter wrote:
>From a programming and data usability perspective you are correct.
(snip)
> It is much more difficult for a designer to
>determine missing entities from the layout if the fields are scattered in an
>non-logical order.
Hi SwingVoter,
The easiest way to solve this is to organize the columns in a logical
order in your documentation, but don't care about the order in the
database.
Designers should work from the logical model, not from the
implementation.
> It seems that there would be a
>way to simply change the ordinal position in one of the master table as part
>of the alter table script.
There isn't one. As Tibor pointed out - attempting to do this would
corrupt your database.
--
Hugo Kornelis, SQL Server MVP