Add a coulm in a table, after a specific column

I know that in a relational database the order of the columns in a table
usually is not important, but I need to know if it's possible to alter a
table adding a column in a specific position, so when I do "SELECT *
FROM..." I see the columns in the order I like (the most important columns
in first position).

I read the SQL Server 2005 manuals and it seems it's not possible with a
simple ALTER TABLE instruction.

Is there a stored procedure that can do this?

Thanks in advance,
D.

Re: Add a coulm in a table, after a specific column

I don't think there is a quick shortcut. The way I have normally done this
is to create a new table, move the data, drop the original table and rename
the new table to the original name.

You can use SSMS to generate a script for this (that is if you make the
change via the graphical interface in SSMS). This option is available
through Generate Change Script in the Table Designer menu or if you
right-click the table editor.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Re: Add a coulm in a table, after a specific column

> You can use SSMS to generate a script for this (that is if you make the
> change via the graphical interface in SSMS). This option is available
> through Generate Change Script in the Table Designer menu or if you
> right-click the table editor.
>

Thanks!!!

Re: Add a coulm in a table, after a specific column

On Wed, 31 Oct 2007 16:56:29 +0100, D. wrote:

>> You can use SSMS to generate a script for this (that is if you make the
>> change via the graphical interface in SSMS). This option is available
>> through Generate Change Script in the Table Designer menu or if you
>> right-click the table editor.
>>
>
>Thanks!!!
>

But please do check, double-check, and triple-check the script before
executing it on a live server. There have historically been some serious
flaws in the scripted code used, that might result in losing all your
data if you are unlucky. Maybe things have changed since the last time I
looked, but I'd never run a script generated by Enterprise Manager or
SQL Server Management Studio without prior minute inspection!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Re: Add a coulm in a table, after a specific column

>
> But please do check, double-check, and triple-check the script before
> executing it on a live server. There have historically been some serious
> flaws in the scripted code used, that might result in losing all your
> data if you are unlucky. Maybe things have changed since the last time I
> looked, but I'd never run a script generated by Enterprise Manager or
> SQL Server Management Studio without prior minute inspection!
>

Ok,
could you please me tell me which part do I have to check?

I though it is the same script that Enterprise Manager launch when I save
the modification in the data structure...

D.

Re: Add a coulm in a table, after a specific column

D. (d@d.com) writes:
>> But please do check, double-check, and triple-check the script before
>> executing it on a live server. There have historically been some serious
>> flaws in the scripted code used, that might result in losing all your
>> data if you are unlucky. Maybe things have changed since the last time I
>> looked, but I'd never run a script generated by Enterprise Manager or
>> SQL Server Management Studio without prior minute inspection!
>>
>
> Ok,
> could you please me tell me which part do I have to check?
>
> I though it is the same script that Enterprise Manager launch when I save
> the modification in the data structure...

EM and Mgmt Studio generate the same crap.

There are many things to watch out for:

o Transsaction scope. The script has many small transactions, but there
should be one big transaction. That or just restore a backup if there
is any error.

o Constraints are restored with NOCHECK, that should be WITH CHECK.
That takes longer time, but the flip side is that the optimiser then
can trust the constraints. This can matter a lot in some cases.

o Remove all "go" in the script, and wrap most statements in EXEC.
The way script lookas as generated, if there is a batch-aborting
error, the transaction is rolled back, and the rest of the statements
will be committed. An alternative is to wrap all batches in
IF @@transcount > 0 BEGIN END.

o Rewiew that the script only includes the changes you intend. There
are situations where EM/SSMS may include a change that you have
abandoned.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Add a coulm in a table, after a specific column

>
> o Transsaction scope. The script has many small transactions, but there
> should be one big transaction. That or just restore a backup if there
> is any error.

Ok, I have to execute the DDL statements via jdbc so Iìll put them inside a
unique transaction.

>
> o Constraints are restored with NOCHECK, that should be WITH CHECK.
> That takes longer time, but the flip side is that the optimiser then
> can trust the constraints. This can matter a lot in some cases.

Ok

>
> o Remove all "go" in the script, and wrap most statements in EXEC.
> The way script lookas as generated, if there is a batch-aborting
> error, the transaction is rolled back, and the rest of the statements
> will be committed. An alternative is to wrap all batches in
> IF @@transcount > 0 BEGIN END.

I must remove all "go" statements because I'll execute the code via jdbc
driver.
I've not understood what do you mean with the phrase "wrap most statements
in EXEC" (sorry but I'm fairly new to SQLServer)

>
> o Rewiew that the script only includes the changes you intend. There
> are situations where EM/SSMS may include a change that you have
> abandoned.

Ok, I'll do.

I' also noticed that SSMS, before the ALTER TABLE statement put always some
instructions (in the automated generated SQL) that I don't unserstand
well...

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
ALTER TABLE xxxxxxx

Do you think that I have to execute them from my java program or can I start
directly with ALTER TABLE instruction?

Thanks,
D.

Re: Add a coulm in a table, after a specific column

D. (d@d.com) writes:
>> o Remove all "go" in the script, and wrap most statements in EXEC.
>> The way script lookas as generated, if there is a batch-aborting
>> error, the transaction is rolled back, and the rest of the statements
>> will be committed. An alternative is to wrap all batches in
>> IF @@transcount > 0 BEGIN END.
>
> I must remove all "go" statements because I'll execute the code via jdbc
> driver.
> I've not understood what do you mean with the phrase "wrap most statements
> in EXEC" (sorry but I'm fairly new to SQLServer)

If you from a client, it's actually easier. Don't remove the "go" in
the sense that you send the entire script at once, but execute each
batch separately, and abort the whole affair if there is an error.
Don't forget to add a IF @@trancount > 0 ROLLBACK TRANSACTION in this case.

The full story is this: when there is an error in an SQL command, several
things can happen, depending on the error, and many of there errors
when you work with DDL abort the batch and rollback the transaction.
Batches is separated by the "go". This means that if one batch fails
and aborts the transaction, and you run the script from a query window,
the remaining statements will still be executed - but without a transaction.

The stuff about EXEC is that instead of "ALTER TABLE ...", you need

EXEC('ALTER TABLE ...')

This is because if you put all in one batch, the script may not compile,
because it may refer to columns that has not been created yet etc.

But since you run from JDBC, you should not need this.

> I' also noticed that SSMS, before the ALTER TABLE statement put always
> some instructions (in the automated generated SQL) that I don't
> unserstand well...
>
> 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
> ALTER TABLE xxxxxxx
>
> Do you think that I have to execute them from my java program or can I
> start directly with ALTER TABLE instruction?

Include them. Some of these settings are save with the table and per
columns. Many of these settings are already in effect, but they do
not make any harm.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx