VARCHAR datatype

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Greg Goodge » VARCHAR datatype

38 posts, Page 3 of 4

«««1 2 3 4 »»

VARCHAR datatype

Rate Topic

Display Mode

Topic Options

Author

Message

Toreador

Posted Monday, July 19, 2010 8:52 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:52 AM
Points: 1,356, Visits: 4,761

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 Wink

Post #954864

Kanaka

Posted Monday, July 19, 2010 9:04 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 10, 2013 11:56 AM
Points: 86, Visits: 71

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

Post #954873

JestersGrind

Posted Monday, July 19, 2010 9:22 AM

Hall of Fame

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:42 PM
Points: 3,487, Visits: 1,581

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

Post #954884

Oleg Netchaev

Posted Monday, July 19, 2010 9:56 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 10:53 AM
Points: 1,662, Visits: 1,709

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

Post #954914

Trey Staker

Posted Monday, July 19, 2010 11:14 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:52 PM
Points: 1,379, Visits: 2,626

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

Post #954980

UMG Developer

Posted Monday, July 19, 2010 11:17 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163, Visits: 2,148

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.

Post #954982

SQLRNNR

Posted Monday, July 19, 2010 12:19 PM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 10:25 AM
Points: 18,754, Visits: 12,337

Excellent question. Thanks for posting it.

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 #955012

webrunner

Posted Monday, July 19, 2010 12:24 PM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:47 PM
Points: 2,118, Visits: 2,213

Trey Staker (7/19/2010)

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

-------------------
"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 #955016

Daniel Bowlin

Posted Monday, July 19, 2010 1:10 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 1:10 PM
Points: 2,673, Visits: 2,418

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

Post #955050

Jan Sorenson

Posted Monday, July 19, 2010 4:49 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 11:38 AM
Points: 265, Visits: 116

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.

Post #955196

« Prev Topic | Next Topic »

38 posts, Page 3 of 4

«««1 2 3 4 »»

Permissions