variable-length data types

"David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>see answers below
>
>
>--
>HTH,
>David Satz
>Principal Web Engineer
>Hyperion Solutions
>{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
>(Please reply to group only - emails answered rarely)
>--------------------
>"amy" <amy@firstlogic.com> wrote in message news:3cb59cfd$1@10.1.10.29...
>>
>> Someone please refresh my memory - does a variable-length data type in
SQL
>> Server set aside the entire space specified for that data type or does
it
>> only allocate the space that is used and dynamically grow the physical
>space
>> as the data grows?
>
>no it does not set aside the entire space - there is a little overhead
>associated with varchar columns to delimit the end of text in the row and
>all, but for any column with a few character (i.e. more than 3) it will
save
>space
>
>>
>> I have a text field that is set to a size right now. Rather than checking
>> the size to see if it is at the max before inserting into the text field
>> or creating another row if it is at its max, I would like to set the max
>> size to the max size allowed (2 gig). If this actually allocates the
>physical
>> space, I don't want to do this but if it doesn't actually use the physical
>> space until the field is actually that big, I could do this.
>>
>> Any help?
>
>text columns are a different animal from varchar so I am little confused
if
>the 2 questions are related.
>
>
sorry about the confusion - I am working with a text field. A text field
is defined as variable-length data. The current size of our text field is
64512. We are inserting comments (text) into this field. I am debating
whether to implement code to check the size of the current field plus the
size of the text to be added - if it is equal to or larger than 64512, I
would have to insert another row of data instead of adding to this specific
field within the existing row. My other option is to set the size of the
field to the maximum allowed by the server (2 gig). I don't want to do this
if the physical space is actually taken up when I set the size. If it is
not taken up until the field actually grows, then I would be more inclined
to set it to the max. Hope this clears up my situation. Any ideas?

Actually, if you execute the following you can see the size of the text field:
SELECT @@TEXTSIZE
You can also set the size of the text field with the following:
SET TEXTSIZE 2048
And, I got the following from SQL Server Books on line:
The Microsoft SQL Server ntext, text, and image data types are capable
of holding extremely large amounts of data (up to 2 GB) in a single value

"David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>still confused text columns (at least in SQL Server) do not have a length
>defined ?
>
>"amy" <amykr@firstlogic.com> wrote in message news:3cb5b399$1@10.1.10.29...
>>
>> "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>> >see answers below
>> >
>> >
>> >--
>> >HTH,
>> >David Satz
>> >Principal Web Engineer
>> >Hyperion Solutions
>> >{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
>> >(Please reply to group only - emails answered rarely)
>> >--------------------
>> >"amy" <amy@firstlogic.com> wrote in message news:3cb59cfd$1@10.1.10.29...
>> >>
>> >> Someone please refresh my memory - does a variable-length data type
in
>> SQL
>> >> Server set aside the entire space specified for that data type or does
>> it
>> >> only allocate the space that is used and dynamically grow the physical
>> >space
>> >> as the data grows?
>> >
>> >no it does not set aside the entire space - there is a little overhead
>> >associated with varchar columns to delimit the end of text in the row
and
>> >all, but for any column with a few character (i.e. more than 3) it will
>> save
>> >space
>> >
>> >>
>> >> I have a text field that is set to a size right now. Rather than
>checking
>> >> the size to see if it is at the max before inserting into the text
>field
>> >> or creating another row if it is at its max, I would like to set the
>max
>> >> size to the max size allowed (2 gig). If this actually allocates the
>> >physical
>> >> space, I don't want to do this but if it doesn't actually use the
>physical
>> >> space until the field is actually that big, I could do this.
>> >>
>> >> Any help?
>> >
>> >text columns are a different animal from varchar so I am little confused
>> if
>> >the 2 questions are related.
>> >
>> >
>> sorry about the confusion - I am working with a text field. A text field
>> is defined as variable-length data. The current size of our text field
is
>> 64512. We are inserting comments (text) into this field. I am debating
>> whether to implement code to check the size of the current field plus
the
>> size of the text to be added - if it is equal to or larger than 64512,
I
>> would have to insert another row of data instead of adding to this
>specific
>> field within the existing row. My other option is to set the size of
the
>> field to the maximum allowed by the server (2 gig). I don't want to do
>this
>> if the physical space is actually taken up when I set the size. If it
is
>> not taken up until the field actually grows, then I would be more inclined
>> to set it to the max. Hope this clears up my situation. Any ideas?
>
>

Thanks for clearing that up! I know what I need to do now.

"David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>AFAIK SET TEXTSIZE has no effect on the data stored in the column only on
>how much data is returned from the text column(s) in a SELECT statement
>
>"amy" <amykr@firstlogic.com> wrote in message news:3cb5d7cb$1@10.1.10.29...
>>
>> Actually, if you execute the following you can see the size of the text
>field:
>> SELECT @@TEXTSIZE
>> You can also set the size of the text field with the following:
>> SET TEXTSIZE 2048
>> And, I got the following from SQL Server Books on line:
>> The Microsoft SQL ServerT ntext, text, and image data types are
>capable
>> of holding extremely large amounts of data (up to 2 GB) in a single value
>>
>>
>> "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>> >still confused text columns (at least in SQL Server) do not have a length
>> >defined ?
>> >
>> >"amy" <amykr@firstlogic.com> wrote in message
>news:3cb5b399$1@10.1.10.29...
>> >>
>> >> "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>> >> >see answers below
>> >> >
>> >> >
>> >> >--
>> >> >HTH,
>> >> >David Satz
>> >> >Principal Web Engineer
>> >> >Hyperion Solutions
>> >> >{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
>> >> >(Please reply to group only - emails answered rarely)
>> >> >--------------------
>> >> >"amy" <amy@firstlogic.com> wrote in message
>news:3cb59cfd$1@10.1.10.29...
>> >> >>
>> >> >> Someone please refresh my memory - does a variable-length data type
>> in
>> >> SQL
>> >> >> Server set aside the entire space specified for that data type or
>does
>> >> it
>> >> >> only allocate the space that is used and dynamically grow the
>physical
>> >> >space
>> >> >> as the data grows?
>> >> >
>> >> >no it does not set aside the entire space - there is a little overhead
>> >> >associated with varchar columns to delimit the end of text in the
row
>> and
>> >> >all, but for any column with a few character (i.e. more than 3) it
>will
>> >> save
>> >> >space
>> >> >
>> >> >>
>> >> >> I have a text field that is set to a size right now. Rather than
>> >checking
>> >> >> the size to see if it is at the max before inserting into the text
>> >field
>> >> >> or creating another row if it is at its max, I would like to set
the
>> >max
>> >> >> size to the max size allowed (2 gig). If this actually allocates
>the
>> >> >physical
>> >> >> space, I don't want to do this but if it doesn't actually use the
>> >physical
>> >> >> space until the field is actually that big, I could do this.
>> >> >>
>> >> >> Any help?
>> >> >
>> >> >text columns are a different animal from varchar so I am little
>confused
>> >> if
>> >> >the 2 questions are related.
>> >> >
>> >> >
>> >> sorry about the confusion - I am working with a text field. A text
>field
>> >> is defined as variable-length data. The current size of our text field
>> is
>> >> 64512. We are inserting comments (text) into this field. I am
>debating
>> >> whether to implement code to check the size of the current field plus
>> the
>> >> size of the text to be added - if it is equal to or larger than 64512,
>> I
>> >> would have to insert another row of data instead of adding to this
>> >specific
>> >> field within the existing row. My other option is to set the size
of
>> the
>> >> field to the maximum allowed by the server (2 gig). I don't want to
do
>> >this
>> >> if the physical space is actually taken up when I set the size. If
it
>> is
>> >> not taken up until the field actually grows, then I would be more
>inclined
>> >> to set it to the max. Hope this clears up my situation. Any ideas?
>> >
>> >
>>
>
>