Best practice for storing long text fields

[Tzanko]

As we all know, there is a 8060 bytes size limit on SQL Server rows. I
have a table which requires a number of text fields (5 or 6). Each of
these text fields should support a max of 4000 characters. We currently
store the data in varchar columns, which worked fine untill our
appetite for text fields increased to the current requirement of 5, 6
fields of 4000 characters size. 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. 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.
Can anyone advise on advantages and disadvantages of the two options
and what the best practice in this case would be.
Any advise will be well appreciated.
Tzanko

[Ed Murphy]

Tzanko wrote:

As we all know, there is a 8060 bytes size limit on SQL Server rows. I
have a table which requires a number of text fields (5 or 6). Each of
these text fields should support a max of 4000 characters. We currently
store the data in varchar columns, which worked fine untill our
appetite for text fields increased to the current requirement of 5, 6
fields of 4000 characters size. 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. 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.
Can anyone advise on advantages and disadvantages of the two options
and what the best practice in this case would be.

I hear that VARCHAR(MAX) is the new TEXT, but it's only available
in SQL 2005.

[Erland Sommarskog]

Tzanko (tz***********@strategicthought.com) 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, 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

[@sh]

Are you saying that in SQL 2000 you can Span VarChar's into multiple columns
automatically? If so how?

Cheers, @sh

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...

Tzanko (tz***********@strategicthought.com) 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, 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

[Erland Sommarskog]

@sh (sp**@spam.com) writes:

Are you saying that in SQL 2000 you can Span VarChar's into multiple
columns automatically? If so how?

No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.
--
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

[@sh]

Cool!
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...

@sh (sp**@spam.com) writes:

>Are you saying that in SQL 2000 you can Span VarChar's into multiple
columns automatically? If so how?

No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.
--
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

[Tzanko]

Many thnaks for your replies.

Just to clarify the issue:
The requirement is to create a table that has say 6 columns which store
strings (such as Description, Notes, etc.) Each of these 6 columns
should store a char string of max length of 4000 characters. The
problem is that SQL Server 2000 will not work if I simply defined the
columns as varchar(4000) as at some point the row size reaches the page
size of 8060 and this generates an error. There is a 8060 bytes limit
on SQL Server 2000 rows. Note that I am not trying to store the same
string into 6 different columns spanning from column to column. I have
a separate string to store in each column.

The question:
What is the best way to implement this in SQL Server 2000. In
particular I am looking at two options: Setting each of the 6 columns
to be of type 'text'. Looking at the documentation, it appears that
this would behave for as long as each string is not longer than 4000
characters and I am happy to have this limit. It however is unpleasant
to use the text type for longer than 4000 char strings, as in this case
I understand there are some specific ways of handling the data. Option
two is to create a new LongStrings table with 2 columns - long unique
number and varchar(4000). Each string is stored in this LongStrings
table and is crosreferenced (by using the unique ID) with its original
cell in its original table. Now I'd preffer option 1 (provided I do not
have to do anything special to handle the strings) and would like to
avoid option 2 because it is not easy to write queries to get the data.

Second question is what is the situation with SQL Server 2005. I
understand I can simply define the columns as varchar(max) and do not
have to do anything special. Has someone used this successfully and can
you confirm it ste case?

Thanks for your help.

Tzanko
@sh wrote:

Cool!
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...

@sh (sp**@spam.com) writes:

Are you saying that in SQL 2000 you can Span VarChar's into multiple
columns automatically? If so how?

No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.
--
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

[Erland Sommarskog]

Tzanko (tz***********@strategicthought.com) writes:

The question:
What is the best way to implement this in SQL Server 2000. In
particular I am looking at two options: Setting each of the 6 columns
to be of type 'text'. Looking at the documentation, it appears that
this would behave for as long as each string is not longer than 4000
characters and I am happy to have this limit. It however is unpleasant
to use the text type for longer than 4000 char strings, as in this case
I understand there are some specific ways of handling the data. Option
two is to create a new LongStrings table with 2 columns - long unique
number and varchar(4000). Each string is stored in this LongStrings
table and is crosreferenced (by using the unique ID) with its original
cell in its original table. Now I'd preffer option 1 (provided I do not
have to do anything special to handle the strings) and would like to
avoid option 2 because it is not easy to write queries to get the data.

The best in my opinion is to create two or three new tables and rename
the existing tbable, and the create a view that unifies them all. Then in
SQL 2005 you can scrap the view, and move the columns back to the mother
table. Very litte code would actually be affected.

If the key of the table is (cola, colb) the new tables should also have
the keys (cola, colb). Simply, what you do is that you split the columns
over several tables.

You should consider text or varchar(max) if you really need to fit more
than 8000 characters.
--
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