Advantages/Disadvantages of TEXT Column type in SQL 2005

Hello,
I know this question has been asked before, but I'd like to get the
opinions from others before I continue with my database. I am creating
a Database to track calls for a Tech Support dept. There are some
tables like tb_Call, tb_Ticket where I need to allow a user to enter a
long description if necessary. I am thinking of using the Text Field
type with a leght of 16. My question is what are the
advantages/divantages with this as opposed to using any other data
type. Any insight or recommendations you can provide will be greatly
appreciated!
Thanks in advance!The TEXT datatype is deprecated in SQL Server 2005. So do not use it for
any new work. Instead, use VARCHAR(MAX) -- that datatype has the same
maximum storage capacity as TEXT, and is a lot more flexible in terms of
being able to use string functions.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Ed_p" <edp@.nomail.com> wrote in message
news:utvCvDY6FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I know this question has been asked before, but I'd like to get the
> opinions from others before I continue with my database. I am creating a
> Database to track calls for a Tech Support dept. There are some tables
> like tb_Call, tb_Ticket where I need to allow a user to enter a long
> description if necessary. I am thinking of using the Text Field type with
> a leght of 16. My question is what are the advantages/divantages with
> this as opposed to using any other data type. Any insight or
> recommendations you can provide will be greatly appreciated!
> Thanks in advance!|||
Adam Machanic wrote:

>The TEXT datatype is deprecated in SQL Server 2005. So do not use it for
>any new work. Instead, use VARCHAR(MAX) -- that datatype has the same
>maximum storage capacity as TEXT, and is a lot more flexible in terms of
>being able to use string functions.
>
>
It's worth noting that while for [text] the default is
to store data out-of-row, for the (max) types, the
default is to store data in-row. In some cases, this
can make a difference, and it may be useful to set the
table option so that the (max) type behaves like [text]
did:
exec sp_tableoption N'MyTable', 'large value types out of row', 'ON'
Steve Kass
Drew University|||In addition to the other posts, if you have several such columns, where each
will fit inside the
regular varchar or nvarchar limit: In 2005, you have page overflow, meaning
you can have for
instance two varchar(5000) each containing 5000 characters.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ed_p" <edp@.nomail.com> wrote in message news:utvCvDY6FHA.1248@.TK2MSFTNGP14.phx.gbl...[colo
r=darkred]
> Hello,
> I know this question has been asked before, but I'd like to get the opinio
ns from others before I
> continue with my database. I am creating a Database to track calls for a
Tech Support dept.
> There are some tables like tb_Call, tb_Ticket where I need to allow a user
to enter a long
> description if necessary. I am thinking of using the Text Field type with
a leght of 16. My
> question is what are the advantages/divantages with this as opposed to
using any other data
> type. Any insight or recommendations you can provide will be greatly appr
eciated!
> Thanks in advance![/color]