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 «««1234»»

VARCHAR datatype Expand / Collapse
Author
Message
Posted Monday, July 19, 2010 8:52 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:05 AM
Points: 1,733, Visits: 6,320
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
Post #954864
Posted Monday, July 19, 2010 9:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 7:45 AM
Points: 86, Visits: 84
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.
Post #954873
Posted Monday, July 19, 2010 9:22 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: 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.


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
Posted Monday, July 19, 2010 9:56 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 12:49 PM
Points: 1,676, Visits: 1,754
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
Posted Monday, July 19, 2010 11:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 1, 2014 9:46 PM
Points: 1,380, Visits: 2,684
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
Posted Monday, July 19, 2010 11:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
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
Posted Monday, July 19, 2010 12:19 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:53 PM
Points: 21,617, Visits: 15,271
Excellent question. Thanks for posting it.



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


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
Posted Monday, July 19, 2010 12:24 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 15, 2014 2:58 PM
Points: 2,345, Visits: 2,690
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


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"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
Posted Monday, July 19, 2010 1:10 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, August 16, 2014 9:15 AM
Points: 2,818, Visits: 2,557
Good question. Although the first thing I wanted to know was the collation.
Post #955050
Posted Monday, July 19, 2010 4:49 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 5:03 PM
Points: 265, Visits: 122
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 »

Add to briefcase «««1234»»

Permissions Expand / Collapse