February 14, 2011 at 1:28 pm
I was wondering whether specifying varchar columns as varchar(255) as a minimum size makes any difference as opposed to varchar(10) for example. varchar(255) is still only one byte in the data dictionary, right?
I have a habit of specifying 255 unless a business rule prescribes a max length of less than 255.
Any critique?
February 14, 2011 at 1:47 pm
From a performance standpoint, if you're moving more data, it's slower by that amount, but we're talking trivia most of the time. I just try to find out what the business expects from all fields. You can seriously mess up your reporting if the data isn't what users expect to see. Other than that... nah. Not worth fighting over if you ask me.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 14, 2011 at 7:03 pm
nico van niekerk (2/14/2011)
I was wondering whether specifying varchar columns as varchar(255) as a minimum size makes any difference as opposed to varchar(10) for example. varchar(255) is still only one byte in the data dictionary, right?I have a habit of specifying 255 unless a business rule prescribes a max length of less than 255.
Any critique?
Another than being annoyed by it because it DOES imply the business rules were insufficient, I agree with Grant. Just don't get into the habit of using VARCHAR(MAX) as the norm. THAT can and will cause a performance impact on your code.
--Jeff Moden
February 20, 2011 at 6:43 pm
I agree, but I don't think that there is more data to move. If a varchar(255) contains only 10 characters, say, it will only be 10+2 that will be moved. If a varchar(10) contains a full complement, it will still be 10+2, right?
February 20, 2011 at 7:05 pm
nico van niekerk (2/20/2011)
I agree, but I don't think that there is more data to move. If a varchar(255) contains only 10 characters, say, it will only be 10+2 that will be moved. If a varchar(10) contains a full complement, it will still be 10+2, right?
Yeah, absolutely, that's how it works. But again, if the business thinks it's only ever going to see 10 characters and it gets 22 or 50 or 243, it can seriously mess up reporting. Clean data is hard enough to get without just tossing the concept of trying to limit the data saved. Again, it's more of a concern for the business and the data for reporting for the business, not so much for performance, although, that can enter into it too, as Jeff pointed out.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 21, 2011 at 3:08 pm
nico van niekerk (2/20/2011)
I agree, but I don't think that there is more data to move. If a varchar(255) contains only 10 characters, say, it will only be 10+2 that will be moved. If a varchar(10) contains a full complement, it will still be 10+2, right?
As I said... it's nothing but an annoyance to me. You'll be safer with the varchar(255). Just don't think for even a minute that the same x+2 rules apply with VARCHAR(MAX) (which was my real point). The reason I bring it up is because I've seen lots'o folks use the same, ummmm.... justification to use VARCHAR(MAX) "just to be safe" and they have no idea of the performance they've lost in the process.
--Jeff Moden
February 21, 2011 at 9:02 pm
Oh, I agree, varchar(max) is totally another beast. My point was that an empty 255 wouldn't cause any increase in disc usage compared to a varchar(10), for example.
Some folks think using varchar(max) as a declared variable in a SP would give them more than 8K. It doesn't because there is no underlying object in which to spill over when 8K is exceeded. At 8K a varchar(max) variable truncates.
February 25, 2011 at 7:54 pm
nico van niekerk (2/21/2011)
Some folks think using varchar(max) as a declared variable in a SP would give them more than 8K. It doesn't because there is no underlying object in which to spill over when 8K is exceeded. At 8K a varchar(max) variable truncates.
I'm really sure that's not true.
--Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply