Query performence about nText

[Cylix]

Is there any difference between a nText field value='' and value=NULL ?

[Madhivanan]

You should compare Null with is

where value='' or value IS Null

Madhivanan

Cylix wrote:

Is there any difference between a nText field value='' and value=NULL ?

[Cylix]

How about the volume?
Is NULL value also use the harddisk.

[Ed Murphy]

Cylix wrote:

How about the volume?
Is NULL value also use the harddisk.

Surely this doesn't make a significant difference. Unless you're
dealing with an embedded system, in which case surely you should
use a SQL engine specifically designed for embedded systems.

[Erland Sommarskog]

Cylix (cy*******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx