Too Many SET Options

[Dil]

Hi

I have created a Sql Script through Enterprise Manager for Drop a
column. By default its creating lot of 'SET' commands. I doubt all
these SET options are required or not. Pls comment on this issue

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
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
ALTER TABLE Employee
DROP COLUMN OrderDetails_ID
GO
COMMIT
Dil

[ZeldorBlat]

Dil wrote:

Hi

I have created a Sql Script through Enterprise Manager for Drop a
column. By default its creating lot of 'SET' commands. I doubt all
these SET options are required or not. Pls comment on this issue

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
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
ALTER TABLE Employee
DROP COLUMN OrderDetails_ID
GO
COMMIT
Dil

Here's something you can try:

Remove all the SET statements
Run the script
See what happens

[Erland Sommarskog]

Dil (di******@gmail.com) writes:

I have created a Sql Script through Enterprise Manager for Drop a
column. By default its creating lot of 'SET' commands. I doubt all
these SET options are required or not. Pls comment on this issue

First of all, the scripts that are generated through EM requires
careful review, as there are several serious bugs in the Table Designer
when it comes to modifing tables. All these bugs are in Mgmt Studio in
SQL 2005 as well, by the way.

I guess the SET commands are there, in case an operation would require
the script to work with an indexed view or an indexed computed column.
These two features requires the settings in the script, except for the
isolation level setting.

There is however a minor flaw with the SET commands. To wit, when you
create a varchar/varbinary column, the setting of ANSI_PADDING is saved
with that column. Say now that you make a change to a table, which requires
the table designer to recreate the table. It will now recreate all
columns with SET ANSI_PADDING ON, even if they were originall created
with padding off. Since a table could, at least theoretically, have a mix
of settings, it would be a lot of work to retain the setting.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx