SQL Server - TEXT datatype default lenth showing 16

Asked By Abhinav Singh on 22-Jul-13 04:00 AM
Hi, I created a table which has a column of datatype text.
CREATE TABLE TABTEXT
(
    EMail TEXT
)
But when I check the length of this field using sp_help, it showing the length of this field as 16.

 Column_name    Type       Computed        Length
--------------          ------          -----------          ------------
EMail            text           no            16

 Can anybody tell me what this 16 is?
Robbe Morris replied to Abhinav Singh on 22-Jul-13 09:28 AM
what version of sql server is this?
Abhinav Singh replied to Robbe Morris on 22-Jul-13 09:57 AM
SQL Server 2008
Robbe Morris replied to Abhinav Singh on 22-Jul-13 10:03 AM
You really want to use varchar(max) or nvarchar(max).  text and ntext are still supported but they don't have some of the optimized data storage "under the hood" that varchar(max) does.
Abhinav Singh replied to Robbe Morris on 22-Jul-13 10:26 AM
I know the varchar(max) can be the best option but this is not the answer of my question. I just want to know what this number 16 depict.
Robbe Morris replied to Abhinav Singh on 22-Jul-13 10:32 AM
Because the data isn't stored "in the row" for text or ntext columns, I "believe" this represents the bytes used to store a pointer to the location of the data.  I do know the pointer is stored "in the row".  I'm not just 100% certain the 16 bytes figure is the accurate representation of that pointer.