SQL Prompt is a practical SQL syntax prompt tool. It searches automatically according to the object name, syntax and code fragment of the database, and provides users with appropriate code selection. Automatic script settings make code easy to read - especially useful when developers are not familiar with scripts. This article describes not to create columns that have ANSI? Padding set to OFF.
It's a bit odd to look up connection settings that permanently change the way certain data types are retrieved in a table, but this can happen if you use set ANSI? Padding off when creating a temporary or permanent table. When creating a table, due to improper settings or the occasional use of an old application with DBLib connections, some columns in the table have since handled trailing spaces of some string or binary data types strangely or inconsistently.
This option has been deprecated and will be removed at some point (it will always be "on"). SQL Prompt has deprecated syntax rule DEP013, which warns you to use this option as well as other deprecated SET options.
What is an ANSI fill? Why?
In the early days of SQL, how to deal with strings was controversial. CHAR data types that define and fix string length are designed to make data retrieval simple and efficient. The string is stored in the CHAR data type at the specified length. For shorter strings, all remaining character positions in the data type are filled with spaces (CHAR) or zeros (BINARY). Are these padding spaces part of the string? If not, how do you distinguish between intentionally filled spaces and automatically filled values?
Early implementations of SQL typically trim all trailing spaces in CHAR when retrieving data, unless the column is NOT NULL. However, it is clear that this has to be changed in order to comply with the ANSI SQL standard. The NIST test suite checks that the CHAR data type is always populated and that the trailing spaces entered by the user are not truncated for CHAR or VARCHAR data types. SQL Server decided that, in order to comply with the rules, any trailing spaces (whether intentionally or automatically added as padding) would be retrieved along with the data, as would trailing zeros for binary data types. However, because too much code was written in the old system, a setting called ANSI? Padding was introduced into transact SQL. When it turns off, it allows the old code to work as usual. It seems everyone is happy.
Once the dispute over the ANSI-ISO standard for CHAR data types subsides, new data types and new table types will be introduced. The ANSI? Padding dispute only affects the type that existed at the time, and the length defined by the user for this purpose. You can now store the string as the defined size of NVARCHAR, VARCHAR, NCHAR, or CHAR. BINARY data can be stored as a defined size of BINARY or VARBINARY. For earlier data types of CHAR (n), BINARY (n), VARCHAR (n), or VARBINARY (n), the setting of the ANSI? Padding option when creating a table affects the way SQL Server processes these strings later.
However, later NCHAR, NVARCHAR, NTEXT, TEXT, or IMAGE data types are not. Types of undefined length VARBINARY (MAX), VARCHAR (MAX), and NVARCHAR (MAX) are also not affected.
The only use that old database developers really need to turn OFF ANSI population is for string connections without using this RTRIM() feature. It seems a good idea to avoid having to use the RTRIM() function, but the behavior of the fill rule is inconsistent with the behavior of having nullable columns. In addition, with the introduction of new types of tables, no one wants to make them backward compatible, so the applicable rules usually have no effect on table variables when ANSI? Padding is turned OFF. Similarly, if you try to create or change an index on a computed column or indexed view, you are likely to get stuck. If you set ANSI? Padding to OFF, this is not allowed at all.
So, what are the rules?
The simple behavior of the ANSI standard is that for data inserted with a fixed width type, trailing spaces or zeros are always filled to the specified length, and then, for all data types, any trailing spaces or zeros are considered part of the data, and so on. When SQL Server retrieves data to memory, they are never pruned.
If you switch ANSI? Padding to OFF when you create tables and columns, the behavior becomes more complex. Fortunately, the presence or absence of trailing spaces does not affect string comparisons in the WHERE clause, because whatever is set, they are always ignored. It will not have a great impact on the comparison either. The main effects of turning OFF ANSI? Padding are as follows:
CHAR NOT NULL and BINARY NOT NULL columns are populated when inserting data and are not subsequently trimmed (same behavior as ANSI standard)
When retrieved, nullable CHAR and BINARY columns are trimmed (so, of course, they are no longer populated at insert time). You will lose any trailing spaces or intentionally added zeros
The VARBINARY and VARCHAR columns are trimmed when retrieved, so you lose any trailing spaces or intentionally added zeros
If you need persuasion, we can prove all of this.
PRINT 'Creating a temporary table with ANSI_PADDING ON';
SET ANSI_PADDING ON;
SET NOCOUNT ON;
CREATE TABLE #OnAnsiPaddingTest
(
TenCharsNull CHAR(10) NULL,
TenCharsNotNull CHAR(10) NOT NULL,
TenVarcharNull VARCHAR(10) NULL,
TenVarcharNotNull VARCHAR(10) NOT NULL,
TenVarbinaryNull VARBINARY(10) NULL,
TenVarbinaryNotNull VARBINARY(10) NOT NULL
);
<a id="post-1115931-_Hlk33089691"></a>PRINT 'Now creating identical temp table with ANSI_PADDING OFF';
SET ANSI_PADDING OFF;
SET NOCOUNT ON;
CREATE TABLE #OffAnsiPaddingTest
(
TenCharsNull CHAR(10) NULL,
TenCharsNotNull CHAR(10) NOT NULL,
TenVarcharNull VARCHAR(10) NULL,
TenVarcharNotNull VARCHAR(10) NOT NULL,
TenVarbinaryNull VARBINARY(10) NULL,
TenVarbinaryNotNull VARBINARY(10) NOT NULL
);
PRINT 'Now creating identical table variable with ANSI_PADDING OFF';
DECLARE @OffAnsiPaddingTest table
(
TenCharsNull CHAR(10) NULL,
TenCharsNotNull CHAR(10) NOT NULL,
TenVarcharNull VARCHAR(10) NULL,
TenVarcharNotNull VARCHAR(10) NOT NULL,
TenVarbinaryNull VARBINARY(10) NULL,
TenVarbinaryNotNull VARBINARY(10) NOT NULL
);
PRINT 'Switching ANSI_PADDING back on'
SET ANSI_PADDING ON;
PRINT 'inserting into both tables'
INSERT INTO #OffAnsiPaddingTest
(TenCharsNull, TenCharsNotNull, TenVarcharNull, TenVarcharNotNull,
TenVarbinaryNull, TenVarbinaryNotNull)
VALUES
('First ', 'Second ', 'Third ', 'fourth ', 0x1234560000,
0x1234560000), --padded to 10
('First', 'Second', 'Third', 'fourth', 0x123456, 0x123456); --no trailing padding
INSERT INTO #OnAnsiPaddingTest
(TenCharsNull, TenCharsNotNull, TenVarcharNull, TenVarcharNotNull,
TenVarbinaryNull, TenVarbinaryNotNull)
VALUES
('First ', 'Second ', 'Third ', 'fourth ', 0x1234560000,
0x1234560000), --padded to 10
('First', 'Second', 'Third', 'fourth', 0x123456, 0x123456); --no trailing padding
INSERT INTO @OffAnsiPaddingTest
(TenCharsNull, TenCharsNotNull, TenVarcharNull, TenVarcharNotNull,
TenVarbinaryNull, TenVarbinaryNotNull)
VALUES
('First ', 'Second ', 'Third ', 'fourth ', 0x1234560000,
0x1234560000), --padded to 10
('First', 'Second', 'Third', 'fourth', 0x123456, 0x123456); --no trailing padding
PRINT 'Selecting from first table, created with ANSI padding ON (<> shows extent of string)'
SELECT '<' + Coalesce(TenCharsNull, '') + '> <' + TenCharsNotNull + '> <'
+ Coalesce(TenVarcharNull, '') + '> <' + TenVarcharNotNull + '> <'
+ Coalesce(Convert(VARCHAR(MAX), TenVarbinaryNull, 2), 'null') + '> <'
+ Convert(VARCHAR(MAX), TenVarbinaryNotNull, 2) + '>' AS AnsiPaddingOn
FROM #OnAnsiPaddingTest AS APT;
PRINT 'Selecting from second table, created with ANSI padding OFF: Same query'
SELECT '<' + Coalesce(TenCharsNull, 'null') + '> <' + TenCharsNotNull + '> <'
+ Coalesce(TenVarcharNull, 'null') + '> <' + TenVarcharNotNull + '> <'
+ Coalesce(Convert(VARCHAR(MAX), TenVarbinaryNull, 2), 'null') + '> <'
+ Convert(VARCHAR(MAX), TenVarbinaryNotNull, 2) + '>' AS AnsiPaddingOff
FROM #OffAnsiPaddingTest AS APT;
PRINT 'Selecting from table variable, created with ANSI padding OFF: Same query'
SELECT '<' + Coalesce(TenCharsNull, 'null') + '> <' + TenCharsNotNull + '> <'
+ Coalesce(TenVarcharNull, 'null') + '> <' + TenVarcharNotNull + '> <'
+ Coalesce(Convert(VARCHAR(MAX), TenVarbinaryNull, 2), 'null') + '> <'
+ Convert(VARCHAR(MAX), TenVarbinaryNotNull, 2) + '>' AS AnsiPaddingOff
FROM @OffAnsiPaddingTest AS APT;
DROP TABLE #OnAnsiPaddingTest;
DROP TABLE #OffAnsiPaddingTest;You will see the following message:
Create a temporary table using ANSI? Padding on
Now use ANSI? Padding off to create the same temporary table
Now use ANSI? Padding off 'to create the same table variable
Reopen ANSI? Padding
Insert two tables
Select from the first table and create with ANSI padding ON (< display string range)
Select from the second table and Use ANSI fill OFF to create: same query
Select from table variables and fill OFF with ANSI to create: same query
The result is this:
Correct. As always, when creating a table with ANSI? Padding set to ON, the first row that we intentionally add trailing spaces or zeros is not clipped. The second line without trailing spaces is consistently added as CHAR and BINARY data types, whether NULL is allowed or not.
The second result comes from the table that was created when ANSI? Padding was turned off. The first CHAR column that can be empty has been trimmed. CHAR columns with NOT NULL constraints are filled with spaces. The VARBINARY column trims trailing zeros whether it can be empty or not. VARCHAR column trims trailing spaces.
The third result comes from a table variable that was also created by setting ANSI? Padding to OFF, which is completely harmless. Regardless of the setting, its behavior is ANSI compliant.
If you're trying to accept all the rules and exceptions, you're not alone.
Find exception columns created with ANSI? Padding off
When accessing a table, the query behavior is consistent no matter what setting you make to ANSI? Padding. The setting will remain in the table column and the connection setting will be ignored. No matter what connection settings are used to access the "legacy" database, it can run consistently. We can check what happened to the temporary table by querying the metadata.
USE tempdb SELECT S.name AS TheColumn, Object_Schema_Name(S.object_id) + '.' + Object_Name(S.object_id) AS TableName, is_ansi_padded FROM sys.columns AS S INNER JOIN sys.tables AS t ON t.object_id = S.object_id WHERE system_type_id IN (165, 167, 173, 175) AND is_ansi_padded = 0; --ansi padding off!!
Columns in the sys.columns view are 1 if ANSI? Padding is on or 0 if it is off. This query will tell you very quickly if the database has an exception column set to ANSI "padding off" (just remove the first line, "USE tempdb").
conclusion
Always use set ANSI? Padding before executing the CREATE statement in the table until the function is finally removed from SQL Server, unless the danger of ANSI? Padding being turned off intentionally or unintentionally disappears On sets the connection to ANSI behavior, but should not be used anywhere else because the setting and support are scheduled to be deprecated and you will not be able to turn off ANSI compatibility at this time.