LIKE and = Operators

Question

Post reply

LIKE and = Operators

Ron McCullough

SSC Guru

Points: 63877
More actions
December 4, 2010 at 2:06 pm

#229843

Comments posted to this topic are about the item LIKE and = Operators
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]
December 6, 2010 at 12:14 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 40 total)

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

Carlo Romagnano SSC-Insane Points: 22669 More actions · Answer 1

Carlo Romagnano

SSC-Insane

Points: 22669

December 6, 2010 at 2:03 am

#1258918

Thank you! Finally a very good question.

dawryn SSCertifiable Points: 5481 More actions · Answer 2

How can result have more rows than number of inserted rows :blush:

Carlo Romagnano SSC-Insane Points: 22669 More actions · Answer 3

dawryn (12/6/2010)
How can result have more rows than number of inserted rows :blush:

The INSERT SCRIPT should be executed twice: one with ANSI_PADDING option set to ON and another one to OFF.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

Tough question, but also a good question. Well done, Ron!

Two minor issues:

1. The documentation link points to the description for SQL Server 2000. This behaviour has not been changed in later versions, so it's not really very relevant. The version for SQL Server 2008R2 can be found at http://msdn.microsoft.com/en-us/library/ms187403.aspx

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.

Finally, an important notice for everyone who might contemplate using this feature in some creative way - don't! This setting has been put on the deprecation list in SQL Server 2008. In a future version, this setting will be removed and trying to set it off will generate an error. See the link I posted above.

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/

dawryn SSCertifiable Points: 5481 More actions · Answer 5

Carlo Romagnano (12/6/2010)
dawryn (12/6/2010)
How can result have more rows than number of inserted rows :blush:
The INSERT SCRIPT should be executed twice: one with ANSI_PADDING option set to ON and another one to OFF.

Have to read all comments next time :doze:

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

good question!! althought i did wrong, because i didn't read carefully!!!!! THE SECRET IS IN COMMENTS!!!

[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!

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 7

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)?

Let me demonstrate, and please correct me if there is something that I haven't understood.

BOL says that when ANSI_PADDING is OFF, trailing spaces are trimmed. Let me demonstrate.

We create a simple table #x with ansi padding on.

set ansi_padding on

create table #x(num_of_spaces char(1), test_string varchar(5))

Then we populate it with five rows. Notice that each string starts with and X, and each string has from 0 to 4 trailing spaces. Notice that ansi padding is off when we run the script, and for the demonstration I have also "switched" place on the row with 4 and 3 trailing spaces.

set ansi_padding off;

with t(num_of_spaces,string_value) as

(select '0','X'

union all

select '1','X '

union all

select '2','X '

union all

select '4','X '

union all

select '3','X '

)

insert into #x

select * from t

Five rows are now inserted into table #x, and based on BOL, all trailing spaces should be trimmed away. Let's check.

select *,datalength(test_string) from #x

Ouch!! Four rows return 1 as datalength, but one row returns 5 as datalength. But didn't BOL say that all trailing spaces should be trimmed? I've done some testing, and it seem like SQL Server fails to trim the longest string. This also happen if you SELECT directly from the CTE, instead of INSERTing it into a table. The longest string(s) never gets trimmed.

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.

Carlo Romagnano SSC-Insane Points: 22669 More actions · Answer 8

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)?
Let me demonstrate, and please correct me if there is something that I haven't understood.
BOL says that when ANSI_PADDING is OFF, trailing spaces are trimmed. Let me demonstrate.
We create a simple table #x with ansi padding on.
set ansi_padding on
create table #x(num_of_spaces char(1), test_string varchar(5))
Then we populate it with five rows. Notice that each string starts with and X, and each string has from 0 to 4 trailing spaces. Notice that ansi padding is off when we run the script, and for the demonstration I have also "switched" place on the row with 4 and 3 trailing spaces.
set ansi_padding off;
with t(num_of_spaces,string_value) as
(select '0','X'
union all
select '1','X '
union all
select '2','X '
union all
select '4','X '
union all
select '3','X '
)
insert into #x
select * from t
Five rows are now inserted into table #x, and based on BOL, all trailing spaces should be trimmed away. Let's check.
select *,datalength(test_string) from #x
Ouch!! Four rows return 1 as datalength, but one row returns 5 as datalength. But didn't BOL say that all trailing spaces should be trimmed? I've done some testing, and it seem like SQL Server fails to trim the longest string. This also happen if you SELECT directly from the CTE, instead of INSERTing it into a table. The longest string(s) never gets trimmed.
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.

That's true!

Unbelievable!

Also in sql2000 same behavior:

set ansi_padding on

create table #x(num_of_spaces char(1), test_string varchar(25))

set ansi_padding off;

insert into #x

select * from ((select '0','X'

union all

select '1','X '

union all

select '2','X '

union all

select '4','X '

union all

select '3','X '

))as t(a,b)

select *,datalength(test_string) from #x

drop table #x

Carlo Romagnano SSC-Insane Points: 22669 More actions · Answer 9

Nils Gustav Stråbø

Now, I understand because

set ansi_padding off is deprecated, it's buggy.

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 10

Buggy indeed, and probably has been from day one (?)

Too bad I don't have and 7 or older versions of SQL Server to test on.

SanDroid SSChampion Points: 10068 More actions · Answer 11

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?

SanDroid SSChampion Points: 10068 More actions · Answer 12

Nice question, actualy had to run the scripts to determine the difference the two inserts would have on the two selects.

My favorite thing about it was the lack of errors in both question and script! 😎

I may have had to type and think a litle bit, but it was worth it. 😛

Steve Jones - SSC Editor SSC Guru Points: 736188 More actions · Answer 13

Steve Jones - SSC Editor

SSC Guru

Points: 736188

December 6, 2010 at 9:23 am

#1259091

I updated the explanation to the SS@K8 R2 documentation