what's best to use for text ranging from 15 to 500

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

what's best to use for text ranging from 15 to 500

Author

Topic

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-03-01 : 14:39:22

Hello !

I have to store text. The length ranges from 15 characters to 800 ! What's best to use ? varchar(500) ?

Thanks a lot for any feedback !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-01 : 14:44:03

yes.

Poor planning on your part does not constitute an emergency on my part.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-03-01 : 15:52:11

If your text is going to be 500charaters and more, your field with varchar(500) will neglect all cases where the field will exceed the length.

anyways, run simple test if you're not sure of the length of your table field.

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-03-02 : 06:50:24

Hello guys,

just an additional question: if I use varchar(500), does this mean that each row in a table will take the space of 500 characters in the sql-server data file, even if it only uses 1 Character ?

Thanks for any additional answer.

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-02 : 09:33:42

http://msdn2.microsoft.com/en-us/library/ms176089.aspx

books-on-line

Poor planning on your part does not constitute an emergency on my part.

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-03-02 : 10:29:32

thanks you dataguru1971 !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-03-02 : 19:51:01

varchar(500) is not so good if you data can be 800 chars. try varchar(800) instead.

elsasoft.org

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2008-03-02 : 20:42:09

Wait. It depends on the distribution of the lengths. If you have 10 million rows where the value of the column is 800 characters long and 1 where it is 15 characters long you will be better off with a char(800).

Jay
to here knows when

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-03-03 : 06:16:55

Hi guys,

I just was confusing the numbers, of course I will set varchar(800). By the way, why not set varchar(9000) immediatly, it wont eat more space, won't it?

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-03 : 06:32:38

Because if it is SQL Server 2000, maximum limit for varchar data type is 8000 characters.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-03-03 : 06:38:57

ok, but what about varchar(8000) ?

my favorit hoster is ASPnix : www.aspnix.com !

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-03 : 19:29:42

there is a row limit in number of bytes, and yes it would eat more space. Why use more than you will actually need?

Poor planning on your part does not constitute an emergency on my part.

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-03-04 : 08:49:47

Hi dataguru1971, I don't want to use more than needed of course, but I read in the suggested article :

http://msdn2.microsoft.com/en-us/library/ms176089.aspx

that "The storage size is the actual length of data entered + 2 bytes."
This would mean that whatever length I define (9 or 9000), it always takes the actual length of the data + 2 bytes, independently of the size I define.
Or do I missunderstand?

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-03-04 : 08:53:04

the point is that if you allow someone to put 8000 chars, then it will happen. in a database, anything you allow to happen, generally WILL happen.

then there won't be any room in that row left for your other columns as you will be butting up against the max size of a row, which is just over 8k (size of a single page).

elsasoft.org

fabianus76
Posting Yak Master

191 Posts

Posted - 2008-03-07 : 16:06:12

Hi jezemine !

thanks for your feedback !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources