Datatype Needed!!

[Warnie]

Hi,

I have a column in a table which should store more than 10000 characters of data. I tried varchar which only gives me 8000 characters. Which datatype should I use for this? Please respond. It's urgent.

Thanks,
Warnie

 

[nigelrivett]

To hold large amounts of data use a text datatype but you will be limitted in the operations you can preform on it.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Warnie]

Thanks Nigel. But I tried text and it seems to only hold 256 characters. May I wrong in my understanding?

 

[nigelrivett]

>>May I wrong in my understanding?
Yes text holds 2,147,483,647 chars.

Apart from text in row it holds a pointer to the page where the data is held so some string functions will not work on text datatypes.
Don't know where you get 256 from (you are using sql server?). Have a look in bol.

How are you displaying the text? Query analyser defaults to 256 chaars max per column for results - go to tools, options, results to change it.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[NathanGriffiths]

It could also be limited to 256 chars if the Text In Row table option is set on, as this is the default limit for text in row.

run:

select objectproperty(object_id('<table name>'),'TableTextInRowLimit')

If this returns 0, text in row is off so its probably Query Analyser truncating your results. If it returns 256, or any other value you might want to set it to off, using sp_tableoption;

sp_tableoption '<table name>','text in row','off')

This should allow the you to store up to the maximum 2GB per value.

Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------

 

[nigelrivett]

The text in row option doesn't limit the size of the text - if the text is larger than the limit or there is not enough room in the row then a pointer will be stored in the row and the text stored as with a normal text datatype.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[NathanGriffiths]

Nigel, you're right, text in row doesn't limit the size of data. Thanks for correcting me there, don't know where I got that from!

Been one of those days...




Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------