SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by SveG / Data Type and Length / Latest Posts

RE: Data Type and Length

Jeff Moden — Thu, 30 Sep 2010 22:05:18 GMT

[quote][b]David Data (9/27/2010)[/b][hr]But I am curious as to why a VARCHAR(MAX) string that happens to contain say 100 chars is so much slower that a VARCHAR(100) that does? Its index and length values will have to be 32 bit numbers, but with 32/64 bit CPUs anything shorter tends to be less rather than more efficient anyway. As even a VARCHAR(100) is stored as a variable length string, I would not imagine the memory management issues are much different either. Or are they?[/quote]Im not sure. Some folks insist (and maybe rightfully so... I seem to remember such a thing in BOL but don't remember for sure) the VARCHAR(MAX) stays "inrow" under such conditions. To me, it seems like it may not but I've not taken the time to research that to any depth. It would be nice to know for sure but I just don't have the floor space on my dance card right now.

RE: Data Type and Length

Hugo Kornelis — Tue, 28 Sep 2010 03:02:01 GMT

[quote][b]David Data (9/28/2010)[/b][hr]OTOH I designed our ETL system with [Postcode] [nvarchar](10) and then had to change it when I found records with postcodes like 'If out please leave by back door or with neighbour at No. 127' :([/quote]I really hope your design change was to add a column for "delivery comments", not to increase the maximum length for the postcode. (Unless your business operates in a country where 'If out please leave by back door or with neighbour at No. 127' is a valid postcode).But I do hope you changed the postcode to varchar(10). As far as I know, all countries in the world that use postal codes limit them to numbers and the letters A-Z.

RE: Data Type and Length

David Data — Tue, 28 Sep 2010 02:04:56 GMT

OTOH I designed our ETL system with [Postcode] [nvarchar](10) and then had to change it when I found records with postcodes like 'If out please leave by back door or with neighbour at No. 127' :([quote][b]magasvs (9/27/2010)[/b][hr]The problem with wide columns is that even though SQL Server supports now larger row size the maximum capacity specification is still 8,060 bytes per row. If the row becomes wider - then row overflow occurs and data is saved differently.[/quote]I understand the need to keep the actual data short, (especially for fields in the primary key, where I prefer to use INT or CHAR if possible), but does a record containing a couple of VARCHAR(8000)'s or even a VARCHAR(MAX) which happen to contain 50-char strings need row overflow, or does it only need 100 bytes (+overhead)? My tests suggest the latter.

RE: Data Type and Length

paul.knibbs — Tue, 28 Sep 2010 01:29:11 GMT

[quote][b]magasvs (9/27/2010)[/b][hr]There were columns like Year nvarchar (255), PostalCode nvarchar (255), Phone nvarchar (8000)...[/quote]One of our developers stored a TCP port number (which is, by definition, an unsigned 16-bit integer) in a VARCHAR(50) field...sometimes I despair. ;-)

RE: Data Type and Length

magasvs — Mon, 27 Sep 2010 19:14:17 GMT

The problem with wide columns is that even though SQL Server supports now larger row size the maximum capacity specification is still 8,060 bytes per row. If the row becomes wider - then row overflow occurs and data saved differently. This affects query performance (I/O performance degradation). More details are here http://msdn.microsoft.com/en-us/library/ms186981.aspx. This works differently with varchar(max), nvarchar(max), varbinary(max), text, image, or xml data types, but I just wanted to make point that columns width should be reasonable. Another example of incorrect data type usage is using NVARCHAR or FLOAT data types when it's not required. Just a couple of days ago I have been testing large database migration and by changing NVARCHAR, DATETIME to VARCHAR and DATE/SMALLDATETIME (together with column width reduction) one of the tables reduced in size by 11 GB! There were columns like Year nvarchar (255), PostalCode nvarchar (255), Phone nvarchar (8000)...

RE: Data Type and Length

David Data — Mon, 27 Sep 2010 16:55:09 GMT

(As someone has re-awakened this subject...)[quote][b]Jeff Moden (8/23/2010)[/b][hr]There is sometimes a HUGE performance penalty (2:1) to be paid for using VARCHAR(MAX) depending, of course, on what you're doing. Correct sizing is always important. I'd dare say the same would be true for most any language... even .Net.[/quote]I was talking about local scalar variables, not arrays or database fields where extra microseconds can get multiplied by millions of records.But I am curious as to why a VARCHAR(MAX) string that happens to contain say 100 chars is so much slower that a VARCHAR(100) that does? Its index and length values will have to be 32 bit numbers, but with 32/64 bit CPUs anything shorter tends to be less rather than more efficient anyway. As even a VARCHAR(100) is stored as a variable length string, I would not imagine the memory management issues are much different either. Or are they?That said, I do always use the smallest VARCHAR I can - though I'm not sure whether it really makes much difference as SQL Server seems to allocate space for the actual content not the potential maximum content, as I tested by creating a large table of VARCHAR(4096) containing just 'x' (1 character) per record.

RE: Data Type and Length

Dhruvesh Shah — Sun, 26 Sep 2010 23:30:53 GMT

Great Question it chew up my hour worth ups time. :-D

RE: Data Type and Length

Jeff Moden — Mon, 23 Aug 2010 21:09:28 GMT

[quote][b]paul.knibbs (8/23/2010)[/b][hr][quote]There is sometimes a HUGE performance penalty (2:1) to be paid for using VARCHAR(MAX) depending, of course, on what you're doing. Correct sizing is always important. I'd dare say the same would be true for most any language... even .Net.[/quote]Well, the person I was responding to never mentioned anything about performance, he just wanted a text field that can be any length in T-SQL, and I was pointing out that already exists. :-)[/quote]Heh... Well, everyone should always be concerned about performance so I was pointing out how bad it can actually be when you don't size stuff properly. :-)

RE: Data Type and Length

paul.knibbs — Mon, 23 Aug 2010 05:16:39 GMT

[quote]There is sometimes a HUGE performance penalty (2:1) to be paid for using VARCHAR(MAX) depending, of course, on what you're doing. Correct sizing is always important. I'd dare say the same would be true for most any language... even .Net.[/quote]Well, the person I was responding to never mentioned anything about performance, he just wanted a text field that can be any length in T-SQL, and I was pointing out that already exists. :-)

RE: Data Type and Length

Jeff Moden — Mon, 23 Aug 2010 05:14:13 GMT

[quote][b]paul.knibbs (8/11/2010)[/b][hr][quote][b]David Data (8/10/2010)[/b]It would be nice to have a datatype like .NET's [b]string[/b] which can carry strings of (almost) any length without worrying about what its contents' maximum length might happen to be. Fields in database tables may need to be specified precisely; local variables should not.[/quote]Isn't that pretty much what VARCHAR(MAX) is?[/quote]There is sometimes a HUGE performance penalty (2:1) to be paid for using VARCHAR(MAX) depending, of course, on what you're doing. Correct sizing is always important. I'd dare say the same would be true for most any language... even .Net.

RE: Data Type and Length

Jeff Moden — Mon, 23 Aug 2010 05:09:41 GMT

[quote][b]Hugo Kornelis (8/9/2010)[/b][hr]Good question! When I first saw it, I thought there would be a very high percentage of correct answers since the confusing default lengths for character strings have already been covered in a few recent QotD's. But the results say that at this time, only 53% of the respondents have given the right answer, so there is obviously still a lot of need to keep driving this point home. Good job, magasvs![/quote]Nah... it just shows that 47% of the people are too lazy to run the code which is also why these are only worth 1 point. :-P

RE: Data Type and Length

VM-723206 — Wed, 11 Aug 2010 06:47:29 GMT

Good question! Thanks.

RE: Data Type and Length

paul.knibbs — Wed, 11 Aug 2010 01:01:28 GMT

[quote][b]David Data (8/10/2010)[/b]It would be nice to have a datatype like .NET's [b]string[/b] which can carry strings of (almost) any length without worrying about what its contents' maximum length might happen to be. Fields in database tables may need to be specified precisely; local variables should not.[/quote]Isn't that pretty much what VARCHAR(MAX) is?

RE: Data Type and Length

David Data — Tue, 10 Aug 2010 13:41:53 GMT

I got it wrong too. But like many questions here, the answer depends on knowing the many inconsistencies and strange rules in SQL Server - most of which you never need to know.I had no idea how long char and varchar default sizes are, for the simple reason that I've never declared any without specifying the length. IMHO the correct answer [i]should [/i]have been 'Syntax Error'!It would be nice to have a datatype like .NET's [b]string[/b] which can carry strings of (almost) any length without worrying about what its contents' maximum length might happen to be. Fields in database tables may need to be specified precisely; local variables should not.

RE: Data Type and Length

Kangana Beri — Mon, 09 Aug 2010 17:56:19 GMT

Good question. Thank you!

RE: Data Type and Length

Enigma475 — Mon, 09 Aug 2010 15:01:24 GMT

This is a very good question. My initial choice was 3/38. It's a good thing I decided to run the code before answering. I learned something new :-) ...

RE: Data Type and Length

pjdiller — Mon, 09 Aug 2010 09:04:37 GMT

Thanks very much! This is a question that applies to the category of "things I need to know and will probably run across" and the answer is straight-forward/simple! Ok, I got it wrong, but that's because I have plenty to learn.(The last couple times I've commented, it was due to being frustrated with the question, thought I might add some positive feedback as well :cool:)

RE: Data Type and Length

Sean Lange — Mon, 09 Aug 2010 08:02:08 GMT

Excellent question! I noticed the lack of defining the size and my initial response was 1;1 but since that was not a choice I figured there must be something in convert for default sizes. Had there been an answer of 1;1 I would have assumed that was the correct answer. ;-)

RE: Data Type and Length

Daniel Bowlin — Mon, 09 Aug 2010 07:43:01 GMT

Curious, the difference in default behaviour between a DECLARE and a CONVERT.I suppose this just puts another check mark in the Always Declare Everything column.

RE: Data Type and Length

Kari Suresh — Mon, 09 Aug 2010 07:22:34 GMT

[p]Good question. Thank you.[/p]

RE: Data Type and Length

Dennissinned — Mon, 09 Aug 2010 06:45:04 GMT

Great Question. Thank you.

RE: Data Type and Length

Ameya- Ameyask — Mon, 09 Aug 2010 04:25:38 GMT

Very good question indeed. Takes back to basics :-)

RE: Data Type and Length

paul.knibbs — Mon, 09 Aug 2010 03:54:38 GMT

Completely forgot about the CONVERT when answering this, but probably would have got it wrong even if I'd taken it into account...didn't know about this default length behaviour!

RE: Data Type and Length

hrvoje.piasevoli — Mon, 09 Aug 2010 02:24:31 GMT

Good question![quote][b]Hugo Kornelis (8/9/2010)[/b][hr]...I thought there would be a very high percentage of correct answers since the confusing default lengths for character strings have already been covered in a few recent QotD's. [/quote]I guess people don't go through discussions. (July, 19th QotD only asked for the implicit length of variable declaration while the CONVERT behavior was mentioned and explained in the discussion that followed.)Regards,Hrvoje

RE: Data Type and Length

Hugo Kornelis — Mon, 09 Aug 2010 00:59:05 GMT

Good question! When I first saw it, I thought there would be a very high percentage of correct answers since the confusing default lengths for character strings have already been covered in a few recent QotD's. But the results say that at this time, only 53% of the respondents have given the right answer, so there is obviously still a lot of need to keep driving this point home. Good job, magasvs![quote][b]Bhuvnesh (8/9/2010)[/b][hr]30 is for char or varchar ?[/quote]Both, when used without length in a CONVERT() function call.In a DECLARE, both char and varchar default to a length of 1.

RE: Data Type and Length

Bhuvnesh — Mon, 09 Aug 2010 00:17:14 GMT

30 is for char or varchar ?

RE: Data Type and Length

SQLRNNR — Sun, 08 Aug 2010 22:51:43 GMT

Great question. As the others have said, this underlines the necessity of specifying the size of the datatype.

RE: Data Type and Length

codebyo — Sat, 07 Aug 2010 23:08:35 GMT

[quote][b]Oleg Netchaev (8/7/2010)[/b][hr]I think that this data type size behaviour is great. Since there is no way in SQL Server to punish the disobedient by, say, 12 lashes they deserve, this difference is actually a good way to teach them to never omit the size when declaring / converting variables. On the top of the 1 / 30 issue, there is another interesting twist to it: both ADODB and ADO.NET default the size of the varchar type procedure parameter to 50 if the size is not specified. The bottom line is that forgetting to specify the size is evil and should be avoided.As far as a technical explanation is concerned, I believe that because the size is required, default values were provided by the parser team, and it probably just so happened that the declaration and conversion were written by different developers, that is all.Oleg[/quote]You're right. Trusting default behaviours should be avoided at all costs.But 12 lashes is too soft. :-D

RE: Data Type and Length

UMG Developer — Sat, 07 Aug 2010 22:59:33 GMT

Great question thanks!This is a very good topic to cover, as I know a lot of developers don't understand the importance of specifying the size. (Good old VB "String" type.)

RE: Data Type and Length

Oleg Netchaev — Sat, 07 Aug 2010 20:11:31 GMT

[quote][b]codebyo (8/7/2010)[/b][hr]Now why in Heavens would there be two different behaviours for the same datatype?Just to confuse people? Or is there a technical explanation for that?[/quote]This is a very good question, I really like it.I think that this data type size behaviour is great. Since there is no way in SQL Server to punish the disobedient by, say, 12 lashes they deserve, this difference is actually a good way to teach them to never omit the size when declaring / converting variables. On the top of the 1 / 30 issue, there is another interesting twist to it: both ADODB and ADO.NET default the size of the varchar type procedure parameter to 50 if the size is not specified. The bottom line is that forgetting to specify the size is evil and should be avoided.As far as a technical explanation is concerned, I believe that because the size is required, default values were provided by the parser team, and it probably just so happened that the declaration and conversion were written by different developers, that is all.Oleg

RE: Data Type and Length

codebyo — Sat, 07 Aug 2010 13:04:00 GMT

Thanks for question. I just lost one point. :-DNow why in Heavens would there be two different behaviours for the same datatype?Just to confuse people? Or is there a technical explanation for that?

Data Type and Length

magasvs — Sat, 07 Aug 2010 13:00:57 GMT

Comments posted to this topic are about the item [B]Data Type and Length[/B]