SQL Server Performance Forum – Threads Archive
Set ansi_padding
Are there any difference between SQL Server 7 and SQL Server 2000 regarding the set ansi_padding? I have the following script: set ansi_padding ondeclare @x integer
select @x = 1
select case when @x = 1 then ‘test ‘ else ‘test2 ‘ end + ‘result’ Running the script in SQL Server 2000 will get this: test result Running the script in SQL Server 7 will get this: testresult Why are the results be different?
I dont think its the ansi padding that is different, I think its the defaults its using in teh case statement
I get the results as you do above in sql7 and 2000, however, if I run the below in SQL7, I keep the padded space
declare @value1 varchar(10)
declare @value2 varchar(10) select @value1 = ‘Test ‘, @value2 = ‘result’
select @value1 + @value2
Because of this, I believe the difference is in how SQL ‘declares’ the datatype of the results of your case statement. Chris
Chris, Thank you for the reply. I have also done testing similar to yours. I can only conclude that this is something to do with case statement. If I run the following script: set ansi_padding on
declare @x integer, @y varchar(20)
select @x = 1
select @y = case when @x = 1 then ‘test ‘ else ‘test2 ‘ end + ‘result’
print @y SQL Server 7 will yield ‘testresult’ but SQL Server 2000 will yield ‘test result’.
Ah, not exactly, but close to what I was getting to. Try this one… declare @x integer, @y varchar(20), @z varchar(20)
select @x = 1
select @y = ‘test ‘, @z = ‘test2 ‘
select case when @x = 1 then @y else @z end + ‘result’ Test Result on either server Its what sql defaults the first result of the ‘Then’ in the case, not the case statement itself that is the issue.
Chris, I have tried your script and both SQL Server 7 and SQL Server 2000 yields ‘test result’ regardless the setting of set ansi_padding. To me, it is a bug in SQL Server 7 to remove the space. Do you agree?
What does this return on sql 7 ? I dont have a v7 to play with. I think Chris has identified the problem. declare @x integer
select @x = 1
select case when @x <> 1 then ‘test’ else ‘test123’ end + ‘result’ Also as far as I know ANSI_PADDING does not come into play here, only with column definitions, but someone please correct me if im wrong.
Chappy, declare @x integer
select @x = 1
select case when @x <> 1 then ‘test’ else ‘test123’ end + ‘result’ This should return ‘test123result’ regardless of the SQL Server version and any settings. I’m not sure what you’re trying to point out.[?]
No. I dont really consider it a bug. Its more how SQL 7 used to set default datatypes for memory used fields when they were not explicitly declared. They changed the way they did this in 2000. It could have been in response to clients requests, it could be because they’ve overall cleaned up and tightened up sql. SQL 7 and SQL 2000, while similar, are not the same product. The why is unknown.
Chris, I’m working with MS on this issue. The weird thing is that only the THEN clause will trim the space but not the ELSE clause. BTW where do you find out the SQL 2000 changed this area?
Thanks for your help.
Wasnt trying to point out anything really, I was curious as to how sql 7 would react to the sql I posted.
I was wondering whether, when the return type is a varchar of undefined length, whether it would base the varchar length on the varchar returned by the THEN clause
I just notice that if run this: set ansi_padding on — actually this can be on or off
declare @x integer
select @x = 1
select case when @x = 1 then ‘test ‘ else ‘test2’ end + ‘result’ It will yield ‘test result’ The difference between this script and my original script is that I remove the space in the ELSE value from ‘test2 ‘ to ‘test2’. Based on my testing in SQL Server 7, 1. If the WHEN expression is true and the length of the ELSE value is <= to the the length of the THEN value, the THEN value won’t get trimmed. 2. If the WHEN expression is not true and the length of the THEN value is <= to the length of the ELSE value, the ELSE value won’t get trimmed. SQL Server 2000 will behave like this if set ansi_padding is set to OFF.
]]>