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 12345»»»

DECLARE - 1 Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2012 8:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:18 PM
Points: 5,589, Visits: 24,946
Comments posted to this topic are about the item DECLARE - 1

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1367377
Posted Tuesday, October 2, 2012 10:05 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
Thanks for this easy point Ron in the middle of the week

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1367390
Posted Tuesday, October 2, 2012 11:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:28 AM
Points: 13,570, Visits: 10,447
Oldie but a goodie



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1367404
Posted Tuesday, October 2, 2012 11:31 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
can anyone explain me why select len(@a) as 'declared' gives output as 1


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1367407
Posted Wednesday, October 3, 2012 12:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 2,631, Visits: 4,723
kapil190588 (10/2/2012)
can anyone explain me why select len(@a) as 'declared' gives output as 1


The answer to this is available in the explanation to the question( also shown below )

When n (Where n defines the string length) is not specified in a data definition or variable declaration statement, the default length is 1


Hence the two declaration shown below are equivalent

DECLARE @a VARCHAR ='xyz' 

DECLARE @a VARCHAR(1) = 'xyz'




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1367416
Posted Wednesday, October 3, 2012 1:10 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:44 PM
Points: 3,907, Visits: 5,083
Easy one for middle of the week, thanks Ron

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1367423
Posted Wednesday, October 3, 2012 1:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
thanks Kingston..


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1367429
Posted Wednesday, October 3, 2012 2:12 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 3,033, Visits: 3,233
Thanks Ron - easy one for me today

-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Post #1367456
Posted Wednesday, October 3, 2012 2:52 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 1,737, Visits: 6,338
As usual the lesson is not to rely on the defaults.

I'd have expected a near 100% success rate given the number of similar questions we've had in recent months, but obviously it still needs repeating!
Post #1367471
Posted Wednesday, October 3, 2012 2:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:10 AM
Points: 55, Visits: 87
Got to know that by default it takes the length as 1 if the size is not declared. Thank you....

However, I still did not get the difference between length of normal declaration and Cast/Convert... Please find below the queries....


1) SELECT
LEN('xyz') AS 'Declared'
, LEN(CONVERT(VARCHAR,'xyz')) AS 'Converted'
, LEN(CAST('xyz' AS VARCHAR)) AS 'Cast'

Ans : 3,3,3


2)
DECLARE @a VARCHAR ='xyz'

SELECT
LEN(@a) AS 'Declared'
, LEN(CONVERT(VARCHAR,@a)) AS 'Converted'
, LEN(CAST(@a AS VARCHAR)) AS 'Cast'

Ans : 1,1,1

I did not get default of 30 (which you mentioned in the answer in case of Cast/Convert) in what ever way I try... Can you please explain more on this?
Post #1367472
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse