DECLARE - 1

Question

DECLARE - 1

Viewing 15 posts - 16 through 30 (of 48 total)

You must be logged in to reply to this topic. Login to reply

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

kapil190588 (10/3/2012)
How in case of
LEN(CONVERT(VARCHAR,@a)) AS 'Converted'
, LEN(CAST(@a AS VARCHAR)) AS 'Cast'
It is returning 30?
Can you plz exlpain.

Please check my previous post, Praveena has the same question..

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Kevin Gill SSCrazy Points: 2348 More actions · Answer 2

Praveena-487125 (10/3/2012)
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?

You're changing the question by your examples here - it's about what the resultant variable type ends up as in each case - putting @a into each one fundamentally changes what's being asked, as does not using @a at all. It's only when you mix them as in the original question that you will get the specified behaviour.

If you explicitly declare a variable as just 'VARCHAR' it has a default length of 1 character. As such:

DECLARE @a VARCHAR = 'xyz'

actually creates @a as a varchar(1) which contains only the character 'x'.

Casting or converting a string literal behaves differently and defaults to 30 characters so when you CAST('xyz' as VARCHAR) or CONVERT(VARCHAR,'xyz') you get a 30 character varchar with only three characters in it, so the 'LEN' call returns 3.

In your first version you call LEN('xyz') which is taking the length of a string literal, which is implicitly cast to a VARCHAR type so internally will be represented (someone correct me if I'm wrong here) as a VARCHAR(30) and hence the length shows as 3. It's only when you declare it as a VARCHAR explicitly that you get length of 1. That's what happens with your second version where you use the explicitly declare length 1 varchar for all your length checks.

Hope that's clearer than it feels...

Kev

-------------------------------Oh no!

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

how Casting or converting a string literal behaves returning 30 characters.

this is the only thing on which am stuck..

plz explain

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 4

One comment -- the syntax of the question only works on SQL 2008 and up. This line:

DECLARE @a VARCHAR ='xyz'

is not supported on earlier versions; you have to declare and select as two steps.

Dave62 SSCertifiable Points: 7459 More actions · Answer 5

kapil190588 (10/3/2012)
how Casting or converting a string literal behaves returning 30 characters.
this is the only thing on which am stuck..
plz explain

I think it has to do with "SET ANSI_PADDING ON" and the default length of varchar being 30.

Just answer off the top of my head from memory. If anyone knows better feel free to correct.

Rich Mechaber SSChampion Points: 11130 More actions · Answer 6

Dave62 (10/3/2012)
kapil190588 (10/3/2012)
how Casting or converting a string literal behaves returning 30 characters.
this is the only thing on which am stuck..
plz explain
I think it has to do with "SET ANSI_PADDING ON" and the default length of varchar being 30.
Just answer off the top of my head from memory. If anyone knows better feel free to correct.

Nope, ANSI_PADDING is a red herring here: you get the same results with it set to OFF.

Per BOL:

length
Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types. For CONVERT, if length is not specified, the default to 30 characters. sic

The CAST('xyz' AS VARCHAR) works the same as the initial DECLARE: if no length is specified for a VARCHAR, it defaults to 1.

Rich

Ken SSCoach Points: 17191 More actions · Answer 7

Ken

SSCoach

Points: 17191

October 3, 2012 at 7:35 am

#1544911

Nice question, thanks!

Kevin Gill SSCrazy Points: 2348 More actions · Answer 8

rmechaber (10/3/2012)
The CAST('xyz' AS VARCHAR) works the same as the initial DECLARE: if no length is specified for a VARCHAR, it defaults to 1.
Rich

But that suggests that running

SELECT LEN(CAST('xyz' as VARCHAR))

should return 1, not 3 as it does.

Also SELECT LEN(CAST('abcdefghijklmnopqrttuvwxyz1234567890' as VARCHAR)) returns 30, suggesting that CAST does behave as per CONVERT.

-------------------------------Oh no!

Mike Dougherty-384281 SSCrazy Points: 2794 More actions · Answer 9

Toreador (10/3/2012)
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!

I think in this case the number wrong indicates that few people use varchar without specifying a length so this default stuff is a weird special case. I knew the declaration was length 1. I think it serve one right to get a nearly useless default. I don't understand the inconsistency with the convert/cast behavior being so "forgiving."

Rich Mechaber SSChampion Points: 11130 More actions · Answer 10

Kevin Gill (10/3/2012)
rmechaber (10/3/2012)
The CAST('xyz' AS VARCHAR) works the same as the initial DECLARE: if no length is specified for a VARCHAR, it defaults to 1.
Rich
But that suggests that running
SELECT LEN(CAST('xyz' as VARCHAR))
should return 1, not 3 as it does.
Also SELECT LEN(CAST('abcdefghijklmnopqrttuvwxyz1234567890' as VARCHAR)) returns 30, suggesting that CAST does behave as per CONVERT.

Interesting. I confess I didn't look into this much more than to check BOL for CONVERT.

I don't really find much useful about questions like this: follow recommended practice and always specify a data length, and this issue disappears. But maybe that's the lesson of the day.

Rich

Kevin Gill SSCrazy Points: 2348 More actions · Answer 11

rmechaber (10/3/2012)
follow recommended practice and always specify a data length, and this issue disappears. But maybe that's the lesson of the day.

I completely agree. Code that assumes default behaviour is always subject to break things later in interesting ways when the defaults change...

-------------------------------Oh no!

Ken SSCoach Points: 17191 More actions · Answer 12

This is a good example of why you should always specify length for your datatypes when applicable.

Miles Neale SSChampion Points: 13147 More actions · Answer 13

Shows both a trick and a trap. Might not use either but might be good to remember when trying to determine why something does not work exactly as expected.

Excellent question!

Not all gray hairs are Dinosaurs!

Ron McCullough SSC Guru Points: 63877 More actions · Answer 14

rmechaber (10/3/2012)
Kevin Gill (10/3/2012)
rmechaber (10/3/2012)
The CAST('xyz' AS VARCHAR) works the same as the initial DECLARE: if no length is specified for a VARCHAR, it defaults to 1.
Rich
But that suggests that running
SELECT LEN(CAST('xyz' as VARCHAR))
should return 1, not 3 as it does.
Also SELECT LEN(CAST('abcdefghijklmnopqrttuvwxyz1234567890' as VARCHAR)) returns 30, suggesting that CAST does behave as per CONVERT.
Interesting. I confess I didn't look into this much more than to check BOL for CONVERT.
I don't really find much useful about questions like this: follow recommended practice and always specify a data length, and this issue disappears. But maybe that's the lesson of the day.
Rich

Yes it is the lesson of the day, and from the results so far, it appears that many might/hopefully have learned something

Correct answers: 68% (456)

Incorrect answers: 32% (219)

Total attempts: 675

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

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

TomThomson SSC Guru Points: 104773 More actions · Answer 15

rmechaber (10/3/2012)

Per BOL:

length
Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types. For CONVERT, if length is not specified, the default to 30 characters. sic

Quoting a version of BoL that old is careless - tht is the wording from the BoL 2000 text; BoL versions for currently supported products SQL 2008, SQL 2008 R2, and SQL 2011 (and for SQL 2005, which I think has ended mainline support) all specify that the default length for when using CAST is 30. Remember that is a question doesn't specify teh SQL version it is supposed to work for all SQL versions still in full support, so you should not be looking at BoL for SQL 2000 to get the answer to this question.

The CAST('xyz' AS VARCHAR) works the same as the initial DECLARE: if no length is specified for a VARCHAR, it defaults to 1.

The text doesn't state that - it just fails to state what the default is when CAST is used. I no longer have an SQL 2000 system so I can't test and see what it actually is, and I can't remember what it was either.

edit: a couple of days on I think my first paragraph above was unfair - I failed to spot the link Rich provided, which is to the 2005 BoL page for cast and convert (which has the same omission as the 2000 BoL page for chard an varchar that I mistakenly though he was quoting) so he wasn't quoting quite as old a page as I thought (it is one for a release which is out of mainstream support, but nowhere near as old as an SQL 2000 one). My assumption that it was the 2000 page (just because it had that omission, when looking more carefully at the quoted text would have shown it wasn't the page I thought it was because that page doesn't refer to binary and varbinary) was careless, so I was committing the fault I accused Rich of.

I'll stick by the second paragraph though: nothing in any of the pages concerned says that the default length for CAST is 1, and every release (from 2000 to 2011 inclusive) has at least one page that says that that default is 30.

Tom