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


Strings and Defaults


Strings and Defaults

Author
Message
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25825 Visits: 12494
nice question.

Evey time I recall that default varchar length is 1 for declare but 30 for cast I feel sick and resolve never to use varchar without specifying the length.

Tom

jlennartz
jlennartz
SSC Eights!
SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)

Group: General Forum Members
Points: 936 Visits: 1197
I knew that second variable would hold all 50 characters just didn't realize that case as varchar without a length would default to 30. Makes sense. I should have realized that.
David Conn
David Conn
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3514 Visits: 1183
I want to side with those who find this inconsistency as unacceptable. I can live with defaults but in this case there are 2, 1 and 30. There should be one default or every varchar should have to specify a length. Maybe a Server or Database option.

It was a good question but you shouldn't have to know these arcane default values
Jagadish Kumar Punnapu
Jagadish Kumar Punnapu
SSC Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 117
Good Question, Thanks Gail.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221873 Visits: 46282
David Conn (2/1/2012)
It was a good question but you shouldn't have to know these arcane default values


Personally my opinion is that you shouldn't have to know the defaults because you never ever declare varchar/char/nvarchar/nchar/binary/varbinary/etc without specifying a length.

I would personally like to see declaring one of those data types without specifying a length deprecated. Won't happen though.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25825 Visits: 12494
GilaMonster (2/1/2012)
David Conn (2/1/2012)
It was a good question but you shouldn't have to know these arcane default values


Personally my opinion is that you shouldn't have to know the defaults because you never ever declare varchar/char/nvarchar/nchar/binary/varbinary/etc without specifying a length.

I would personally like to see declaring one of those data types without specifying a length deprecated. Won't happen though.

If you raise it as a suggestion on connect I'll vote for it.

Tom

GilaMonster
GilaMonster
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221873 Visits: 46282
Question is, how far do you go?

Just char, varchar, nchar, nvarchar, binary, varbinary?
Also decimal and numeric (default is numeric(18,0))?
Also time (default is time(7)), datetime2 (default is datetime2(7)) and datetimeoffset (default is datetimeoffset(7))?

The new datetime and time default to their highest precision. Numeric and decimal default to something in the middle. The character and binary types default to their minimum when declared.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Brigadur
Brigadur
Right there with Babe
Right there with Babe (759 reputation)Right there with Babe (759 reputation)Right there with Babe (759 reputation)Right there with Babe (759 reputation)Right there with Babe (759 reputation)Right there with Babe (759 reputation)Right there with Babe (759 reputation)Right there with Babe (759 reputation)

Group: General Forum Members
Points: 759 Visits: 27695
I lay my vote for that declaration of varchar/char/nvarchar/nchar/binary/varbinary/etc data types without specifying length should throw an error (or at least warning) by the parser. Anyone, lead me and I will follow :-). Maybe this issue is already raised at Microsoft ...
Brigadur
Brigadur
Right there with Babe
Right there with Babe (759 reputation)Right there with Babe (759 reputation)Right there with Babe (759 reputation)Right there with Babe (759 reputation)Right there with Babe (759 reputation)Right there with Babe (759 reputation)Right there with Babe (759 reputation)Right there with Babe (759 reputation)

Group: General Forum Members
Points: 759 Visits: 27695
GilaMonster (2/1/2012)
Question is, how far do you go?

Just char, varchar, nchar, nvarchar, binary, varbinary?
Also decimal and numeric (default is numeric(18,0))?
Also time (default is time(7)), datetime2 (default is datetime2(7)) and datetimeoffset (default is datetimeoffset(7))?

The new datetime and time default to their highest precision. Numeric and decimal default to something in the middle. The character and binary types default to their minimum when declared.


I want to go all the way :-). Thanks Gail for adding these. Everywhere were length or precision can be specified. Maybe, a warning message would be the most elegant and a good compromise.

Cheers
Istvan
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25825 Visits: 12494
GilaMonster (2/1/2012)
Question is, how far do you go?

Just char, varchar, nchar, nvarchar, binary, varbinary?
Also decimal and numeric (default is numeric(18,0))?
Also time (default is time(7)), datetime2 (default is datetime2(7)) and datetimeoffset (default is datetimeoffset(7))?

The new datetime and time default to their highest precision. Numeric and decimal default to something in the middle. The character and binary types default to their minimum when declared.

I think the default precisions for time, datetime2 and datetimeoffset are sensible, so although I have a general feeling that precision defaults are not a good thing I guess I can live with the defaults for those three.

Decimal and Numeric have such bizarre rounding and precision/scale adjustment conventions that it seems pure insanity to have defaults for precision and scale, since the effects could be disastrous; so I'd prefer it to be forbidden to omit precision and scale for these types (actually I'd prefer MS to implement the 2008 revision of the floating point standard so that we could have exponents to base 10 and deprecate decimal, numeric, money and smallmoney, but there's no chance of that in the short or medium term, and probably very little chance even in the long term).

Tom

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