SQL Server Performance

ANSI PADDING

Discussion in 'Getting Started' started by lcerni, Mar 27, 2008.

  1. lcerni New Member

    Is there a query to run to determine which columns have ansi padding turned on or off? I don't see anything in information_schema.columns. Is there somewhere else that I can look?
    Notice how SET ANSI_PADDING OFF is in the middle of the create statement. I want to determine what is off and correct it and make it ON.
    CREATE TABLE [dbo].[ol](
    [ols_tkt_num] [int] IDENTITY(1,1) NOT NULL,
    [order_num] [int] NOT NULL,
    [client_id] [int] NULL,
    [contract_cd] [varchar](18) NULL,
    [invoice_option] [smallint] NULL
    ) ON [PRIMARY]
    SET ANSI_PADDING ON
    ALTER TABLE [dbo].[ol] ADD [service_type_cd] [char](3) NOT NULL
    SET ANSI_PADDING OFF
    ALTER TABLE [dbo].[ol] ADD [cust_po_num] [char](18) NULL
    ALTER TABLE [dbo].[ol] ADD [package_cd] [char](18) NULL
    ALTER TABLE [dbo].[ol] ADD [ol_type_cd] [char](3) NOT NULL
    ALTER TABLE [dbo].[ol] ADD [completed_dt] [datetime] NULL
    ALTER TABLE [dbo].[ol] ADD [entered_dt] [datetime] NOT NULL
    Here is additional information.
    --when right click on table and script table as create
    /***SET ANSI_PADDING ON
    ALTER TABLE [dbo].[order_labor] ADD [billing_cd] [char](3) NULL
    ALTER TABLE [dbo].[order_labor] ADD [invoice_num] [int] NULL
    ALTER TABLE [dbo].[order_labor] ADD [time_in] [smalldatetime] NULL
    ALTER TABLE [dbo].[order_labor] ADD [time_out] [smalldatetime] NULL
    SET ANSI_PADDING OFF***/

    However, when I put this query together I get this:
    select b.name as tname, a.name cname, a.is_ansi_padded
    from sys.all_columns a,
    sys.sysobjects b
    where a.object_id=b.idand xtype='U'
    and b.name='order_labor'
    and a.name in ('billing_cd', 'invoice_num', 'time_in','time_out')
    order by a.name

    /***tname cname is_ansi_padded
    order_labor billing_cd 1
    order_labor invoice_num 0
    order_labor time_in 0
    order_labor time_out 0
    ***/

    And when do the following I get the following result:
    SELECT 'order_labor','billing_cd',COLUMNPROPERTY(OBJECT_ID('order_labor'), 'billing_cd', 'UsesAnsiTrim')
    SELECT 'order_labor','invoice_num',COLUMNPROPERTY(OBJECT_ID('order_labor'), 'invoice_num', 'UsesAnsiTrim')
    SELECT 'order_labor','time_in',COLUMNPROPERTY(OBJECT_ID('order_labor'), 'time_in', 'UsesAnsiTrim')
    SELECT 'order_labor','time_out',COLUMNPROPERTY(OBJECT_ID('order_labor'), 'time_out', 'UsesAnsiTrim')
    /***
    order_labor billing_cd 1
    order_labor invoice_num NULL
    order_labor time_in NULL
    order_labor time_out NULL
    ***/

    I am confused. Why does scripting the table say that it is on but when I try to view through the tables it is not?
    How do you change it from NULL or 0 to 1? How do you change individual columns?
  2. Luis Martin Moderator

    Did you use EM or SSM to script the table?. Both GUI give you different results using 2000 databases.
  3. lcerni New Member

    I used Microsoft SQL Server Management Studio to script a SQL Server 2005 database.
    I think what happened is that this database was originally a sybase database with all the settings turned off. We are migrating from SQL Server 2000 to 2005. The previous individual would make all his database changes through ERWIN. We no longer have the licence and thus I make all my changes through T-SQL. I was making my changes through Microsoft SQL Server Management Studio (MSSMS) on the new database. My settings on MSSMS have the settings turned on. Thus where the settings are turned off that is the old sybase setting and where I see the settings turned on, it is because I make a change to the table.
    When we do the final migration, I will script the database and make sure the settings are turned on and I will script the database objects and make sure the appropriate settings are also turned on. I am trying to be pro active since it is my understanding that the SET feature in the front end side/session side is going away (deprecated) for SQL Server 2008.

Share This Page