January 30, 2014 at 11:42 pm
On one of my development servers:
Microsoft SQL Server 2005 - 9.00.5057.00 (X64)
Mar 25 2011 13:33:31
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
The following code works as expected (with some rather odd length results I won't go into now):
Declare @s-2 VarChar(8000);
Set @s-2='sqlservercentral.com';
Select @s-2,Len(@s),DataLength(@s),Cast(@s as VarBinary);
Set @s-2=Replace(@s,'.',Char(0));
Select @s-2,Len(@s),DataLength(@s),Cast(@s as VarBinary);
Set @s-2=Replace(@s,Char(0),'.');
Select @s-2,Len(@s),DataLength(@s),Cast(@s as VarBinary);
However, change the @s-2 declaration to VarChar(max) and my systems hangs indefinitely burning up one of my CPUs!?!
Declare @s-2 VarChar(max);
Set @s-2='sqlservercentral.com';
Select @s-2,Len(@s),DataLength(@s),Cast(@s as VarBinary);
Set @s-2=Replace(@s,'.',Char(0));
Select @s-2,Len(@s),DataLength(@s),Cast(@s as VarBinary);
Set @s-2=Replace(@s,Char(0),'.');
Select @s-2,Len(@s),DataLength(@s),Cast(@s as VarBinary);
Incidentally, both versions of the code work on SQL 2008 and SQL 2012.
Does this happen to anyone else? Does anyone have an explanation? And yes, we are migrating to SQL 2012...
January 31, 2014 at 12:07 am
did you checked it on any other machine?
January 31, 2014 at 10:08 am
twin.devil (1/31/2014)
did you checked it on any other machine?
Yes, unfortunately I only have access to SQL Serer 2005 64-bit servers so I couldn't test it on a 32-bit server, but it does the same thing on every SQL 2005 server I could find. As stated in my initial post, the code does work as expected on newer versions of SQL server.
I was just wondering if others have stumbled across this behavior...
February 2, 2014 at 11:31 pm
i have checked this on 2005, 2008, 2008R2, 2012(all the x64 bit versions) and it worked perfectly. Its a very strange behavior thou. try to put the statistic on for the query and check what is happening in the background.
February 7, 2014 at 7:17 am
same on a 2005 32bit
February 18, 2014 at 7:31 am
Got the same CPU burn on our x64 2005 Enterprise servers and my 2008 x64 laptop server.
February 18, 2014 at 11:05 am
Nothing constructive to add, just wanted to give you props for the thread name... 😀
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply