SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


VARCHAR datatype


VARCHAR datatype

Author
Message
Toreador
Toreador
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4217 Visits: 8142
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
Kanaka
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 88
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
JestersGrind
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4057 Visits: 1585
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
Oleg Netchaev
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 1828
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
Trey Staker
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1756 Visits: 2788
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
UMG Developer
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4754 Visits: 2204
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
SQLRNNR
SSC Guru
SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)

Group: General Forum Members
Points: 82952 Visits: 18577
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

webrunner
webrunner
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9796 Visits: 4035
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

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"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
Daniel Bowlin
Daniel Bowlin
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10188 Visits: 2629
Good question. Although the first thing I wanted to know was the collation.
Jan Sorenson
Jan Sorenson
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 125
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search