Length of column with 'text' data type

[Blazeix]

Hi. I'm getting various data about columns in a table by using sp_column.
Everything is working as I expected, except one thing. When I get the length of
a column that is of data type 'text', it returns the number 2147483647 when it
is supposed to return 16. I've played around with this number, and I can't seem
to find any relationship, mainly because it is prime. Is there some way I can
get the actual length of the table?

[JaredJBlackburn]

The Text datatype has a maximum length of 231-1 (2,147,483,647) characters.
That should be what the sp_columns is telling you. The DATALENGTH() function
should return the length of a specific text, if that's what you're after.

[paross1]

I'm not sure why you would expect 16. Take a look at SQL Server Books Online
for the text datatype:

text

Variable-length non-Unicode data in the code page of the server and with a
maximum length of 231-1 (2,147,483,647) characters. When the server code
page uses double-byte characters, the storage is still 2,147,483,647 bytes.
Depending on the character string, the storage size may be less than
2,147,483,647 bytes.

It looks to me like sp_columns is reporting exactly what I would expect it to.

Phil

[JaredJBlackburn]

@@TEXTSIZE should give you the current setting for the max size of a text.

SET TEXTSIZE sets or changes that setting.

[Blazeix]

[q]Originally posted by: paross1
I'm not sure why you would expect 16.[/q]

Well, I'm using SQL Server Enterprise, and when I specify that a column is
'text' it automatically sets the column length to 16. But if 2^31-1 is actually
the length, I guess i'll stick to that. Thanks for everyone's help.

[paross1]

It looks like you are confusing the length the data in a column with the length
of the data type. Since individual text data type values can be too long to
store in a single data row, they are stored in a collection of pages separate
from the pages holding the data for the other columns of the row. When that is
the case, all that is stored in the data row is a 16-byte pointer. For each
row, this pointer points to the location of the text data.

What Enterprise Manager is reporting as "length" is the length of the pointer
that actually points to the location where the data is actually stored.

Phil