Caveman's Blog

My commitment to learning.

SQL Server: How to compute the length of a ntext datatyped column?

with one comment

ntext, text and image are the available fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data in a SQL Server database. Following table lists the maximum possible storage capacity and the nature of the data that can be stored.

Data Type Size Description
ntext 230 – 1 (1,073,741,823) characters Variable-length Unicode data
text 231-1 (2,147,483,647) characters Variable-length non-Unicode data
image 231-1 (2,147,483,647) bytes Variable-length binary data

Following is one of the ways of computing the length of data in a column whose data type is one the above mentioned types. The len() function does not work with these data types and we will have to user the DATALENGTH() function instead. This function returns the number of bytes used to represent any expression. The output of the function when divided by 2 gives us the number of characters stored in a column (ntext, text). Here is the usage:

declare @temptable Table(Id int identity(1,1), name varchar(50), data ntext)

--Insert dummy date into table 1
Insert @temptable(name, data) values('test 1', 'abc')
Insert @temptable(name, data) values('test 2', 'abcdef')
Insert @temptable(name, data) values('test 3', 'abcdefghi')

This is what happens when we try to use the len() function in a SQL statement on the data column:

select *, LEN(Data) DATA_SIZE from @temptable

--Msg 8116, Level 16, State 1, Line 8
--Argument data type ntext is invalid for argument 1 of len function.

DATALENGTH function comes to our rescue:

select *, DATALENGTH(Data)/2 DATA_SIZE from @temptable


1. MSDN Online


Written by cavemansblog

February 19, 2010 at 12:10 pm

One Response

Subscribe to comments with RSS.

  1. After reading you site, Your site is very useful for me .I bookmarked your site!
    I am been engaged 10 years on the Free finance personal software If you have some questions, please get in touch with me.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: