March 30, 2016 at 3:42 am
Hugo Kornelis (3/29/2016)
nadersam (3/29/2016)
why don't we just make all varchar fields size to be 8000 the max size available?Because if you do, the question does not become whether someone will end up putting an 8000-character novel in the postal code column, but when (and how often) it will happen.
(Always imagine that your successor will be a maniacal axe killer ... who knows where you live)
Absolutely, 100% and always this. Improperly sized columns are an open invitation for people to cause potentially significant problems by entering complete garbage
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
March 30, 2016 at 4:57 am
nadersam (3/30/2016)
GilaMonster (3/30/2016)
Varchar(max) is a very different data type. It's got additional overheads because it's off-row data, It's definitely not something you'd use on all columns.yes you are right off course but nvarchar is even worse even if you define with small size as in attached results.
Um, but nvarchar will be twice the size of a varchar, since it's unicode and 2 bytes per character. Completely expected.
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
April 13, 2016 at 4:01 pm
On the other hand, I recently read an article about a person having all kinds of problems in life because his last name is longer than the 36 characters a last name column is often limited to. There are always trade-offs.
April 13, 2016 at 4:14 pm
dan-572483 (4/13/2016)
On the other hand, I recently read an article about a person having all kinds of problems in life because his last name is longer than the 36 characters a last name column is often limited to. There are always trade-offs.
Another proof for the golden rule:
"Everything should be made as simple as possible, but not simpler"
It's not a trade-off.
It's an error in design.
_____________
Code for TallyGenerator
April 14, 2016 at 8:31 am
GilaMonster (3/29/2016)
Space used + 2 bytes.... The longest city name in the world is 176 characters (and that city is usually written with a much shorter name - Bangkok)...
Just curious as to how you ever found out about Bangkok.
[font="Comic Sans MS"]"Krung Thep Mahanakhon Amon Rattanakosin Mahinthara Ayuthaya Mahadilok Phop Noppharat Ratchathani Burirom Udomratchaniwet Mahasathan Amon Piman Awatan Sathit Sakkathattiya Witsanukam Prasit".
The city of angels, the great city, the residence of the Emerald Buddha, the impregnable city (of Ayutthaya) of God Indra, the grand capital of the world endowed with nine precious gems, the happy city, abounding in an enormous Royal Palace that resembles the heavenly abode where reigns the reincarnated god, a city given by Indra and built by Vishnukarn.[/font]
April 24, 2016 at 11:43 am
nadersam (3/29/2016)
GilaMonster (3/29/2016)
Space used + 2 bytes.As for make everything varchar(8000), why? Is a person's first name going to be thousands of characters long? A city name? A company name? An address line?
No, none of those are ever going to be that kind of size. The longest city name in the world is 176 characters (and that city is usually written with a much shorter name - Bangkok). Address lines sizes are mandated by the postal service, they won't be thousands of characters long.
As with all design choices, use the appropriate data type (and length) for the data being stored as a form of constraint. Making everything 8000 is lazy design (and makes indexing much harder).
Thank you for your reply.
I am asking to check what's the performance impact of defining sizes much bigger than what's actually needed.
Also you mentioned it's harder for indexing , can you please explain why?, isn't it going to be the same concept.
Thanks again.
Nader
From a performance standpoint this would, in essence, prevent page level locks for rows returned with this data full or I believe at least 4001 bytes full.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply