Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Stewart Campbell » String length?
Add to briefcase
29 posts, Page 3 of 3
«««123

String length? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
SQLRNNR
Posted Monday, April 12, 2010 10:05 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 18,853, Visits: 12,438
Awesome Loop.

Great question.


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

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
Nils Gustav Stråbø
Posted Monday, April 12, 2010 10:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:30 AM
Points: 1,788, Visits: 3,330
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
KevinC.
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 07, 2010 12:55 AM
Points: 771, Visits: 504
Awesome question. Thanks.
Post #901955
ColdCoffee
Posted Monday, April 12, 2010 11:36 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:37 PM
Points: 2,248, Visits: 5,352
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
Ben Leighton
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 05, 2011 8:18 AM
Points: 582, Visits: 359
Surely the only sensible output from running this is: "Query was cancelled by user."
Post #902231
ColdCoffee
Posted Tuesday, April 13, 2010 3:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:37 PM
Points: 2,248, Visits: 5,352
Ben Leighton (4/13/2010)
Surely the only sensible output from running this is: "Query was cancelled by user."


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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:08 PM
Points: 2,121, Visits: 2,226
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

-------------------
"The chemistry must be respected." - Walter White

"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
L' Eomot Inversé
Posted Thursday, April 15, 2010 5:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 7,182, Visits: 7,281
Nice question. Fun.

Tom
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.

http://es.linkedin.com/in/tomthomsonsoftware
Post #903913
L' Eomot Inversé
Posted Thursday, April 15, 2010 5:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 7,182, Visits: 7,281
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
Is minic a gheibheann béal oscailte dorn dúnta.
Is minig a cheapas beul fosgailte dòrn dùinte.

http://es.linkedin.com/in/tomthomsonsoftware
Post #903914
« Prev Topic | Next Topic »

Add to briefcase
29 posts, Page 3 of 3
«««123

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.