Does SQL Server support "after" on alter table ?

Hello,
I'm trying to do this:
alter table maquinas add marca varchar(50) after modelo
and I get the following error:
Incorrect syntax near 'after'.
What is going on?
hans
No, you cannot tell SQL Server where to add a column. It gets apended to
the "end". Why is column order important? Yes, Management Studio /
Enterprise Manager can do this, but guess what they do behind the scenes?
Drop the table and re-create it.
"Hans" <goosseno@.gmail.com> wrote in message
news:5b95bb01-499a-410e-b656-cbe926f06263@.m34g2000hsb.googlegroups.com...
> Hello,
> I'm trying to do this:
> alter table maquinas add marca varchar(50) after modelo
> and I get the following error:
> Incorrect syntax near 'after'.
> What is going on?
> hans
|||Well, using Management Studio or Enterprise Manager is not that bad if your
table is small.
Ben Nevarez
although using Management Studio or Enterprise Manager is not that bad if
your table is small.
Ben
"Aaron Bertrand [SQL Server MVP]" wrote:

> No, you cannot tell SQL Server where to add a column. It gets apended to
> the "end". Why is column order important? Yes, Management Studio /
> Enterprise Manager can do this, but guess what they do behind the scenes?
> Drop the table and re-create it.
>
> "Hans" <goosseno@.gmail.com> wrote in message
> news:5b95bb01-499a-410e-b656-cbe926f06263@.m34g2000hsb.googlegroups.com...
>
>

Does SQL Server support "after" on alter table ?

Hello,
I'm trying to do this:
alter table maquinas add marca varchar(50) after modelo
and I get the following error:
Incorrect syntax near 'after'.
What is going on?
hansNo, you cannot tell SQL Server where to add a column. It gets apended to
the "end". Why is column order important? Yes, Management Studio /
Enterprise Manager can do this, but guess what they do behind the scenes?
Drop the table and re-create it.
"Hans" <goosseno@.gmail.com> wrote in message
news:5b95bb01-499a-410e-b656-cbe926f06263@.m34g2000hsb.googlegroups.com...
> Hello,
> I'm trying to do this:
> alter table maquinas add marca varchar(50) after modelo
> and I get the following error:
> Incorrect syntax near 'after'.
> What is going on?
> hans|||Well, using Management Studio or Enterprise Manager is not that bad if your
table is small.
Ben Nevarez
although using Management Studio or Enterprise Manager is not that bad if
your table is small.
Ben
"Aaron Bertrand [SQL Server MVP]" wrote:
> No, you cannot tell SQL Server where to add a column. It gets apended to
> the "end". Why is column order important? Yes, Management Studio /
> Enterprise Manager can do this, but guess what they do behind the scenes?
> Drop the table and re-create it.
>
> "Hans" <goosseno@.gmail.com> wrote in message
> news:5b95bb01-499a-410e-b656-cbe926f06263@.m34g2000hsb.googlegroups.com...
> > Hello,
> >
> > I'm trying to do this:
> > alter table maquinas add marca varchar(50) after modelo
> >
> > and I get the following error:
> > Incorrect syntax near 'after'.
> >
> > What is going on?
> >
> > hans
>
>

Does ALTER COLUMN effect the data?

I just want to change the length of 2 fields. They're CHAR and NVARCHAR type. I want to change the length from 50 to 75.

Will doing this effect the data? Will it delete the data in those columns? I just want to make sure before I do anything. The table contains about 2.5 million rows, so I don't want to mess anything up. I'm aware that the script may take a while to run and finish, but I'm only concerned about data integrity.

Here is my example script syntax to show what I'm planning to do:

Code Snippet

alter table credit alter column writeoffreason char(75) not null;

Does it matter what type of data is in the columns? For now, it's only CHAR and NVARCHAR.

I also read somewhere that you can increase length but cannot decrease without repercussions?

Thanks!

It will not delete, but SS has to allocate space in order to accomodate the CHAR one, because it has to pad with spaces to the right. This statement will not only affect metadata, it will affect actual space allocation. Check table fragmentation after it has finished.

AMB

|||thanks for the confirmation, i had thought so that the data integrity would remain intact.

Does Alter Column do work if column already the same as desired?

I have a table with a column defined thus: LOCNumber Varchar(100) in a
table called Bookdata.

