|
Home > Archive > MS SQL Server > February 2006 > Re: Alter Table - Add Colum - Ordinal Position??
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: Alter Table - Add Colum - Ordinal Position??
|
|
|
| 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 2006-02-09, 7:23 am |
| :) 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.
>
>
| |
| Sreejith G 2006-02-09, 7:23 am |
| :) 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.
>
>
| |
| SwingVoter 2006-02-09, 11:23 am |
| 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:
[color=darkred]
> :) 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:
>
| |
| Hugo Kornelis 2006-02-10, 8:23 pm |
| 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
|
|
|
|
|