How much space does the long character data type use.

We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
One of the tables we use have a COMMENT field which is a
Long Character datatype. In SQL the data type show up
as 'Text'. How does this data type utilize disk space?
Would the size grow based on the number of characters
entered in the field or does the system reserve space as
it does with other data type. How much space does it
reserve when a field is the long character datatype.
There is a 16 byte overhead on the text, ntext, and image data types.
If your data in these columns is small, look at "Text in Row Data" in BOL.
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
news:075c01c42d44$45936490$7d02280a@.phx.gbl...
> We currently use PeopleSoft HRMS on MS SQL SERVER 2000.
> One of the tables we use have a COMMENT field which is a
> Long Character datatype. In SQL the data type show up
> as 'Text'. How does this data type utilize disk space?
> Would the size grow based on the number of characters
> entered in the field or does the system reserve space as
> it does with other data type. How much space does it
> reserve when a field is the long character datatype.
>
|||Hi Barry,
Thanks you for replying.
What is puzzling me is that when I run sp_help on the
table I see that the EMPLID field is CHAR with a length of
11 which I can understand because you cannot enter more
than 11 characters in the EMPLID field. However the
COMMENT field is TEXT with a length of 16 but you can
enter much more than 16 characters.
How is this possible and how does it affect the disk space
utilization?

>--Original Message--
>There is a 16 byte overhead on the text, ntext, and image
data types.
>If your data in these columns is small, look at "Text in
Row Data" in BOL.
>--
>Barry McAuslin
>Look inside your SQL Server files with SQL File Explorer.
>Go to http://www.sqlfe.com for more information.
>"Emerson Dixon" <edixon64@.hotmail.com> wrote in message
>news:075c01c42d44$45936490$7d02280a@.phx.gbl...
>
>.
>
|||The max length for text data is 2GB and the reported size is the 'text in
row' size. With the default length of 16, only a pointer to the separate
text pages is stored in the data row. You can adjust the 'text in row size
to control how much text data is stored in the data row but the max length
is still 2GB.
See the SQL 2000 Books Online <cm_8_des_04_0x5t.htm> for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Emerson" <edixon64@.hotmail.com> wrote in message
news:69c501c42ea8$31b315a0$a001280a@.phx.gbl...[vbcol=seagreen]
> Hi Barry,
> Thanks you for replying.
> What is puzzling me is that when I run sp_help on the
> table I see that the EMPLID field is CHAR with a length of
> 11 which I can understand because you cannot enter more
> than 11 characters in the EMPLID field. However the
> COMMENT field is TEXT with a length of 16 but you can
> enter much more than 16 characters.
> How is this possible and how does it affect the disk space
> utilization?
> data types.
> Row Data" in BOL.
|||Hi Dan,
Thanks a lot. This clears up my question.

>--Original Message--
>The max length for text data is 2GB and the reported size
is the 'text in
>row' size. With the default length of 16, only a pointer
to the separate
>text pages is stored in the data row. You can adjust
the 'text in row size
>to control how much text data is stored in the data row
but the max length
>is still 2GB.
>See the SQL 2000 Books Online <cm_8_des_04_0x5t.htm> for
details.[vbcol=seagreen]
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Emerson" <edixon64@.hotmail.com> wrote in message
>news:69c501c42ea8$31b315a0$a001280a@.phx.gbl...
of[vbcol=seagreen]
space[vbcol=seagreen]
image[vbcol=seagreen]
in[vbcol=seagreen]
Explorer.[vbcol=seagreen]
2000.[vbcol=seagreen]
is a[vbcol=seagreen]
up[vbcol=seagreen]
space?[vbcol=seagreen]
space as
>
>.
>