More ANSI_PADDING Tables

Question

More ANSI_PADDING Tables

Steve Jones - SSC Editor

SSC Guru

Points: 736172
More actions
January 20, 2023 at 12:00 am

#4130858

Comments posted to this topic are about the item More ANSI_PADDING Tables

Viewing 7 posts - 1 through 6 (of 6 total)

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

Rune Bivrin SSCrazy Eights Points: 8511 More actions · Answer 1

The correct answer to this will depend on the setting of ANSI_NULL_DFLT_ON, since the NULLability of the CHAR column isn't specified.

Just because you're right doesn't mean everybody else is wrong.

JKB Ten Centuries Points: 1134 More actions · Answer 2

I think you might be missing '2' in the answer...;0)

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 3

I noticed that the actual answer did not appear in the results. I tried to guess the intent of the question, but was wrong.

Rune Bivrin SSCrazy Eights Points: 8511 More actions · Answer 4

Scott Arendt wrote:

I noticed that the actual answer did not appear in the results. I tried to guess the intent of the question, but was wrong.

There are two results that can be correct, depending on if the CHARCOL column is nullable or not. Both are among the provided alternatives.

If connecting through the native client or ODBC, the ANSI_NULL_DEFAULT_ON setting is ON after connection, so if this hasn't been explicitly changed by the connecting application, you will get the behaviour where trailing blanks are trimmed. If connecting through DB-library, however, ANSI_NULL_DEFAULT_ON will be OFF, and in that case the CHAR column will be NOT NULL, and the value will be padded with blanks to the declared size.

To quote from Books Online:

For a more reliable operation of Transact-SQL scripts that are used in databases with different nullability settings, it is better to specify NULL or NOT NULL in CREATE TABLE and ALTER TABLE statements.

Just because you're right doesn't mean everybody else is wrong.

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 5

Rune Bivrin wrote:

The correct answer to this will depend on the setting of ANSI_NULL_DFLT_ON, since the NULLability of the CHAR column isn't specified.

The default is NULL for columns if not specified for most drivers. As you noted, this might be different, but the question is based around defaults. I'll adjust the question to say SSMS, which connects with a .NET provider.

jschmidt 17654 SSCommitted Points: 1656 More actions · Answer 6

Rune Bivrin wrote:

Scott Arendt wrote:
I noticed that the actual answer did not appear in the results. I tried to guess the intent of the question, but was wrong.
There are two results that can be correct, depending on if the CHARCOL column is nullable or not. Both are among the provided alternatives.
If connecting through the native client or ODBC, the ANSI_NULL_DEFAULT_ON setting is ON after connection, so if this hasn't been explicitly changed by the connecting application, you will get the behaviour where trailing blanks are trimmed. If connecting through DB-library, however, ANSI_NULL_DEFAULT_ON will be OFF, and in that case the CHAR column will be NOT NULL, and the value will be padded with blanks to the declared size.
To quote from Books Online:
For a more reliable operation of Transact-SQL scripts that are used in databases with different nullability settings, it is better to specify NULL or NOT NULL in CREATE TABLE and ALTER TABLE statements.

Is DB-library used anymore? I recall it from many years ago, but haven't seen it mentioned recently.