Partition switch failed because : column ‘xyz’ does not have the same ANSI trimming semantics in tables ‘a’ and ‘b’
It turned out it was because the ANSI_PADDING setting was different between the two tables (well, for a single column). So first things first, what exactly is ANSI_PADDING.
Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data.
Descriptions are nice but I like examples:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
SET ANSI_PADDING ON; CREATE TABLE Padding_ON ( is_char_NULL char(10) NULL, is_char_NOTNULL char(10) NOT NULL, is_varchar_NULL varchar(10) NULL, is_varchar_NOTNULL varchar(10) NOT NULL ); INSERT INTO Padding_ON VALUES ('aaa','aaa','aaa','aaa'), ('aaa ','aaa ','aaa ','aaa '); SELECT DATALENGTH(is_char_NULL) AS is_char_NULL_len, DATALENGTH(is_char_NOTNULL) AS is_char_NOTNULL_len, DATALENGTH(is_varchar_NULL) AS is_varchar_NULL_len, DATALENGTH(is_varchar_NOTNULL) AS is_varchar_NOTNULL_len FROM Padding_ON; SELECT is_char_NULL+'|' AS is_char_NULL, is_char_NOTNULL+'|' AS is_char_NOTNULL, is_varchar_NULL+'|' AS is_varchar_NULL, is_varchar_NOTNULL+'|' AS is_varchar_NOTNULL FROM Padding_ON; ------------------------------------------------------ SET ANSI_PADDING OFF; CREATE TABLE Padding_OFF ( is_char_NULL char(10) NULL, is_char_NOTNULL char(10) NOT NULL, is_varchar_NULL varchar(10) NULL, is_varchar_NOTNULL varchar(10) NOT NULL ); INSERT INTO Padding_OFF VALUES ('aaa','aaa','aaa','aaa'), ('aaa ','aaa ','aaa ','aaa '); SELECT DATALENGTH(is_char_NULL) AS is_char_NULL_len, DATALENGTH(is_char_NOTNULL) AS is_char_NOTNULL_len, DATALENGTH(is_varchar_NULL) AS is_varchar_NULL_len, DATALENGTH(is_varchar_NOTNULL) AS is_varchar_NOTNULL_len FROM Padding_OFF; SELECT is_char_NULL+'|' AS is_char_NULL, is_char_NOTNULL+'|' AS is_char_NOTNULL, is_varchar_NULL+'|' AS is_varchar_NULL, is_varchar_NOTNULL+'|' AS is_varchar_NOTNULL FROM Padding_OFF; |
ON is the default and is what you would expect. Trailing spaces are saved in VARCHAR and in CHAR additional spaces added to fill the entire space. When ANSI_PADDING is off then additional spaces are not saved .. unless the column is CHAR AND NOT NULL.
So there’s the first reason to not turn ANSI_PADDING off. Most people expect the ON results and the OFF results can be .. let’s just say confusing.
Continue reading on SQLStudies.com.