microsoft.public.sqlserver.server - Varchar or Text fields and Max Row Size

Author

Varchar or Text fields and Max Row Size

Danny J. Lesandrini

2002-10-29, 9:23 am

I searched Google and found some suggestions, but nothing
that directly answered my question. What is your opinion of
this scenario:

I'm designing a table that will contain the answers to about
25 multi-part questions. It's going to require about 55 fields
in the Answers table and about 15 of them are for Comments.
If I use VARCHAR(1000) fields for Comments, that will use
up 1.5 k of my 2 k page, leaving little room for the other 40
fields.

If I use TEXT fields, will that not free up the row space?

How might that affect searches on those fields, if someone
wanted to look for answers with specific notes in comment
fields?

Would it make a difference to know that there will be few
records? (less than 1000 records per year)

Thanks in advance for your insights. I look forward to
seeing what kind of feedback comes out of this.
--
Danny J. Lesandrini
dlesandrini@hotmail.com

Tony Rogerson

2002-10-29, 10:23 am

I'd denormalise your table so that answers are stored one per row.

The page size is now 8K and has been since 7.0 so you have more room for
varchar.

The text method will take up more space because it won't use the space as
efficiently as varchar - you could use text in row (sp_tableoption) to store
the text in the data page if small enough, otherwise it goes out onto its
own data page.

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
http://www.sql-server.co.uk [UK User Group, FAQ, KB's etc..]
http://www.sql-server.co.uk/tr [To Hire me]

"Danny J. Lesandrini" <dlesandrini@hotmail.com> wrote in message
news:apm8k5$2rqkb$1@ID-82595.news.dfncis.de...
> I searched Google and found some suggestions, but nothing
> that directly answered my question. What is your opinion of
> this scenario:
>
> I'm designing a table that will contain the answers to about
> 25 multi-part questions. It's going to require about 55 fields
> in the Answers table and about 15 of them are for Comments.
> If I use VARCHAR(1000) fields for Comments, that will use
> up 1.5 k of my 2 k page, leaving little room for the other 40
> fields.
>
> If I use TEXT fields, will that not free up the row space?
>
> How might that affect searches on those fields, if someone
> wanted to look for answers with specific notes in comment
> fields?
>
> Would it make a difference to know that there will be few
> records? (less than 1000 records per year)
>
> Thanks in advance for your insights. I look forward to
> seeing what kind of feedback comes out of this.
> --
> Danny J. Lesandrini
> dlesandrini@hotmail.com
>
>
>
>

Danny J. Lesandrini

2002-10-29, 10:24 am

Ooops, you're right. I wasn't thinking clearly about the row size.
But even 8 k would be a problem with 15 comment fields, since I
think they will need more than 1000 k per answer.

I was wondering if, when the text is stored on its own page, that
makes it more difficult to include in WHERE clause text searches?
--
Danny J. Lesandrini
dlesandrini@hotmail.com
http://datafast.cjb.net

"Tony Rogerson" <tonyrogerson@sqlserver.eu.com> wrote in message
news:upOVSF2fCHA.2584@tkmsftngp12...
> I'd denormalise your table so that answers are stored one per row.
>
> The page size is now 8K and has been since 7.0 so you have more room for
> varchar.
>
> The text method will take up more space because it won't use the space as
> efficiently as varchar - you could use text in row (sp_tableoption) to store
> the text in the data page if small enough, otherwise it goes out onto its
> own data page.
>
> --
> Tony Rogerson SQL Server MVP
> Torver Computer Consultants Ltd
> http://www.sql-server.co.uk [UK User Group, FAQ, KB's etc..]
> http://www.sql-server.co.uk/tr [To Hire me]
>
>
> "Danny J. Lesandrini" <dlesandrini@hotmail.com> wrote in message
> news:apm8k5$2rqkb$1@ID-82595.news.dfncis.de...
> > I searched Google and found some suggestions, but nothing
> > that directly answered my question. What is your opinion of
> > this scenario:
> >
> > I'm designing a table that will contain the answers to about
> > 25 multi-part questions. It's going to require about 55 fields
> > in the Answers table and about 15 of them are for Comments.
> > If I use VARCHAR(1000) fields for Comments, that will use
> > up 1.5 k of my 2 k page, leaving little room for the other 40
> > fields.
> >
> > If I use TEXT fields, will that not free up the row space?
> >
> > How might that affect searches on those fields, if someone
> > wanted to look for answers with specific notes in comment
> > fields?
> >
> > Would it make a difference to know that there will be few
> > records? (less than 1000 records per year)
> >
> > Thanks in advance for your insights. I look forward to
> > seeing what kind of feedback comes out of this.
> > --
> > Danny J. Lesandrini
> > dlesandrini@hotmail.com
> >
> >
> >
> >
>
>

Bill Hollinshead [MS]

2002-10-29, 3:23 pm

Hi Danny ,