If I execute ALTER TABLE BookData ALTER COLUMN LOCNumber varchar(100)
on the table, does any work get done on the table and column?
Or does SqlServer know that the column is already varchar(100) and
nothing
needs to be done?
If it does do some work, how do you tell it not to run this as it is
already a varchar(100) column. Right now my program just blindly does
this ALTER TABLE
statement regardless of any conditions. Is this a potential problem?
Thanks for any help.SQL 2000 only needs to update meta data when the new varchar column length
is >= the specified length. Consequently, you can skip the length check.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<sdowney717@.msn.com> wrote in message
news:1107370345.836079.312700@.z14g2000cwz.googlegr oups.com...
>I have a table with a column defined thus: LOCNumber Varchar(100) in a
> table called Bookdata.
> If I execute ALTER TABLE BookData ALTER COLUMN LOCNumber varchar(100)
> on the table, does any work get done on the table and column?
> Or does SqlServer know that the column is already varchar(100) and
> nothing
> needs to be done?
> If it does do some work, how do you tell it not to run this as it is
> already a varchar(100) column. Right now my program just blindly does
> this ALTER TABLE
> statement regardless of any conditions. Is this a potential problem?
> Thanks for any help.|||Thanks
It is 2000, so everything is fine.

Does a tool like this exist?

Hi:
I am looking for a tool/utility that can compare 2 tables (indexes and
constraints etc.) and create a SQL script containing 'ALTER TABLE ...'
statements that would update the second table so it is the same as the first
table.
Does anything like this exist? It's a bit of a long-shot isn't it?
TIA,
MartinH.
Red Gate has a tool called SQL Compare
http://www.red-gate.com/
--Keith
"Martin Hart - Memory Soft, S.L." <memorysoftsl _at_ infotelecom _dot_ es>
wrote in message news:O8IXekOQFHA.244@.TK2MSFTNGP12.phx.gbl...
> Hi:
> I am looking for a tool/utility that can compare 2 tables (indexes and
> constraints etc.) and create a SQL script containing 'ALTER TABLE ...'
> statements that would update the second table so it is the same as the
> first table.
> Does anything like this exist? It's a bit of a long-shot isn't it?
> TIA,
> MartinH.
>
|||Hi,
SQLCompare is an excellent tool to compare and generate the script for the
differences between databases.
Try out the 14 day trial.
http://www.red-gate.com/SQL_Compare.htm
Thanks
Hari
SQL Server MVP
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23ux6lzOQFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Red Gate has a tool called SQL Compare
> http://www.red-gate.com/
> --Keith
> "Martin Hart - Memory Soft, S.L." <memorysoftsl _at_ infotelecom _dot_ es>
> wrote in message news:O8IXekOQFHA.244@.TK2MSFTNGP12.phx.gbl...
>
|||Martin Hart - Memory Soft, S.L. wrote:
> Hi:
> I am looking for a tool/utility that can compare 2 tables (indexes and
> constraints etc.) and create a SQL script containing 'ALTER TABLE ...'
> statements that would update the second table so it is the same as
> the first table.
> Does anything like this exist? It's a bit of a long-shot isn't it?
>
Imceda offers Speed Change Manager. 14-day trial on web site.
David Gugick
Imceda Software
www.imceda.com
|||Keith:
Thanks, looks *very* interesting.
Regards,
MartinH.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> escribi en el mensaje
news:%23ux6lzOQFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Red Gate has a tool called SQL Compare
> http://www.red-gate.com/
> --Keith
> "Martin Hart - Memory Soft, S.L." <memorysoftsl _at_ infotelecom _dot_ es>
> wrote in message news:O8IXekOQFHA.244@.TK2MSFTNGP12.phx.gbl...
>
|||Innovartis does DB Ghost - more than a comparison tool - 30 day trial
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Martin Hart - Memory Soft, S.L." wrote:

> Hi:
> I am looking for a tool/utility that can compare 2 tables (indexes and
> constraints etc.) and create a SQL script containing 'ALTER TABLE ...'
> statements that would update the second table so it is the same as the first
> table.
> Does anything like this exist? It's a bit of a long-shot isn't it?
> TIA,
> MartinH.
>
>
|||Could you tell me Where is the web site ?
Thanks a lot
"mark baekdal" <markbaekdal@.discussions.microsoft.com> glsD:F9AC3991-7F4F-4C29-BA6D-0D52E03FB1C5@.microsoft.com...[vbcol=seagreen]
> Innovartis does DB Ghost - more than a comparison tool - 30 day trial
>
> regards,
> Mark Baekdal
> http://www.dbghost.com
> http://www.innovartis.co.uk
> +44 (0)208 241 1762
> Build, Comparison and Synchronization from Source Control = Database
> change
> management for SQL Server
>
>
> "Martin Hart - Memory Soft, S.L." wrote:
|||http://www.dbghost.com
"Agnes" wrote:

> Could you tell me Where is the web site ?
> Thanks a lot
> "mark baekdal" <markbaekdal@.discussions.microsoft.com> ???g?ó?l￥ó·s?D:F9AC3991-7F4F-4C29-BA6D-0D52E03FB1C5@.microsoft.com...
>
>