Maximum length of Datatype text...

Hello,
I see that the max length of datatype text is some where abouts 2
million characters.
My question is, when I conenct the MSDE database to SQL Enterprise
administrator, and look at the table design, the length of the data
reads "16"
It's quite obvious this doesn't mean 16 characters... what does it
mean? 16 bytes of information? if so, that's an insanely large
amount; but that's okay. Right now, just to be safe, I've limited the
input to 200 characters. After you finish snickering to yourself, how
many characters does this value of 16 equate out to? 2 ba-gillion?
Thank!
Matt
On Wed, 11 Jul 2007 13:45:52 -0700, Matt Brown - identify wrote:

>Hello,
>I see that the max length of datatype text is some where abouts 2
>million characters.
Hi Matt,
More, actuallly. 2Gb of characters, meaning well over 2 billion
characters.

>My question is, when I conenct the MSDE database to SQL Enterprise
>administrator, and look at the table design, the length of the data
>reads "16"
That's the length of the pointer.
For a text column, the only thing stored in the regular storage area for
the table is a 16-byte pointer. This pointer points to the start of the
structures that store the real information, up to 2Gb. The sice of this
information is not reported in Enterprise Manager.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On Jul 11, 6:46 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Wed, 11 Jul 2007 13:45:52 -0700, Matt Brown - identify wrote:
>
> Hi Matt,
> More, actuallly. 2Gb of characters, meaning well over 2 billion
> characters.
>
> That's the length of the pointer.
> For a text column, the only thing stored in the regular storage area for
> the table is a 16-byte pointer. This pointer points to the start of the
> structures that store the real information, up to 2Gb. The sice of this
> information is not reported in Enterprise Manager.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
So... Hugo... you're saying I've got relatively no limit aka a 2GB
limit per-data type entry? like 200 characters is a comical limit? I
can more like make it 2000 without a problem?
|||On Thu, 12 Jul 2007 03:34:21 -0000, Matt Brown - identify wrote:

>So... Hugo... you're saying I've got relatively no limit aka a 2GB
>limit per-data type entry? like 200 characters is a comical limit? I
>can more like make it 2000 without a problem?
Hi Matt,
(Sorry for the delayed reply.)
That is indeed what I am saying. I'd actually go a step further, and say
that as long as you are under the limit of 8000 bytes (that is 4000
characters for unicode text or 8000 character for ASCII text), you
should not even use the text / ntext datatypes at all. These datatypes
have many limitations, making them awkward to work with. Use varchar or
nvarchar for strings with a length of up to 8000 bytes. Onlu ise text or
text if you really need to store more characters.
Also note that in SQL Server 2005, text and ntext have been replaced by
varchar(max) and nvarchar(max), with the same length limit but without
most of the limitations. For compatibility reasons, text and ntext are
still supported - but they will be removed in a future version. For SQL
Server 2005, the advice to use varchar and nvarchar for strings up to
8000 bytes still stands, but you shoud use varchar(max) or nvarchar(max)
rather than text or ntext for longer strings.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis