ANSI_PADDING

Question

ANSI_PADDING

Rechana Rajan

SSCertifiable

Points: 7709
More actions
December 7, 2016 at 6:17 am

#316612

When i script out tables from my local some have ANSI_PADDING ON and some dont have that mentioned before the create table state. Can anyone help me understand why?
As per my understanding the ANSI_PADDING is ON by default for char,varchar .I tried to create a table with ANSI_PADDING off and it failed . What are all the scenarios when ANSI_PADDING is OFF.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

Rechana Rajan (12/7/2016)
When i script out tables from my local some have ANSI_PADDING ON and some dont have that mentioned before the create table state. Can anyone help me understand why?
As per my understanding the ANSI_PADDING is ON by default for char,varchar .I tried to create a table with ANSI_PADDING off and it failed . What are all the scenarios when ANSI_PADDING is OFF.

Why did it fail? It must have given you an error message.

You should be able to create a table with ANSI_PADDING = OFF (not recommended).

Check your settings in the options panel (see attached figure)

Igor Micev,My blog: www.igormicev.com

Rechana Rajan SSCertifiable Points: 7709 More actions · Answer 2

Igor Micev (12/7/2016)
Rechana Rajan (12/7/2016)
When i script out tables from my local some have ANSI_PADDING ON and some dont have that mentioned before the create table state. Can anyone help me understand why?
As per my understanding the ANSI_PADDING is ON by default for char,varchar .I tried to create a table with ANSI_PADDING off and it failed . What are all the scenarios when ANSI_PADDING is OFF.
Why did it fail? It must have given you an error message.
You should be able to create a table with ANSI_PADDING = OFF (not recommended).
Check your settings in the options panel (see attached figure)

Thanks Igor.

It failed because of below trigger

CREATE TRIGGER [Audit_DDL] ON DATABASE FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE AS

DECLARE @event xml; SET @event = EVENTDATA();

INSERT INTO Audit_DDL_Events VALUES ( REPLACE(CONVERT(varchar(50), @event.query('data(/EVENT_INSTANCE/PostTime)')), 'T', ' ') , CONVERT(varchar(150), @event.query('data(/EVENT_INSTANCE/LoginName)')) , CONVERT(varchar(150), @event.query('data(/EVENT_INSTANCE/UserName)')) , CONVERT(varchar(150), @event.query('data(/EVENT_INSTANCE/DatabaseName)')) , CONVERT(varchar(150), @event.query('data(/EVENT_INSTANCE/SchemaName)')) , CONVERT(varchar(150), @event.query('data(/EVENT_INSTANCE/ObjectName)')) , CONVERT(varchar(150), @event.query('data(/EVENT_INSTANCE/ObjectType)')) , CONVERT(varchar(max), @event.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')) );

GO

Rechana Rajan SSCertifiable Points: 7709 More actions · Answer 3

Igor Micev (12/7/2016)
Rechana Rajan (12/7/2016)
When i script out tables from my local some have ANSI_PADDING ON and some dont have that mentioned before the create table state. Can anyone help me understand why?
As per my understanding the ANSI_PADDING is ON by default for char,varchar .I tried to create a table with ANSI_PADDING off and it failed . What are all the scenarios when ANSI_PADDING is OFF.
Why did it fail? It must have given you an error message.
You should be able to create a table with ANSI_PADDING = OFF (not recommended).
Check your settings in the options panel (see attached figure)

My question is why ANSI_PADDING option is showing for some tables and not for others when i script out