Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Different behaviour of SQL Server when setting varchar size


Different behaviour of SQL Server when setting varchar size

Author
Message
ChrisTar
ChrisTar
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 84
Hi there

I hit my head on a strange behaviour of SQL Server: I have a large query fired from a .NET application. First, I realized, that .NET gets nothing back, while my test in Management Studio returned rows. Then I looked at the query in detail, reshaped them and found the difference:
The declare statement from .NET has sizes set for the varchar parameters, while my test in SSMS has not. That is the only difference. w00t

If i declare the varchar parameters in my testquery with size (.NET sets the size to the length of the value-string), the query also returns empty set. Independently of the size (even if I set them to the size the columns have in the tables or to max or the default size of varchar when not declared).

Also interesting is that it depends on the paramter: I can remove the size setting of one or two of two varchar parameters and the behaviour changes. Size setting of the third varchar parameter is not influencing the result. Difference: This third parameter concerns another table from the joins of the query. If I remove parts of the join (i.e. the table with the third varchar parameter, I always get a result.

It does also not depend if I execute the query by sp_executesql or declaring the variables and then running the T-SQL directly.

And another dependency: If I change the values of the parameters, the query gives results in both variants. I'm very confused!

Can anybody explain this change in behaviour? Why doesn't SSMS warn me about that if it si prevented from returning values by the size setting? Why does this change the behaviour?
Are there any problems with querylength? But then again, why doesn't SSMS warn about that? If I really have not to give sizes: How do I tell .NET not to set them? Because in the code I only give type varchar.

At the moment I cannot post the query itself because of table-, column- and parameternames representing our logic, but If needed I can rename these, still keeping length of the names in case of this beeing the problem?

Thanks in advance for any suggestions or other participation,
Chris
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
I can think of a few possibilities, but this is hard to comment on at such an abstract level. Could you post the query for us? That would make it much easier. Smile

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
ChrisTar
ChrisTar
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 84
Query is something like:
declare @p1 varchar(4), @p2 varchar(6)
set @p1 = '%ZH%'
set @p2 = '%8434%'

select * from table1 o left join table2 t on o.x=t.x
where o.canton like @p1 and o.postcode like @p2 and ...


If I drop (4) or (6) from above query, it outputs result rows. With both set, it does not.

After a long day I found the solution.
And I'm ashamed to tell, but the SQL Server must behave like this, since the query is not the same: What I was not aware: If you do not set the size of a varchar parameter, SQL Server defaults to varchar(1). Since in the query the two parameters affected by the size problem were like '%8434%' or like '%ZH%', this default has set the parameter value to '%', which is true for any string and therefore the query had results. In the other way, if I set the varcharsize or if it was set by .NET, the value of the parameter has been '%8434%' and then the output of no result rows simply was correct. I assumed it to be wrong since my query did return values... Unsure Letting away a join returned results because some restricting where clauses got removed by removing the join.

So sorry for troubling, but I really learned something out of that and I think some of you will too ;-) I'm glad having found a forum post somewhere deep in the net of a user pointing to the fact of default size 1 and wondering, where this strange default of 1 comes from...

Cheers, Chris
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search