How many bytes does ntext consume?

[Bob]

I have read in several places that an attribute of data type ntext
only uses 16 bytes in SQL Server (2005) regardless of it's length, yet
an nvarchar uses 2 x n where n is the length of the nvarchar field.

I tried to test this by creating an attribute of type ntext on a
custom entity with a length of 2000 characters. My assumption, based
on what I have read, is that this will consume 16 bytes in SQL Server.
When I open up SQL Server and view the column that relates to the
newly created attribute I note that it is an nvarchar type and is
represented as (nvarchar(max), null). When I put some data into this
field and then run a query in SQL Server to return the datalength, it
returns 2 x length of field, in other words it is behaving just like
an nvarchar data type.

Can somebody explain to me if this is expected and that even though it
is telling me it is using, for example, 4128 bytes (len = 2064) it is
actually only using 16 bytes, as this doesn't appear to be the case.

Thanks

Bob

[Patrick]

In SQL there are two methods to physically store data. For in-page storage
the cell will contain the actual data, for out-of-page storage the cell will
contain a pointer to the actual data. For the basic types (e.g. int, char,
varchar etc.) the data is stored in-page, LOB's (large objects: ntext and
image) are stored out-of-page.
Sql 2005 introduced the (n)varchar(max) and varbinary(max) types. For these
types the storage engine will deceide at run time wheter to store the data in
or out of page.

If you are concerend with the size of the sql data-page with its 8192byte
limit, use the 16 bytes for any var(max)-type. The storage engine will move
any var(max) data out-of-page before any problems would occur.

--
Patrick Verbeeten (MCPD)
Lead Developer
Aviva IT

Extended Entity and Plug-in browser:
http://www.patrickverbeeten.com/maps/CrmTool.aspx

[Bob]

> > Bob- Hide quoted text -
>
> - Show quoted text -

Excellent, thanks for your help Patrick.