Lengths of varchar -why?

[sunaj]

Why specify the length of a varchar?

I know it defines the max length of the field, but why not allways set it to 8000 (which I guess is the max length for a varchar field)?


Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[tlbroadbent]

1) If you define all columns as varchar(8000), how would anyone looking at the schema know what the true size of the column should be? Columns sizes should be appropriste for the data.

2) By defining all varchar columns as 8000 characters, you open the door for failure because 8060 bytes is the maximum row width in SQl server. A column could unintentionally be updatd with too many characters. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[sunaj]

Thx for your answer tlbroadbent. I realize that you are the top guru of the forum, but I'm still not 100% clear on the issue...

Your answer does not give any technical reasons. If I knew the approximate size of the field I would use a char instead of a var char. I guess what you are saying it that there is no extra overhead of setting the length of the varchar field to 8000 compared to 50 (or whatever).

Maybe I don't understand your 2), but that would be true no matter what the length of tha varchar field is (e.i. I can't insert more than 8060 characters into a row).

X-) Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[tlbroadbent]

Consider that when you use CHAR data type, the column always occupies the number of characters defined. VARCHAR will be trimmed of trailing spaces and only occupy the amount of space required for the data.

Recently, I changed a CHAR(10) column to VARCHAR(10) on one table. The table contained over 12 million rows. 90% of the rows had no data in the column. However, due to the CHAR data type, this column was always 10 characters long. If you do the math, you find that that column alone occupied 120MB. The column was used in two indexes. Each of the indexes occupied 120MB. Total space occupied 360MB. Total space actually needed 36MB.

There is overhead involved with using VARCHAR instead of CHAR data type. I frankly don't know if VARCHAR(8000) involves more overhead than VARCHAR(10).

Oops! There goes my guru title! :-(
​

From a database design point-of-view, the reasons listed in my first post are sufficiently "technical" for me to avoid using an arbitrary number of characters, unrelated to the database design, when creating tables. I want the design to reflect reality. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[nimapoggy]

the real question is "why don't you know the size of your text ?" I'dont really have any tips for that but it is possible that you needed to organize your datas in another way or try to split datas into two sub datas types...

But that's not the point, and I also have a question.

I have to migrate an ACCESS database to SQL Server 2000 and I noticed that the ACCESS database is full of "memo" type columns. It's said that I have to convert them into "ntext" under SQL server and that I have to put this columns at the end of the table. Ok, why not.
But the texts stored in this columns are articles, html pages and other very long texts. Isn't it better to store this texts as files and only refer to the path in the database ?
What is the influence of a "ntext" column on the database size ? Is this type of column working as "varchar" as seen above in your answer ?

 

[tlbroadbent]

"In SQL server Individual text, ntext, and image values can be a maximum of 2-GB, which is too long to store in a single data row." (SQL BOL)

I prefer to store the path in SQL Server and store large files on a server. See thread183-247490 for a recent discussion about this topic.

Storing large documents in text or ntext will certainly increase the size of a database. In SQL Server 2000, the size impact isn't as great as in earlier versions because multiple text, ntext or image columns can be stored on a page if they fit. SQL 200 also allows storage of small text, ntext and image data in a data row if they fit.

Note: ntext is a Unicode data type. Unicode is a double byte character set (DBCS). I recommend using ntext only if you store data that can't be represented in a single byte character set (SBCS). A DBCS uses two bytes to store each character in the string. A SBCS data type uses one byte to store each character. Using any DBCS character data type (ntext, nchar, nvarchar) when a SBCS is sufficient doubles the storage space required and can be very wasteful. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.