sql server msde: 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

[quoted text, click to view]

Hi Matt,

More, actuallly. 2Gb of characters, meaning well over 2 billion
characters.

[quoted text, click to view]

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

On Jul 11, 6:46 pm, Hugo Kornelis
[quoted text, click to view]

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?

[quoted text, click to view]

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