Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

String length? Expand / Collapse
Author
Message
Posted Monday, April 12, 2010 10:05 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 21,209, Visits: 14,894
Awesome Loop.

Great question.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #901738
Posted Monday, April 12, 2010 10:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, June 6, 2014 7:58 AM
Points: 1,837, Visits: 3,420
da-zero (4/12/2010)
A small question:

isn't the variable @InputStr stored somewhere? As the loop goes on forever, at some point in time that variable will become too large to reside in memory/physical location. So some sort of out of memory exception has to take place, right?
SQL Server controls the amount of memory each request gets, and will spill to tempdb if the required memory exceeds the memory your request needs. So, no, you will not run out of memory. I did some testing, and it seems that when a variable exceeds 512KB it will start to use tempdb for storage. I have not found any information that can confirm my tests.

Also, the trailing spaces are removed for nvarchar and varchar data types, so the length of the variable in this specific example will always be six bytes.
Post #901752
Posted Monday, April 12, 2010 2:17 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 12:55 AM
Points: 771, Visits: 504
Awesome question. Thanks.
Post #901955
Posted Monday, April 12, 2010 11:36 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:03 PM
Points: 2,262, Visits: 5,405
Wow... awesome question.. i misread "while" to "If" in the question and was thinking why this one will end up in a loop.. lol..

good loopy question
Post #902136
Posted Tuesday, April 13, 2010 3:35 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 5, 2011 8:18 AM
Points: 582, Visits: 359
Surely the only sensible output from running this is: "Query was cancelled by user."
Post #902231
Posted Tuesday, April 13, 2010 3:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:03 PM
Points: 2,262, Visits: 5,405
Ben Leighton (4/13/2010)
Surely the only sensible output from running this is: "Query was cancelled by user."


precisely..
Post #902238
Posted Tuesday, April 13, 2010 9:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 2,328, Visits: 2,658
Paul White NZ (4/11/2010)
Very clever question - with some well-thought-out wrong answers

First one to whinge about the explanation being 'incomplete' gets a banana

Well done.


Yes, this was a good question, including the possible answers. I need to better understand use of LEN versus DATALENGTH.

- webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #902484
Posted Thursday, April 15, 2010 5:34 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:00 AM
Points: 8,551, Visits: 9,043
Nice question. Fun.


Tom
Post #903913
Posted Thursday, April 15, 2010 5:37 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:00 AM
Points: 8,551, Visits: 9,043
Paul White NZ (4/12/2010)
Christian Buettner-167247 (4/12/2010)
Paul White NZ (4/11/2010)
First one to whinge about the explanation being 'incomplete' gets a banana

Me want the banana *whingingabouttheexplanation*! :D

No results returned, as an infinite loop is invoked. But even if there was no infinite loop, no results would be returned, as the variable is not defined as OUTPUT.


But still, good question & explanation. More of that please!

Excellent, Christian!



Drat , Christian beat me to it! Now there's no point in dreaming up some monkey business with the explanation.


Tom
Post #903914
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse