Posted by Erland Sommarskog on 11/28/90 11:57

Cylix (cylix2000@gmail.com) writes:
> How about the volume?
> Is NULL value also use the harddisk.

A text value consists of a 16-byte pointer which is stored in the row,
and then the rest of the data is stored outside the row. (Unless you
have set the table option "text in row".

The 16 byte pointer takes up space also for a NULL value. If any space
is allocated for the empty string, I will have to confess that I don't
know. You could examine this by creating two tables with ntext columns,
and fill one with NULL values and one with empty strings, and then use
sp_spaceused to see if there is any difference.

Permit me also to norice that ntext is a data type that is on its way out.
In SQL 2005 there is a new data type nvarchar(MAX) which just like ntext
permits 2GB of data, but works like regular nvarchar, and does not have
the many quirks of ntext.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Download the JAVA , .NET and SQL Server interview question with answers
• Prev in forum: Re: An interesting Lastname, Firstname, Middlename challenge
• Thread view: Re: Query performence about nText

[Reply to this message]