Character length of Data type in SQL database table

[becky]

Hi, i need some help here.
What is the length of the datatype text in the sql datatable?
There is a default value 'length' in sql table, what is the units of measurement
for the value shown? is it in Byte?
for instance, the default value for datatype text is 16. Is is mean 16 bytes
is the maximum value for the datatype text?

HOpe somebody can give me some advice.
Thank you.

Becky

[Simon Sellick]

Becky

In SQL Server 6.0, a TEXT datatype can hold 2^31 - 1 characters. They aren't
stored very efficiently: as soon as you store anything except NULL in a TEXT
column, a number of 2K byte pages are allocated, and at least one 2K page
stays allocated until the row is deleted, even if you update the column to
NULL.

If you convert them to CHAR or VARCHAR the limit is 255 bytes.

Hope this helps.

"becky" <boeykean@hotmail.com> wrote:
>
>Hi, i need some help here.
>What is the length of the datatype text in the sql datatable?
>There is a default value 'length' in sql table, what is the units of measurement
>for the value shown? is it in Byte?
>for instance, the default value for datatype text is 16. Is is mean 16 bytes
>is the maximum value for the datatype text?
>
>HOpe somebody can give me some advice.
>Thank you.
>
>Becky

[PP]

Sql 7 can store upto a data page of text in a varchar (about 8KB) where a
text is typically used for anything over 8KB. The following functions are
needed to manipulate abstract data from a text field:

DATALENGTH: length of the text in the field. LEN will only tell you how big
the pointer to the text is (Always 16 bytes).

UPDATETEXT, WRITETEXT, READTEXT: can be used in stored procedures to add,
append and select the data into variables.

TEXTPTR: needed so for manipulating the actual text rather than the point
itself.

PATINDEX: similar to the LIKE function


The only thing about TEXT field is that it is often an unlogged process and
will prevent the transaction log being backed up. You can add a WITH LOG
to the necessary statements to overcome this problem but the transaction
log then becomes very big very quickly.

If you need to store upto 8KB of text then vertically partition the table
with a 1:1 relationship between both parts and use a VARCHAR instead of a
text field.


Hope this helps.


"Simon Sellick" <simon.sellick@tesco.net> wrote:
>
>Becky
>
>In SQL Server 6.0, a TEXT datatype can hold 2^31 - 1 characters. They aren't
>stored very efficiently: as soon as you store anything except NULL in a
TEXT
>column, a number of 2K byte pages are allocated, and at least one 2K page
>stays allocated until the row is deleted, even if you update the column
to
>NULL.
>
>If you convert them to CHAR or VARCHAR the limit is 255 bytes.
>
>Hope this helps.
>
>"becky" <boeykean@hotmail.com> wrote:
>>
>>Hi, i need some help here.
>>What is the length of the datatype text in the sql datatable?
>>There is a default value 'length' in sql table, what is the units of measurement
>>for the value shown? is it in Byte?
>>for instance, the default value for datatype text is 16. Is is mean 16
bytes
>>is the maximum value for the datatype text?
>>
>>HOpe somebody can give me some advice.
>>Thank you.
>>
>>Becky
>