I error out on this as well, but even if I run SET ANSI_PADDING ON this code errors out as well.

But try this:

SET Ansi_Padding ON

CREATE TABLE #T(k VARCHAR(10))

INSERT #T

SELECT 'a' UNION ALL

SELECT 'a ' UNION ALL

SELECT 'a ' UNION ALL

SELECT 'a ' UNION ALL

SELECT 'a '

SELECT CASE WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'

ELSE 'ON'

END AS ansi_padding_setting, k, DATALENGTH(k) AS datalength_k, LEN(k) AS len_k FROM #t WHERE k LIKE 'a_'

DROP TABLE #t

GO

SET ansi_padding OFF

CREATE TABLE #T(k VARCHAR(10))

INSERT #T

SELECT 'a' UNION ALL

SELECT 'a ' UNION ALL

SELECT 'a ' UNION ALL

SELECT 'a ' UNION ALL

SELECT 'a '

SELECT CASE WHEN SESSIONPROPERTY('ANSI_PADDING') = 0 THEN 'Off'

ELSE 'ON'

END AS ansi_padding_setting, k, DATALENGTH(k) AS datalength_k, LEN(k) AS len_k FROM #t WHERE k LIKE 'a_'

DROP TABLE #t

With ANSI_PADDING ON this eliminates the first row, but with ANSI_PADDING OFF it eliminates all the rows. Also you can see that the DataLength function returns 1-5 with ANSI_PADDING ON and 1 with ANSI_PADDING OFF. Using "=" does not seem to be affected by the setting, but "LIKE" is affected.

Notice that I am creating the temporary table AFTER the SET.

The question is when creating a table with SSMS which defaults to ANSI_PADDING ON is that how the table is created or is it created with the default on the database? I will be testing this in one of my already created DB's now.