Disadvantages of Using Text/nText Data Type ? 1

[JohnBates]

hi all,

Seems I remember there are certain things you cannot do with text types that you can do with varchar data types.

In general, isn't it best to use varchar rather than text ?
If you have a col text (16) SQL Server reserves that 16 bytes, whereas varchar(16) would only actually use the amount of space the data actually used, ie JOHN would take 4 bytes if in a varchar column. ?

Thanks, John

 

[SQLDenis]

>>If you have a col text (16) SQL Server reserves that 16 bytes

that is actaully a pointer to the data itself
you can not use certain functions on textfields and they can't be ordered either


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Or use text/ntext in AFTER triggers. Or GROUP BY. Or CHECKSUM().

If text value has only one (1) character it will still occupy 8kB on disk. So if there are many short text values internal waste of space becomes significant.

And generally speaking any serious manipulation with READTEXT/WRITETEXT/etc is nothing but real pain.

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]