locked
Create table script.... RRS feed

  • Question

  • When I right click on a table and click Script Table as --> Create to -->  it scripts out:

    CREATE TABLE [dbo].[Test](            

    [FirstName] [varchar](50) NULL,            

    [LastName] [varchar](50) NULL,            

    [Address] [varchar](50()  NULL

    SET ANSI_PADDING OFF

    ALTER TABLE [dbo].[Test] ADD [City] [varchar](50) NULL

     

    Why is there an Alter table statement?  I’m assuming this table was altered at some point but why is SQL not just doing a create table and how is it keeping track of these changes?



    • Edited by FJTexas Tuesday, July 21, 2015 3:52 PM
    Tuesday, July 21, 2015 3:49 PM

Answers

  • The ANSI_PADDING setting is saved by column, and apparently City has a different setting from the rest.

    Now, ANSI_PADDING OFF is a legacy setting you should never use, so the correct thing here would be to remove the command SET ANSI_PADDING OFF.

    The effect of ANSI_PADDING OFF is that trailing spaces in varchar data and trailing zero bytes in varbinary data are stripped off when data is saved. With ANSI_PADDING ON, the spaces/zeroes are retained. Note that the setting does not apply to nvarchar, nor to the MAX types.

    There are functionality in SQL Server that requires ANSI_PADDING to be ON, os if you have columns with the wrong setting, you will sooner or later run into problem.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Eric__Zhang Wednesday, July 29, 2015 2:00 AM
    Tuesday, July 21, 2015 10:07 PM

All replies

  • My only guess would be that some of the columns were created with SET ANSI_PADDING OFF setting in effect. This is very bad, actually.

    Check my article on this topic

    SQL Server: SET ANSI_PADDING Setting and Its Importance


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 21, 2015 3:53 PM
  • No I tested it with different ANSI_PADDING settings.
    Tuesday, July 21, 2015 3:59 PM
  • Is the problem with this particular table or any table? 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 21, 2015 4:06 PM
  • The ANSI_PADDING setting is saved by column, and apparently City has a different setting from the rest.

    Now, ANSI_PADDING OFF is a legacy setting you should never use, so the correct thing here would be to remove the command SET ANSI_PADDING OFF.

    The effect of ANSI_PADDING OFF is that trailing spaces in varchar data and trailing zero bytes in varbinary data are stripped off when data is saved. With ANSI_PADDING ON, the spaces/zeroes are retained. Note that the setting does not apply to nvarchar, nor to the MAX types.

    There are functionality in SQL Server that requires ANSI_PADDING to be ON, os if you have columns with the wrong setting, you will sooner or later run into problem.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Eric__Zhang Wednesday, July 29, 2015 2:00 AM
    Tuesday, July 21, 2015 10:07 PM