VARCHAR datatype

Question

Post reply

VARCHAR datatype

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

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

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 1

Nice question.

I had to browse my memory from long ago after I saw that VARCHAR declaration. I had forgotten that.

Thanks a lot. 😎

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 2

Nice question - I guessed that the collation was case insensitive (the default on any installation I've done), but got it wrong anyway, because I didn't spot that "Green" had an "r" in it.

I guess that's Mondays for you.

Duncan

JestersGrind SSCertifiable Points: 6222 More actions · Answer 3

Thanks for all of the feedback. I definitely should have considered case-sensitivity, especially since we have several case-sensitive database here. In hind sight, I should have made everything lower case to avoid confusion.

A little back story... I ran into this situation while reviewing a developer's stored procedure that was returning more results than it should have. Up until that point, I had never needed to know the default length of a varchar variable, because I always followed the best practice of explicitly stating the length of a varchar variable. I guess this is why it's a best practice.

Thanks,

Greg

Tom Garth SSCertifiable Points: 6193 More actions · Answer 4

I too thought the default length was 30.

Not stating the collation setting makes it obvious no matter what actual db settings you work with regularly.

Thanks for the question and education.

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

webrunner SSC-Dedicated Points: 31882 More actions · Answer 5

Good question. In this case I don't consider it a trick question because the lack of a length specification in the statement "DECLARE @TestVar VARCHAR" pointed to behavior based on omitting the length. At that point there is not so much trickery as a real test of knowledge of how SQL behaves in the absence of a length specification. That's a valid test of knowledge, not a trick.

I got the question wrong, but that is as it should be because I didn't know the information the question was testing for. I learned something from the question and the documentation, namely:

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

http://msdn.microsoft.com/en-us/library/ms176089.aspx

I assumed the length is always 30 when a length is not specified, but as the documentation states, it is sometimes 1. That's enough to make the question a valuable one, as far as I am concerned.

Thanks,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Toreador SSChampion Points: 11368 More actions · Answer 6

A lesson that's common to many QotDs is that you should never rely on default values - always be specific, so you know exactly what you're getting, and so does naybody else who reads the code later!

A lesson which I break whenever I create a table without specifying 'primary' all over the place 😉

Kanaka SSC Enthusiast Points: 103 More actions · Answer 7

I am not sure why sql server 2008 returned 1, inspite of not giving the variable length.

I knew this would give a syntax error, however, I tried this and it returned 1 row.:w00t:

JestersGrind SSCertifiable Points: 6222 More actions · Answer 8

Kanaka (7/19/2010)
I am not sure why sql server 2008 returned 1, inspite of not giving the variable length.
I knew this would give a syntax error, however, I tried this and it returned 1 row.:w00t:

The version of SQL Server doesn't matter. The only way that you will get one record is if you are using a case-sensitive collation.

Thanks,

Greg

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 9

This is an excellent question, thank you Greg. It serves as a reminder that "thou shalt not omit the size when declaring a string based variable" as it can serve as a ground of subtle bugs. For example, int and varchar are implicitly convertible and funny things can happen because of it:

create proc dbo.dump_me

(

@input varchar

)

as

begin

select @input result;

end;

go

The proc above has a nasty side effect:

exec dbo.dump_me '123';

produces

result

------

1

However, removing the single quotes:

exec dbo.dump_me 123;

produces

result

------

*

While this behaviour is by design, it might catch someone by surprise. The size is omitted, so '123' becomes 1 when cast to varchar(1), but implementation of int data type allows returning * when the number of characters in the int does not fit.

Oleg

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 10

Good Question. I've been stung by this in production early on in my career. Since then I always have specified the length of my varchar's.

I've read the complaints about coallation. I always assume default out of the box coallation for QOD but you all have a point since many of you are outside the US and have different coallations. I think if we took in consideration every tiny thing that we normally assume the question may take pages and pages to set up.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

UMG Developer SSChampion Points: 13482 More actions · Answer 11

Trey Staker (7/19/2010)
I've read the complaints about coallation. I always assume default out of the box coallation for QOD but you all have a point since many of you are outside the US and have different coallations. I think if we took in consideration every tiny thing that we normally assume the question may take pages and pages to set up.

Or like Greg stated, he could have put it all in lower, or upper, case and then the collation wouldn't have mattered. But yes, there are lots of things to think about when writing a QotD to make it successful.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Excellent question. Thanks for posting it.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

webrunner SSC-Dedicated Points: 31882 More actions · Answer 13

Trey Staker (7/19/2010)
Good Question. I've been stung by this in production early on in my career. Since then I always have specified the length of my varchar's.
I've read the complaints about coallation. I always assume default out of the box coallation for QOD but you all have a point since many of you are outside the US and have different coallations. I think if we took in consideration every tiny thing that we normally assume the question may take pages and pages to set up.

Thanks - your comment made me wonder why SQL doesn't just throw an error when the varchar definition doesn't include a size. What is the purpose of having two default values based on the kind of declaration vs. CAST and CONVERT, given the possible bad side effects?

Just wondering.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 14

Good question. Although the first thing I wanted to know was the collation.

Jan Sorenson Mr or Mrs. 500 Points: 579 More actions · Answer 15

I liked this question as well, it demonstrated what many assumed to be one thing but was another, in this case the default length of a varchar when none is specified.