Set ANSI_PADDING on a specific column (ANSI_PADDIN

问题:

Is it possible - using a single CREATE TABLE script - to set specific varbinary columns ANSI_PADDING = ON but others to ANSI_PADDING = OFF?

e.g.

CREATE TABLE PaddingX (
    ...
    ColumnA varbinary(max),
    ...    
    ColumnB varbinary(50)
)

I would like ColumnA to trim (padding OFF), but ColumnB to contain the full (padded) value - padding ON.

回答1:

You hace to do it in two steps:

SET ANSI_PADDING OFF
-- create the table without the columns that need ANSI padding
SET ANSI_PADDING ON
-- alter the table to add the columns that need ANDI passing

This is the only way to do it.

If you look at the remarks of SET ANSI_PADDING docs, you can read:

This setting affects only the definition of new columns. After the column is created, SQL Server stores the values based on the setting when the column was created. Existing columns are not affected by a later change to this setting.

NOTE: for adding new columns, you can see the A Example of ALTER TABLE docs.

回答2:

This appears to also work for a table with existing columns that need to be changed:

SET ANSI_PADDING ON 
GO 

ALTER TABLE [TableX] ALTER COLUMN [ColumnY] VARBINARY (50) NULL; 
GO 

SET ANSI_PADDING OFF 
GO 

Any comments? It appears to solve the issue...