best data type for unknown amount of text
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
Re: best data type for unknown amount of text
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)
Re: best data type for unknown amount of text
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) ?
Re: best data type for unknown amount of text
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?
Re: best data type for unknown amount of text
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?
Re: best data type for unknown amount of text
8000 is a magic number - look at this:
Code:
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:
Code:
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
Code:
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??
Re: best data type for unknown amount of text
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?
Re: best data type for unknown amount of text
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.
Re: best data type for unknown amount of text
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.
Re: best data type for unknown amount of text
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.
Re: best data type for unknown amount of text
???? 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
Re: best data type for unknown amount of text
That might be what StrangerInBeijing used...
They are not searchable datatypes though - right? They cannot be used in WHERE clauses - is that true?
Re: best data type for unknown amount of text
Quote:
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.
Re: best data type for unknown amount of text
Thanks for the clarification - I've never used them myself...
Re: best data type for unknown amount of text
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.
Re: best data type for unknown amount of text
So that must be for the performance boost - nice.
I see it works with image datatypes as well...
Re: best data type for unknown amount of text
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.
Re: best data type for unknown amount of text
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.