kcaran.com

SQL Server: Padding of data in columns of type ‘varchar’

I’ve been having some trouble with the way Microsoft SQL Server pads strings stored in its tables with spaces. Generally, I would assume that columns of type ‘CHAR’ would be a fixed length (and therefore padded), while columns of type ‘VARCHAR’ would be variable length. But I would be wrong.

The default behavior for SQL Server 7.0 and Server 2000 is that CHAR’s and VARCHAR’s are padded to their maximum lengths. To make the VARCHAR columns unpadded, the ‘ansi_padding’ property needs to be set to ‘off’. Unfortunately, you must do this every time you open a session with the database server. There isn’t a way to force ANSI padding off as the default.

Also note that the ANSI padding property must be turned off when the table is created. There isn’t a way to change this property for pre-existing tables without recreating them and copying the data.

Here’s a SQL example:

  set ansi_padding off;
  CREATE TABLE dbo.pwrdesk (
    ou                   varchar(55) NOT NULL,
    parent_broker        char(5) NOT NULL,
    rollout              int NOT NULL,
    startpage            int NOT NULL,
    mediaview            int NOT NULL
  );
  GO

In this example, the ‘parent_broker’ column is padded to 5 characters, but the ‘ou’ column is not (because it is of type ‘varchar’).

There are a couple of ways to check if a column uses ANSI padding:

  select columnproperty(object_id('<tablename>'),'<columnname>','UsesAnsiTrim')

This returns a ‘1’ if the column is right-padded, or a ‘0’ if it is trimmed.

  sp_help <tablename>

This returns all the properties of the database table, including TrimTrailingBlanks, which is ‘yes’ if ANSI padding is off, and ‘no’ otherwise.

Finally, here’s an example of a SQL statement that right trims all the existing data in a table:

  update pwrdesk set ou = rtrim(ou);

Last Updated: Mon, 26 Sep 2005