Re: Best practice for storing long text fields
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Tue, 12 Sep 2006 21:28:40 +0000 (UTC)
Tzanko (tzanko.tzanev@xxxxxxxxxxxxxxxxxxxx) writes:
As we all know, there is a 8060 bytes size limit on SQL Server rows.
Yes, in SQL 2000. Not in SQL 2005. There a row can span pages.
I have a table which requires a number of text fields (5 or 6).
Do these text fields hold the same text that spans fields, or are
they different texts?
I am given to review a design, which esentially suggests moving the text
columns to a separate TextFields table. The TextFields table will have
two columns - a unique reference and a VARCHAR (4000) column, thus
allowing us to crossreference with the original record.
If they are different texts they should be in different columns, or you
should have some type column telling them apatt.
My first impresion is that I'd rather use the SQL Server 'text' DB type
instead, which would allow me the same functionality with much less
effort and possibly better performance.
Yes, if they the column are all the same text, this might be the way
to go. You can store up to 2GB in a text column.
But better performance? Nah. If nothing else, text is difficult to
work with and there are lot of limitations. As Ed mention, SQL 2005
comes with varchar(MAX) which also can fit 2GB, but which you can
work with in the same way as a regular varchar.
If the columns are different texts, I see little point to use the
text data type.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
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
.
- Follow-Ups:
- References:
- Best practice for storing long text fields
- From: Tzanko
- Best practice for storing long text fields
- Prev by Date: Re: SQL troubles with VPN connection
- Next by Date: Re: Tree table
- Previous by thread: Re: Best practice for storing long text fields
- Next by thread: Re: Best practice for storing long text fields
- Index(es):
Relevant Pages
|
