CODEWITHSUNDEEP
sqlsql-serverstring
tgandrews
tgandrews

Reputation: 12670

Why is ANSI padding enabled by default in SQL Server?

Can someone explain to me what logic there is to enabling ANSI padding by default in SQL server.

Where the two code snippets below return true

if len('       ') = len('') begin
    print 'true'
end
else begin
    print 'false'
end

if '       ' = '' begin
    print 'true'
end
else begin
    print 'false'
end

EDIT: I do not care about that you can turn it off. Does anyone know why it is on? Is it a database design decision? Is it a database optimisation? What are the positives of this being used? It certainly doesn't make much sense to me - all I see are negatives.

Upvotes: 1

Views: 5069

Answers (2)

gonsalu
gonsalu

Reputation: 3194

To be compliant with the ANSI/ISO SQL-92 specification:

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.

Upvotes: 4

Tom Hunter
Tom Hunter

Reputation: 5918

Testing the behaviour:

SELECT SESSIONPROPERTY('ANSI_PADDING') AS [ANSI_PADDING]

SET ANSI_PADDING ON

IF '       ' = '' BEGIN
    PRINT 'TRUE'
END
ELSE BEGIN
    PRINT 'FALSE'
END

SET ANSI_PADDING OFF

IF '       ' = '' BEGIN
    PRINT 'TRUE'
END
ELSE BEGIN
    PRINT 'FALSE'
END

SET ANSI_PADDING ON

Output:

TRUE
TRUE

Upvotes: 2

Related Questions