Posted by Erland Sommarskog on 03/11/06 01:35

Dil (dilishtv@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, esquel@sommarskog.se

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

[Back to original message]