Alter column

Hello guys,
I already have a table, and i want to add a new column by using a script -
how do i do it?
I need it to be the identity column and it should be the first column!!
I can do it using enterprise manager- insert column at the first row, but i
just cant find a TSQL Script to do it
thanks in advance
Reagrds
IshanYou can use
alter table mytable add newfield int identity
But I do not think there is a way to make it the first column using only one
command. As far as I remember Enterprise Manager do several steps in the
background including creating a new table with the new field and moving data
from the original table.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ishan Bhalla" wrote:
> Hello guys,
> I already have a table, and i want to add a new column by using a script -
> how do i do it?
> I need it to be the identity column and it should be the first column!!
> I can do it using enterprise manager- insert column at the first row, but i
> just cant find a TSQL Script to do it
> thanks in advance
> Reagrds
> Ishan|||Ishan
Actually , it does not matter the order , does it? You can run SELECT as
SELECT IdentityColumn,col1,col2,col2......
Or
SELECT col1,col2,col2...... ,IdentityColumn FROM Table
"Ishan Bhalla" <IshanBhalla@.discussions.microsoft.com> wrote in message
news:2B5884A1-7E99-45D5-90A6-61F8198A664B@.microsoft.com...
> Hello guys,
> I already have a table, and i want to add a new column by using a script -
> how do i do it?
> I need it to be the identity column and it should be the first column!!
> I can do it using enterprise manager- insert column at the first row, but
> i
> just cant find a TSQL Script to do it
> thanks in advance
> Reagrds
> Ishan|||Hi,
actually i agree that i should not make a difference IF the queries dont
have something like Select * from xyz and then in the VB code the fields are
being accesssed by the ordinal position rather than the field name. As i dont
know that i just want to be sure!!
Thanks anyway!!!
regards
Ishan
"Uri Dimant" wrote:
> Ishan
> Actually , it does not matter the order , does it? You can run SELECT as
> SELECT IdentityColumn,col1,col2,col2......
> Or
> SELECT col1,col2,col2...... ,IdentityColumn FROM Table
>
>
> "Ishan Bhalla" <IshanBhalla@.discussions.microsoft.com> wrote in message
> news:2B5884A1-7E99-45D5-90A6-61F8198A664B@.microsoft.com...
> > Hello guys,
> >
> > I already have a table, and i want to add a new column by using a script -
> > how do i do it?
> > I need it to be the identity column and it should be the first column!!
> >
> > I can do it using enterprise manager- insert column at the first row, but
> > i
> > just cant find a TSQL Script to do it
> >
> > thanks in advance
> > Reagrds
> > Ishan
>
>|||Ishan Bhalla wrote:
> Hi,
> actually i agree that i should not make a difference IF the queries dont
> have something like Select * from xyz and then in the VB code the fields are
> being accesssed by the ordinal position rather than the field name. As i dont
> know that i just want to be sure!!
>
Perfect reason why SELECT * shouldn't be used in production code!!!
Another thread was asking for specific reasons why not to use SELECT *,
you just provided a great one.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||hi
i agree and i have never done something like it, but this is a code
maintainance, and i dont know what has been written in the applications!!!
the apps seem to have been poorly written
regards
Ishan
"Tracy McKibben" wrote:
> Ishan Bhalla wrote:
> > Hi,
> >
> > actually i agree that i should not make a difference IF the queries dont
> > have something like Select * from xyz and then in the VB code the fields are
> > being accesssed by the ordinal position rather than the field name. As i dont
> > know that i just want to be sure!!
> >
> Perfect reason why SELECT * shouldn't be used in production code!!!
> Another thread was asking for specific reasons why not to use SELECT *,
> you just provided a great one.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||"Ishan Bhalla" <IshanBhalla@.discussions.microsoft.com> wrote in message
news:3B1674B9-A45A-4260-9502-47A2CB223283@.microsoft.com...
> hi
> i agree and i have never done something like it, but this is a code
> maintainance, and i dont know what has been written in the applications!!!
> the apps seem to have been poorly written
Generally though in this case, the worst thing you can do is put the column
FIRST since all queries will break.
If you make it "last" then in theory most queries will probably still work
since they'll just pick off N-1 columns.
> regards
> Ishan
> "Tracy McKibben" wrote:
> > Ishan Bhalla wrote:
> > > Hi,
> > >
> > > actually i agree that i should not make a difference IF the queries
dont
> > > have something like Select * from xyz and then in the VB code the
fields are
> > > being accesssed by the ordinal position rather than the field name. As
i dont
> > > know that i just want to be sure!!
> > >
> >
> > Perfect reason why SELECT * shouldn't be used in production code!!!
> > Another thread was asking for specific reasons why not to use SELECT *,
> > you just provided a great one.
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com
> >|||Actually these are stagin tables and a new one is created everyday. The VB
code just loops through the code and access the columns by ordinal position!!!
thanks everyone for your point of view!
regards
Ishan
"Greg D. Moore (Strider)" wrote:
> "Ishan Bhalla" <IshanBhalla@.discussions.microsoft.com> wrote in message
> news:3B1674B9-A45A-4260-9502-47A2CB223283@.microsoft.com...
> > hi
> >
> > i agree and i have never done something like it, but this is a code
> > maintainance, and i dont know what has been written in the applications!!!
> > the apps seem to have been poorly written
> Generally though in this case, the worst thing you can do is put the column
> FIRST since all queries will break.
> If you make it "last" then in theory most queries will probably still work
> since they'll just pick off N-1 columns.
>
> >
> > regards
> > Ishan
> >
> > "Tracy McKibben" wrote:
> >
> > > Ishan Bhalla wrote:
> > > > Hi,
> > > >
> > > > actually i agree that i should not make a difference IF the queries
> dont
> > > > have something like Select * from xyz and then in the VB code the
> fields are
> > > > being accesssed by the ordinal position rather than the field name. As
> i dont
> > > > know that i just want to be sure!!
> > > >
> > >
> > > Perfect reason why SELECT * shouldn't be used in production code!!!
> > > Another thread was asking for specific reasons why not to use SELECT *,
> > > you just provided a great one.
> > >
> > >
> > > --
> > > Tracy McKibben
> > > MCDBA
> > > http://www.realsqlguy.com
> > >
>
>