What Is ANSI_PADDING

Facebooktwitterredditpinterestlinkedinmail

If you work with stored procedures or if you script objects in SQL Server, you have probably seen SET statements that turn on/off some crazy thing called ANSI_PADDING. If you’re like most people, you just ignore this because it works. I wanted to take a few minutes to explain what this ANSI_PADDING thing is.

 
What Is ANSI_PADDING?
ANSI_PADDING is an option that controls how VARCHAR and VARBINARY values are stored. If ANSI_PADDING is turned on, then SQL Server will NOT trim the trailing spaces when it inserts into a VARCHAR field. Similarly, it will NOT trim trailing nulls when it inserts into a VARBINARY field.

 
Setting the ANSI_PADDING only affects the spaces on inserts. It does NOT affect comparisons.

Syntax For Setting The ANSI_PADDING:

SET	ANSI_PADDING ON
GO

INSERT
INTO	Animal
	(AnimalName)
VALUES	('Dog    ')

GO

SET	ANSI_PADDING OFF
GO