text or varchar ?

[Sam]

Hi,
What is the maximum character a text type column can contain ? Can't
change the length to upper than 16....
What is the bigger ? Varchar(8000) or Text ?

Regards

[Stu]

Text is a actually a binary representation of data; it is virtually
unlimited in terms of the characters that can be inserted into.
However, you can run into some issues, as noted in the SQL Books
OnLine:

"If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression."

HTH, Stu

[Hugo Kornelis]

On 31 May 2005 08:53:53 -0700, Sam wrote:

Hi,
What is the maximum character a text type column can contain ? Can't
change the length to upper than 16....
What is the bigger ? Varchar(8000) or Text ?

Hi Sam,
The length you see for text columns in tools such as Enterprise Manager
is actually the length of a pointer. This pointer points to a seperate
location where the actual text is stored. The pointer is part of the
data row; the text itself is not. This keeps the data row small
(speeding up queries that dopn't use the text), but makes access to the
text slow (since another page has to be read).

The maximum length of a text value is 2 GB. (2,147,483,647 characters).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)