While trying to switch a partition from one table to another i got following error message
ALTER TABLE SWITCH statement failed because column ‘%’ does not have the same ANSI trimming semantics in tables ‘%’ and ‘%’.
It is related to difference of ANSI Padding setting between any column of both the tables involved in partition switch. Surprisingly i did not find the match of this setting as a partition switch prerequisite in BOL.
ANSI Padding relates to storage of char, varchar, binary, and varbinary data types. It is by default ON in SQL Server unless explicitly set OFF for a connection. Best practice is to always set it ON. Data types like nchar, nvarchar, ntext, text, image, varbinary(max), varchar(max), and nvarchar(max) have always ANSI_Padding ON and cannot be changed. So we will be dealing with char, varchar, binary, and varbinary data typesto manage this setting.
Problem while switching partition
Coming back to our partition switch issue, we are required to eliminate the ANSI_Padding difference between both tables to perform the partition switch. Let us create two tables, one with ANSI_Padding ON and other with OFF.
-- Select database USE DBDigs GO -- Set ANSI_Padding for first table (tbl_APOff) SET ANSI_PADDING OFF GO -- Create first table (tbl_APOff) IF EXISTS ( SELECT NAME FROM sys.objects WHERE type = 'U' AND NAME = 'tbl_APOff' ) DROP TABLE tbl_APOff GO CREATE TABLE tbl_APOff (id INT identity(1, 1), firstName VARCHAR(50), lastname VARCHAR(50)) GO -- Set ANSI_Padding for second table (tbl_APOn) SET ANSI_PADDING ON GO IF EXISTS ( SELECT NAME FROM sys.objects WHERE type = 'U' AND NAME = 'tbl_APOn' ) DROP TABLE tbl_APOn GO -- Create second table (tbl_APOn) CREATE TABLE tbl_APOn (id INT identity(1, 1), firstName VARCHAR(50), lastname VARCHAR(50)) GO
Above script will create tbl_APOff with ANSI_Padding OFF and tbl_APOn with ANSI_Padding ON. Both tables are exact copies of each other except the difference of ANSI_Padding setting that can be verified with following script
-- Select database USE DBDigs GO -- Check ANSI_PAdding for columns in both tables SELECT s1.NAME AS TableAPOn, s1.Is_ANSI_Padded AS APOnSetting, s2.NAME AS TableAPOff, s2.Is_ANSI_Padded AS APOffSetting FROM sys.columns s1 INNER JOIN ( SELECT NAME, Is_ANSI_Padded FROM sys.columns WHERE object_Name(object_ID) IN ('tbl_APOff') ) AS s2 ON s1.NAME = s2.NAME WHERE object_Name(object_ID) IN ('tbl_APOn') ORDER BY APOnSetting GO
At this stage we have idea of issue and a script to detect and analyze the issue.
To fix the ANSI_Padding difference issue we have two primary options.
- ALTER the ANSI_Padding setting of columns in one table to match other
- Recreate one of these tables with ANSI_Padding setting matching to other one
While applying any of above approach we should prefer to make ANSI_Padding ON so that best practice can be implemented.
ALTER the ANSI_Padding setting
Let us change the ANSI_Padding setting for both columns in tbl_APOff. This can be done with following ALTER COLUMN commands.
USE DBDigs GO SET ANSI_PADDING ON GO ALTER TABLE tbl_APOFF ALTER COLUMN firstName VARCHAR (50) NULL; GO ALTER TABLE tbl_APOFF ALTER COLUMN lastname VARCHAR(50); GO
Now we can match and verify the ANSI_Padding setting in both tables by re-executing the previous script. And result confirms the fix.
Issue is fixed and we can switch the partitions
Recreate table with modified ANSI_Padding
As a second option you can drop and re-create the table and do not forget to mention
SET ANSI_PADDING ON GO
at start of script.