Share via

SQL Server: SET ANSI_PADDING Setting and its Importance

  • Article

Problem Description

Recently I got an interesting escalation to solve for the client. Our VFP based application was getting the following SQL Server error: "Violation of PRIMARY KEY constraint 'rep_crit_operator_report'. Cannot insert duplicate key in object 'dbo.rep_crit' The duplicate key value is (ADMIN, REPORT_PERIOD_SALES)."

Investigation

I started my investigation of the problem by checking VFP code and finding it to be a bit sloppy with no good error handling (the code was issuing a TABLEUPDATE without checking its return status).

I then connected to the client through TeamViewer and observed that error in action. I then also fired SQL Server Profiler and found that the tableupdate command was attempting to do an insert instead of UPDATE and therefore was failing with the above error. At that point I was afraid that we would not be able to solve the problem without fixing the source code.

In the VFP source code we were always padding the report column which was defined as varchar(20) to 20 characters. I am not sure why we were doing it this way and why in this case we were not using CHAR(20) instead of VARCHAR(20) since the value was always saved with extra spaces at the end. But since this code was there for a long time, I didn't try to question its validity.

At that point I decided to test what was the actual length of report column saved in the table. So, I ran the following query

 

      SELECT *, DATALENGTH(Report) as Report_Length FROM dbo.rep_crit

To my surprise I saw values less than 20. I ran the same code in my local database and got expected value 20 for all rows. The strange behavior on the client was a bit perplexing. 

I then thought I'll try to fix the problem and ran the following UPDATE statement:

 

      UPDATE dbo.rep_crit SET report = LEFT(RTRIM(report) + SPACE(20),20)

to pad the column with spaces at the end. Again, I verified that code locally first. I ran that code on the client and then ran the first select statement and got the same result as before - the column still showed length less than 20 characters.

Resolution

To be honest, I should have guessed what was happening by myself. But I must admit that I still didn't, I sent e-mail to my colleagues asking what do they think about that strange behavior and I also posted this thread Weird problem with the client. My colleague immediately recognized the problem as one he already experienced with another client. And Latheesh NK also pointed out into SET ANSI_PADDING setting as possible culprit.

So, somehow several tables were saved with the wrong ANSI_PADDING setting being in effect and therefore the column's setting overrode sessions settings. 

Recently I made a change in our VFP applications to save varchar columns as varchar (prior to that all varchar columns were automatically padded with spaces to their length). This caused the above mentioned problem when the client upgraded the software to the recent release version.

The solution to that particular error was to run ALTER TABLE statement to alter report column to be the same width as the original column but using SET ANSI_PADDING ON before running the statement. This fixed the wrong padding on the column.

This is how we can check column's status in design mode when we right click on the column and check its properties:

 

ANSI Padding Status is close to the bottom in the designer.

After the problem was identified, we wanted to check the scope of the problem and also correct the problem for other columns that have been saved with wrong ANSI_PADDING setting.

Script to correct problem in the whole database

I came up with the following script to correct the problem:



;WITH cte
AS (
    SELECT c.is_nullable
        ,c.object_id AS  table_id
        ,OBJECT_NAME(c.object_id) AS  TableName
        ,c.max_length
        ,c.NAME column_name
        ,CASE c.is_ansi_padded
            WHEN 1
                THEN 'On'
            ELSE 'Off'
            END AS  [ANSI_PADDING]
        ,T.NAME AS  ColType
    FROM sys.columns c
    INNER JOIN sys.types T  ON  c.system_type_id = T.system_type_id
    WHERE T.NAME IN ('varbinary', 'varchar')
    )
SELECT 'ALTER TABLE dbo.' + quotename(cte.TableName) + ' ALTER COLUMN '  + QUOTENAME(cte.column_name) + ' '  + cte.ColType + '('  + CASE
        WHEN cte.max_length = - 1
            THEN 'max'
        ELSE CAST(cte.max_length AS  VARCHAR(30))
        END + ')'  + CASE
        WHEN cte.is_nullable = 1
            THEN ' NULL '
        ELSE ' NOT NULL'
        END
FROM cte
INNER JOIN (
    SELECT objname
    FROM fn_listextendedproperty('SIRIUS_DefaultTable', 'user', 'dbo', 'table',  NULL, NULL, NULL)
    ) st ON  st.objname = cte.TableName
    AND cte.ANSI_PADDING = 'Off'

In this code the extra INNER JOIN is done to perform the update only on our tables in the database. In generic case you don't need this extra JOIN.

We need to run the code above using Query results to Text option from the Query menu. Then we can copy the output of that statement into new query window and run it to fix this problem. 

Default Database Settings

I discussed this problem in one more thread SET ANSI_PADDING setting. This thread provides additional insight into the importance of the correct setting.

It would be logical to expect that when we create a new database, the default settings have correct values for SET ANSI_NULL and SET ANSI_PADDING. However, this is not the case even for SQL Server 2012. If we don't change database defaults, they all come up wrong. See them here:

 

Therefore if we want correct settings on the database level, it may be a good idea to fix them at the moment we create a new database. However, these settings are not very important since they are overwritten by the session settings. 

As noted in the Comments, another interesting case of varbinary truncation due to this wrong setting is found in this Transact-SQL forum's thread.

 This entry participated in the TechNet Guru contributions for June contest and won the Silver prize.

See Also

Other Languages