Any need for TEXT/NTEXT column types in SQL2005

13 Sep 2006 7:57 PM

David W

We currently have a number of columns that can store a real variety of text
length columns - typically 300 to 16000 chars, but possible more. Typically
one column per table. In SLQ2000 they were defined as TEXT columns and the
text in row value was set to 4000.

With the Varchar(max) data type, is there really any reason to use
TEXT/NTEXT columns. I prefer the benefits of the Varchar(max) such as the
string functions, and being able to declare that type of variable in a
sproc. I would set the TableTextInRowLimit limit to 4000 or so. Are there
any gotchas I missed? A couple of points: We never use SELECT *, never
index these columns (or search them with LIKE), and all data access is
through flavors of ADO.

Thanks,
Dave

13 Sep 2006 8:03 PM

Aaron Bertrand [SQL Server MVP]

> With the Varchar(max) data type, is there really any reason to use
> TEXT/NTEXT columns.

Personally, I can't think of any, and don't have a single TEXT/NTEXT column
in any of my SQL 2005 deployments.

I'm sure someone will have an argument to post in the FOR column, but I
don't have one.

A

13 Sep 2006 8:16 PM

Warren Brunk

Here is a great article on how to maximize large data with the new max
datatypes...
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1098157,00.html

I use mostly varchar max for the same reasons you do...
Replace, substring functions will only work with varchar fields vs text
fields.

If you are not using a Full Text Indexing on the column then I would use the
varchar(max)

--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/

Show quote

"David W" <spivey@nospam.post.com> wrote in message
news:eFfvn721GHA.1568@TK2MSFTNGP03.phx.gbl...
> We currently have a number of columns that can store a real variety of
> text length columns - typically 300 to 16000 chars, but possible more.
> Typically one column per table. In SLQ2000 they were defined as TEXT
> columns and the text in row value was set to 4000.
>
> With the Varchar(max) data type, is there really any reason to use
> TEXT/NTEXT columns. I prefer the benefits of the Varchar(max) such as the
> string functions, and being able to declare that type of variable in a
> sproc. I would set the TableTextInRowLimit limit to 4000 or so. Are
> there any gotchas I missed? A couple of points: We never use SELECT *,
> never index these columns (or search them with LIKE), and all data access
> is through flavors of ADO.
>
> Thanks,
> Dave
>
>

13 Sep 2006 9:29 PM

Roy Harvey

The only reason I can think of is that there isn't time to change
applications to deal with the difference wheh converting from 2000 to
2005.

Roy Harvey
Beacon Falls, CT

Show quote

On Wed, 13 Sep 2006 15:57:58 -0400, "David W" <spivey@nospam.post.com>
wrote:

>We currently have a number of columns that can store a real variety of text
>length columns - typically 300 to 16000 chars, but possible more. Typically
>one column per table. In SLQ2000 they were defined as TEXT columns and the
>text in row value was set to 4000.
>
>With the Varchar(max) data type, is there really any reason to use
>TEXT/NTEXT columns. I prefer the benefits of the Varchar(max) such as the
>string functions, and being able to declare that type of variable in a
>sproc. I would set the TableTextInRowLimit limit to 4000 or so. Are there
>any gotchas I missed? A couple of points: We never use SELECT *, never
>index these columns (or search them with LIKE), and all data access is
>through flavors of ADO.
>
>Thanks,
>Dave
>