You may want to consider the 2nd "Steps Using" phrase/section of
http://support.microsoft.com/suppor.../q292/2/99.asp. Consider
using the text data type in place of the bit data type that is mentioned
within that article (where you need text as oppsed to TRUE/FALSE or
YES/NO). Using text (not considering "text in row") will require an
additional 16 bytes (for a text pointer) per row
(http://msdn.microsoft.com/library/e...ar_da2_5f1d.asp). As
Tony said, using text may easily require extra pages for the table, and you
should always use the smallest data type possible. Whether the text is in a
row or not, you can use LIKE
(http://msdn.microsoft.com/library/e..._la-lz_115x.asp),
CONTAINS
(http://msdn.microsoft.com/library/e..._qd_15_64dv.asp and
http://msdn.microsoft.com/library/e..._ca-co_2y2h.asp), and
CONTAINSTABLE
(http://msdn.microsoft.com/library/e..._ca-co_3syt.asp),
FREETEXT
(http://msdn.microsoft.com/library/e..._fa-fz_2juc.asp),
and/or FREETEXTTABLE
(http://msdn.microsoft.com/library/e..._fa-fz_1f51.asp) with
text. While text columns cannot be indexed in the traditional SQL Server
sense, creating a full-text index
(http://msdn.microsoft.com/library/e...fullad_3bs2.asp and
http://msdn.microsoft.com/library/e..._ar_da_80tv.asp) can
enhance the speed at which text is found (perhaps within a WHERE clause)
and enables the use of some of the preceding functions. For relatively
small tables, the performance improvement from full-text indexing may not
be noticeable, although you may still find some of the above functions to
be desirable. You can read about SQL Server's page structure, and other
database design considerations, within Kalen Delaney's Inside Microsoft SQL
Server 2000.

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Danny J. Lesandrini

2002-10-29, 7:23 pm

Thanks much. It will take me some time to digest this,
but I really appreciate the tips ... and links!
--
Danny J. Lesandrini
dlesandrini@hotmail.com
http://datafast.cjb.net

""Bill Hollinshead [MS]"" <billhol@online.microsoft.com> wrote in message
news:$fxnt54fCHA.1896@cpmsftngxa09...
> Hi Danny ,
>
> You may want to consider the 2nd "Steps Using" phrase/section of
> http://support.microsoft.com/suppor.../q292/2/99.asp. Consider
> using the text data type in place of the bit data type that is mentioned
> within that article (where you need text as oppsed to TRUE/FALSE or
> YES/NO). Using text (not considering "text in row") will require an
> additional 16 bytes (for a text pointer) per row
> (http://msdn.microsoft.com/library/e...ar_da2_5f1d.asp). As
> Tony said, using text may easily require extra pages for the table, and you
> should always use the smallest data type possible. Whether the text is in a
> row or not, you can use LIKE
> (http://msdn.microsoft.com/library/e..._la-lz_115x.asp),
> CONTAINS
> (http://msdn.microsoft.com/library/e..._qd_15_64dv.asp and
> http://msdn.microsoft.com/library/e..._ca-co_2y2h.asp), and
> CONTAINSTABLE
> (http://msdn.microsoft.com/library/e..._ca-co_3syt.asp),
> FREETEXT
> (http://msdn.microsoft.com/library/e..._fa-fz_2juc.asp),
> and/or FREETEXTTABLE
> (http://msdn.microsoft.com/library/e..._fa-fz_1f51.asp) with
> text. While text columns cannot be indexed in the traditional SQL Server
> sense, creating a full-text index
> (http://msdn.microsoft.com/library/e...fullad_3bs2.asp and
> http://msdn.microsoft.com/library/e..._ar_da_80tv.asp) can
> enhance the speed at which text is found (perhaps within a WHERE clause)
> and enables the use of some of the preceding functions. For relatively
> small tables, the performance improvement from full-text indexing may not
> be noticeable, although you may still find some of the above functions to
> be desirable. You can read about SQL Server's page structure, and other
> database design considerations, within Kalen Delaney's Inside Microsoft SQL
> Server 2000.
>
> Thanks,
>
> Bill Hollinshead
> Microsoft, SQL Server
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>

Matthew Bando

2002-10-30, 7:23 am

The biggest thing you should take from all of this is Tony's suggestion to
change your design to use one row per answer, rather than one row for all
answers.

Matthew Bando
BandoM@CSCTechnologies.com

"Danny J. Lesandrini" <dlesandrini@hotmail.com> wrote in message
news:apn90u$383bh$1@ID-82595.news.dfncis.de...
> Thanks much. It will take me some time to digest this,
> but I really appreciate the tips ... and links!
> --
> Danny J. Lesandrini
> dlesandrini@hotmail.com
> http://datafast.cjb.net
>
>
>
> ""Bill Hollinshead [MS]"" <billhol@online.microsoft.com> wrote in message
> news:$fxnt54fCHA.1896@cpmsftngxa09...
> > Hi Danny ,
> >
> > You may want to consider the 2nd "Steps Using" phrase/section of
> > http://support.microsoft.com/suppor.../q292/2/99.asp. Consider
> > using the text data type in place of the bit data type that is mentioned
> > within that article (where you need text as oppsed to TRUE/FALSE or
> > YES/NO). Using text (not considering "text in row") will require an
> > additional 16 bytes (for a text pointer) per row
> > (http://msdn.microsoft.com/library/e...ar_da2_5f1d.asp). As
> > Tony said, using text may easily require extra pages for the table, and
you
> > should always use the smallest data type possible. Whether the text is
in a
> > row or not, you can use LIKE
> > (http://msdn.microsoft.com/library/e..._la-lz_115x.asp),
> > CONTAINS
> > (http://msdn.microsoft.com/library/e..._qd_15_64dv.asp and
> > http://msdn.microsoft.com/library/e..._ca-co_2y2h.asp), and
> > CONTAINSTABLE
> > (http://msdn.microsoft.com/library/e..._ca-co_3syt.asp),
> > FREETEXT
> > (http://msdn.microsoft.com/library/e..._fa-fz_2juc.asp),
> > and/or FREETEXTTABLE
> > (http://msdn.microsoft.com/library/e..._fa-fz_1f51.asp) with
> > text. While text columns cannot be indexed in the traditional SQL Server
> > sense, creating a full-text index
> > (http://msdn.microsoft.com/library/e...fullad_3bs2.asp and
> > http://msdn.microsoft.com/library/e..._ar_da_80tv.asp) can
> > enhance the speed at which text is found (perhaps within a WHERE clause)
> > and enables the use of some of the preceding functions. For relatively
> > small tables, the performance improvement from full-text indexing may
not
> > be noticeable, although you may still find some of the above functions
to
> > be desirable. You can read about SQL Server's page structure, and other
> > database design considerations, within Kalen Delaney's Inside Microsoft
SQL
> > Server 2000.
> >
> > Thanks,
> >
> > Bill Hollinshead
> > Microsoft, SQL Server
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
> >
>
>

Danny J. Lesandrini

2002-10-31, 10:23 am

Thanks for the confirmation. That was my original plan,
but something a colleage said spooked me from it. I'm still
concerned about how I'll handle updates, but the ASP is
in .Net, so there's got to be some efficient way to do that.

Thanks.
--
Danny J. Lesandrini
dlesandrini@hotmail.com
http://datafast.cjb.net

"Matthew Bando" <mbando@csctechnologies.com> wrote in message news:#V8FuWBgCHA.2424@tkmsftngp11...
> The biggest thing you should take from all of this is Tony's suggestion to
> change your design to use one row per answer, rather than one row for all
> answers.
>
> Matthew Bando
> BandoM@CSCTechnologies.com
>
>
> "Danny J. Lesandrini" <dlesandrini@hotmail.com> wrote in message
> news:apn90u$383bh$1@ID-82595.news.dfncis.de...
> > Thanks much. It will take me some time to digest this,
> > but I really appreciate the tips ... and links!
> > --
> > Danny J. Lesandrini
> > dlesandrini@hotmail.com
> > http://datafast.cjb.net
> >
> >
> >
> > ""Bill Hollinshead [MS]"" <billhol@online.microsoft.com> wrote in message
> > news:$fxnt54fCHA.1896@cpmsftngxa09...
> > > Hi Danny ,
> > >
> > > You may want to consider the 2nd "Steps Using" phrase/section of
> > > http://support.microsoft.com/suppor.../q292/2/99.asp. Consider
> > > using the text data type in place of the bit data type that is mentioned
> > > within that article (where you need text as oppsed to TRUE/FALSE or
> > > YES/NO). Using text (not considering "text in row") will require an
> > > additional 16 bytes (for a text pointer) per row
> > > (http://msdn.microsoft.com/library/e...ar_da2_5f1d.asp). As
> > > Tony said, using text may easily require extra pages for the table, and
> you
> > > should always use the smallest data type possible. Whether the text is
> in a
> > > row or not, you can use LIKE
> > > (http://msdn.microsoft.com/library/e..._la-lz_115x.asp),
> > > CONTAINS
> > > (http://msdn.microsoft.com/library/e..._qd_15_64dv.asp and
> > > http://msdn.microsoft.com/library/e..._ca-co_2y2h.asp), and
> > > CONTAINSTABLE
> > > (http://msdn.microsoft.com/library/e..._ca-co_3syt.asp),
> > > FREETEXT
> > > (http://msdn.microsoft.com/library/e..._fa-fz_2juc.asp),
> > > and/or FREETEXTTABLE
> > > (http://msdn.microsoft.com/library/e..._fa-fz_1f51.asp) with
> > > text. While text columns cannot be indexed in the traditional SQL Server
> > > sense, creating a full-text index
> > > (http://msdn.microsoft.com/library/e...fullad_3bs2.asp and
> > > http://msdn.microsoft.com/library/e..._ar_da_80tv.asp) can
> > > enhance the speed at which text is found (perhaps within a WHERE clause)
> > > and enables the use of some of the preceding functions. For relatively
> > > small tables, the performance improvement from full-text indexing may
> not
> > > be noticeable, although you may still find some of the above functions
> to

> > > be desirable. You can read about SQL Server's page structure, and other
> > > database design considerations, within Kalen Delaney's Inside Microsoft
> SQL
> > > Server 2000.
> > >
> > > Thanks,
> > >
> > > Bill Hollinshead
> > > Microsoft, SQL Server
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
> > >
> >
> >
>
>

Sponsored Links