Click here to Skip to main content
15,886,578 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,
I created a table which has a column of datatype text.

SQL
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?
Posted
Updated 22-Jul-13 0:41am
v3

SQL
CREATE TABLE TABTEXT
(
    EMail TEXT
)

Insert into TABTEXT 
Select 'a'

Select COL_LENGTH('TABTEXT','EMail') as ColumnLength
Select EMail,Datalength(EMail) as DataLength From TABTEXT 

In sp_help if you give a tableName...Additional result set returned on column objects
and Length is Nothing but the column length given in Bytes...

Col_Length:Returns the defined length, in bytes, of a column.The 16 what you are getting is nothing but column length of EMail column in Bytes in that TABTEXT table, by Default it takes the length of text column as 16... If you enter a some data into that column u can check the length in bytes by using DataLength... if you want to check the length in characters u can declare a variable as varchar or nvarchar and assign the column value to that variable and use len on that variable..

The output for the above queries is
SQL
ColumnLength
------------
  16

EMail	DataLength
-----   ----------
 a	 1


Hope this Answers your Question....
Check this links http://msdn.microsoft.com/en-us/library/ms187335.aspx[sp_help]
http://msdn.microsoft.com/en-us/library/ms188732.
http://msdn.microsoft.com/en-us/library/ms173486.aspx
 
Share this answer
 
v9
Comments
Raja Sekhar S 25-Jul-13 3:04am    
u got what u wanted..?
16 is the default minimum recommended value.
Please see more[^]
 
Share this answer
 
Comments
astrovirgin 22-Jul-13 7:03am    
But why 16? how much size will it take if I enter a single character? I need details.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900