best data type for unknown amount of text

[sagey]

Hello,


I am writing a web application that will allow users to save an unknown amount of text to a sql server 2000 database.

What would be the best data type to use. i'm figuring nvarchar migth be useful
as someone may save 2 or 2 thousand characters (or even more). however i'm aware that i'll need to define a limit with an nvarchar.

is there a data type i can use that doesn't need a limit set? what are the advantage of such a data type as opposed to an arbitraily setting an nvarchar(10000).

Performance is also a big consideration for me, what would be the most efficient data type for storing and retrieving this unknown amount of data.

if in the interests of performance i have to set an arbitrary limit, then thats a decision i'll have to live with

[Hack]

Because there is no memo field type, you will have a limit and that would be the length of the field itself that you created in your table.

Typically for "memo" fields in SQL Server we use an nvarchar(2000)

[sagey]

Thanks for the reply hack.

Just to give you a bit more background the text will be code from a number of different programming languages, that the user will be sharing with other users. this needs to happen as quickly as possible, as the users will be sharing code in (as near as possible to) realtime.

If i set the field to nvarchar(10000) (just to keep myself covered for someones particularly verbose code) for example, will that have a greater negative affect on performance than if i set the datatype to nvarchar(50000) ?

[szlamany]

MS SQL 2000 has a kind of 8000 byte max to the size of a row itself.

Do you really want to have this be unlimited?

[sagey]

hello szlamany,

it doesn't have to be unlimited, really i guess i just need to decide on a practical arbitrary nvarchar size.

What i want to avoid is the user entering a large bit of code only for the insert (or update) to fail because it exceeds the arbitrary limit i have set on the column.

What would be a practical limit in your opinion?

[szlamany]

8000 is a magic number - look at this:

Create Table BigText(IdNum int, TextValue nvarchar(10000))

got this error...

Server: Msg 131, Level 15, State 2, Line 1
The size (10000) given to the column 'TextValue' exceeds 
the maximum allowed for any data type (8000).

but what I was trying to test was the max size of a row...

So I did this:

Create Table BigText(IdNum int, TextValue1 nvarchar(4000), TextValue2 nvarchar(4000))
Go
Declare @TV nvarchar(4000)
Set @TV='1234567890'
While Len(@TV)<4000
Begin
	Set @TV=@TV+@TV
End
Insert into BigText values (1,@TV,@TV)

And got these messages

Warning: The table 'BigText' has been created but its maximum row size (16029) exceeds
 the maximum number of bytes per row (8060). 
INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds
 8060 bytes.
Server: Msg 511, Level 16, State 1, Line 7
Cannot create a row of size 16017 which is greater than the allowable maximum of 8060.
The statement has been terminated.

Remember that nvarchar takes twices as much of this internal space as varchar - do you need nvarchar??

[sagey]

er i don't know i'm afraid.

as long as the field can handle all charcters that are present in most programming languages:

vb/c/c++/c#/perl/ruby/php/java/ etc etc

then that is all that matters, in whcih case maybe a varchar is the way to go?

[szlamany]

I think it's basically your call...

Choice of nvarchar(4000) or varchar(8000) - as long as the rest of the columns in the row aren't getting close to 60 bytes...

Remember the 8060 byte max and take your pick...

Other thing to consider is maybe making it nvarchar(4000) but allowing a SeqNo to be part of the key so that you can store values the exceed the 4000 bytes in several rows.

[sagey]

Is that the approach that other people use who have to store a lot of text data?

what about a blob i notice thats what microsoft uses for the asp.net 2 personalisation provider, for when you simply don't know how much information you might be storing.

[szlamany]

Send a PM to StrangerInBeijing

http://www.vbforums.com/member.php?u=56233

Give him a link to this thread in the PM and see what he has to say...

I believe he has done quite a lot of this.

[brucevde]

???? Use the Text or NText datatype. They allow an unlimited # of characters and only take up 16 bytes of the 8060 byte row limit.

Edit: Text and NText do have a limit

Text : 2,147,483,647 characters
NText : 1,073,741,823

[szlamany]

That might be what StrangerInBeijing used...

They are not searchable datatypes though - right? They cannot be used in WHERE clauses - is that true?

[brucevde]

They cannot be used in WHERE clauses - is that true?

They can be used in Where clauses with Like but not =.
Also, some String functions cannot be used on Text or NText datatypes.

[szlamany]

Thanks for the clarification - I've never used them myself...

[brucevde]

I learned something new today...

There is an option to store Text data directly within the Data Row "if it fits". You specify a max character limit that will be stored in the data row. If the datalength is greater than the limit the data is stored outside the table (default functionality).

See the sp_TableOption system stored procedure and the "Text in Row" option.

[szlamany]

So that must be for the performance boost - nice.

I see it works with image datatypes as well...

[StrangerInBeijing]

hell...me? what did i do wrong this time?

why not just use NTEXT?
Yeah, if it's too big, it's too big. (Size matters!)
So if you know whatever data type u use are gonna give problems, figure out a new scheme, like breaking up data into different columns or something.

[sagey]

thanks all for your replies,

Just to clarify i won't need to search the text i store and there is no way of breaking it down into constituent parts, the text will just be source code from various programming languages so i presume i can get away with using a
Text data type rather than a NText data type.