LIKE and = Operators

Question

LIKE and = Operators

Viewing 15 posts - 16 through 30 (of 40 total)

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

rfr.ferrari SSCertifiable Points: 6879 More actions · Answer 1

hi everbody!!!

I was testing some possibilities and came across the following situation: when filled with the same number of characters that were defined in the datatype then with "PADDING OFF" is not made the trim, but if the number of characters is different from the maximum value of then the datatype "PADDING OFF" running trim. Exmple:

this is not work:

----------------

print 'PADDING OFF'

go

set ansi_padding off;

create table #

(

num_of_spaces char(1),

test_string varchar(5)

)

set ansi_padding off;

with t(num_of_spaces, string_values) as

(

select 0, 'X' union all

select 1, 'X ' union all

select 2, 'X ' union all

select 3, 'X ' union all

select 4, 'X '

)

insert into #

select * from t

select *, DATALENGTH(test_string) as qty_charcts from #

drop table #

but this is work:

---------------

print 'PADDING OFF'

go

set ansi_padding off;

create table #

(

num_of_spaces char(1),

test_string varchar(6)

)

set ansi_padding off;

with t(num_of_spaces, string_values) as

(

select 0, 'X' union all

select 1, 'X ' union all

select 2, 'X ' union all

select 3, 'X ' union all

select 4, 'X '

)

insert into #

select * from t

select *, DATALENGTH(test_string) as qty_charcts from #

drop table #

this is a programming error!! this BUG!!!

[font="Times New Roman"]rfr.ferrari[/font]
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!

SanDroid SSChampion Points: 10068 More actions · Answer 2

rfr.ferrari (12/6/2010)
this is a programming error!! this BUG!!!

This has been the expected behavior of this "feature" for the last 15+ years in my world.

Was usefull when data storage and query memory where very expensive and limited by the x86 architecture.

Now it is deprecated.. and there was much rejoicing...

rfr.ferrari SSCertifiable Points: 6879 More actions · Answer 3

althout this is a deprecated feature, but this is very used!!! in BOL the default for newer versions will is ON and not OFF like is now!!!

[font="Times New Roman"]rfr.ferrari[/font]
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!

SanDroid SSChampion Points: 10068 More actions · Answer 4

rfr.ferrari (12/6/2010)
althout this is a deprecated feature, but this is very used!!! in BOL the default for newer versions will is ON and not OFF like is now!!!

Do you mean when creating tables, or when inserting data from a connected client?

UMG Developer SSChampion Points: 13482 More actions · Answer 5

Thanks for the question! It took me a while to find that you are supposed to run the insert twice, changing one line the second time, but once I figured that out I got the question correct. 🙂

I am surprised by how few people have gotten it correct, which I guess shows most people don't run in to this type of thing.

john.moreno Default port Points: 1485 More actions · Answer 6

I'm getting different results depending upon whether I run the script against a SQL 2005 or SQl 2008 server (no trailing spaces on 2008, no results from second select). Not sure why...

Ron McCullough SSC Guru Points: 63877 More actions · Answer 7

john.moreno

hmm I tested repeatedly on both 5K and 8K before submitting the question .. always produced the identical answers on all servers...

Are you sure you executed the first 6 lines (Settings each followed by the GO of the posted code, on each server?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

john.moreno Default port Points: 1485 More actions · Answer 8

bitbucket-25253 (12/6/2010)
john.moreno
hmm I tested repeatedly on both 5K and 8K before submitting the question .. always produced the identical answers on all servers...
Are you sure you executed the first 6 lines (Settings each followed by the GO of the posted code, on each server?

Well, further checking shows that it's not the server, it's whether the code has been run before.

use adventureworks

set ansi_nulls on

go

set quoted_identifier on

go

create table [dbo].[qod35] ([id] int identity(1,1) not null,

data_insert_setting bit, trailing_characters varchar(20) null,

test_string varchar(5) null,

constraint [pk__qod35] primary key clustered ([id] asc)

with (pad_index = off, statistics_norecompute = off,

ignore_dup_key=off, allow_row_locks= on, allow_page_locks= on) on [primary]) on [primary]

go

set ansi_padding on

go

declare @session bit;

set @session = CONVERT(bit, sessionproperty('ansi_padding'));

insert into [dbo].qod35 (data_insert_setting, trailing_characters, test_string)

select @session, 'None', 'x' union all

select @session, '1 space', 'x ' union all

select @session, '2 spaces', 'x ' union all

select @session, '3 spaces', 'x ' union all

select @session, '3 spaces & 1 tab', 'x ' + CHAR(9)

;

go

set ansi_padding off

go

declare @session bit;

set @session = CONVERT(bit, sessionproperty('ansi_padding'));

insert into qod35 (data_insert_setting, trailing_characters, test_string)

select @session, 'None', 'x' union all

select @session, '1 space', 'x ' union all

select @session, '2 spaces', 'x ' union all

select @session, '3 spaces', 'x ' union all

select @session, '3 spaces & 1 tab', 'x ' + CHAR(9)

;

select data_insert_setting, '<' + test_string + '>' as '= x with 1 space' from qod35

where test_string = 'x ';

select data_insert_setting, '<' + test_string + '>' as '= x with 1 space' from qod35

where test_string like 'x '

drop table [dbo].[qod35]

The first time I execute a batch (new connection), it gives me your expected results, if I immediately run it again (i.e. don't close connection first) then I get zero results for the second select.

SanDroid SSChampion Points: 10068 More actions · Answer 9

john.moreno (12/6/2010)
bitbucket-25253 (12/6/2010)
john.moreno
hmm I tested repeatedly on both 5K and 8K before submitting the question .. always produced the identical answers on all servers...
Are you sure you executed the first 6 lines (Settings each followed by the GO of the posted code, on each server?
Well, further checking shows that it's not the server, it's whether the code has been run before.

John,

This would be the expected result if the connection default on your server was ANSI_PADDING ON or you started out with ANSI_PADDING ON when the Table is created the first time, but have it explicitly set to OFF when the table is created the second time.

I think this was what HUGO was talking about in his observations earlier today.

john.moreno Default port Points: 1485 More actions · Answer 10

SanDroid,

That's it exactly. Setting ansi_padding immediately before the table is created results in repeatable results.

SanDroid SSChampion Points: 10068 More actions · Answer 11

john.moreno (12/6/2010)
SanDroid,
That's it exactly. Setting ansi_padding immediately before the table is created results in repeatable results.

I understand that. My experiance with this feature in the past has taught me to make sure the Table Create Scripts I use validate this set option before creation.

You can view all current user options with the following command:

DBCC USEROPTIONS

I have access to three clean default SQL Server version installs. 200, 2005, and 2008.

All of them have the following default SET options for user connections.

textsize2147483647

languageus_english

dateformatmdy

datefirst7

quoted_identifierSET

arithabortSET

ansi_null_dflt_onSET

ansi_defaultsSET

ansi_warningsSET

ansi_paddingSET

ansi_nullsSET

concat_null_yields_nullSET

isolation levelread committed

SQL Server 2005 and 2008 have the extra user option:

lock_timeout-1

FYI: For those that do not know, a row and a value of SET = ON. No row No value No ON. 😎

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 12

Nils Gustav Stråbø (12/6/2010)
Good question, thanks!
Is it just me, or is there a bug in SQL Server when ANSI_PADDING is OFF (not that I've ever used it)?
(... snip ...)
I suspect this is a bug as I see no logic in what is happening, but perhaps some of the more enlightened people in here have a good explanation for this strange behavior.

I agree that this is a bug, and I suggest you file it on Connect. Note that I don't really expect a fix, since the OFF behaviour for ANSI_PADDING is deprecated anyway.

If you play a bit with the number of spaces in the various rows in the code you posted, you'll find that actually all strings that have the longest length retain their trailing characters (if any()), and all other strings are trimmed. Also, if you replace the INSERT INTO ... SELECT with a SELECT INTO SomeTable ..., and then use EXEC sp_help to query the properties of that table, you'll find that the column type and length chosen for the result of the UNION ALL series uis varchar, and the length of the longest value. So it seems as if varchar values that match the longest length are not trimmed, and shorter values are.

However, this does not happen if you use single-row inserts (with INSERT VALUES or INSERT SELECT), nor if you fetch the rows from some other table. I have only seen it happen with INSERT SELECT ... UNION ALL SELECT ... and variations on that theme.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 13

SanDroid (12/6/2010)
Hugo Kornelis (12/6/2010)
2. The explanation does not mention how important it is to make ANSI_PADDING is enabled at the time the column is created. If it's not, the results are different. This is very important to stress - the ANSI_PADDING setting is governed by a combination of the value when the column was crteated and the value when the INSERT is executed.
Hugo, Isn't it important to make certain ANSI_PADDING is ON when creating any table with character values in it?

Good catch, SanDroid!

Let me clarify what I meant with the above quote - what I meant is that in order to reproduce the behaviour in the QotD, the table must be created with the ANSI_PADDING option set to ON.

And to answer your question - since ANSI_PADDING OFF is deprecated behaviour (it will be removed in a future version of SQL Server, and it also has never worked for nchar and nvarchar data), it is indeed recommended to always have this option on when creating tables. Or when entering data. Or when doing anything else in the database, actually 😎

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Carlo Romagnano SSC-Insane Points: 22597 More actions · Answer 14

When I realize that a command or option is deprecated, I stop to waste my time to use it.

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 15

Hardy21

SSCrazy Eights

Points: 9708

December 7, 2010 at 3:06 am

#1259429

Nice question. thanks

Thanks