varchar vs text

[Michael Shutt]

I have read conflicting information from several sources about when it makes
sense to use the text data type vs the varchar data type and I was hoping
someone here could confirm my understanding of how these two data types
work. From what I think I know, the basic difference is that the contents
of a varchar field are stored with each record, where as the contents of a
text field are stored separately and the record only contains a pointer to
the actual data. My rules of thumb as far as when to use each type:

Use varchar if:

1.) You need to query on the text field
Or
2.) The content of the field is relatively small (<50 characters), negating
the advantages of use a pointer.
Or
3.) The content of the field is larger but is retrieved often (its faster to
retrieve it if the data is stored in the record).

Use text if:

1.) You don't need to query on the text field
And
2.) The data stored is relatively large (>50 characters).
And
3.) The contents of the text field are rarely retrieved compared to the
number of times the table is queried.

MJS

[Daniel Reber]

What version of sql server are you using?

For SQL 2k I only use text datatype if I want to store more than 8000
characters.

Daniel Reber
Datamasters, Inc

"Michael Shutt" <mshutt@advectis.com> wrote in message
news:3d7601f5$1@10.1.10.29...
> I have read conflicting information from several sources about when it
makes
> sense to use the text data type vs the varchar data type and I was hoping
> someone here could confirm my understanding of how these two data types
> work. From what I think I know, the basic difference is that the contents
> of a varchar field are stored with each record, where as the contents of a
> text field are stored separately and the record only contains a pointer to
> the actual data. My rules of thumb as far as when to use each type:
>
> Use varchar if:
>
> 1.) You need to query on the text field
> Or
> 2.) The content of the field is relatively small (<50 characters),
negating
> the advantages of use a pointer.
> Or
> 3.) The content of the field is larger but is retrieved often (its faster
to
> retrieve it if the data is stored in the record).
>
> Use text if:
>
> 1.) You don't need to query on the text field
> And
> 2.) The data stored is relatively large (>50 characters).
> And
> 3.) The contents of the text field are rarely retrieved compared to the
> number of times the table is queried.
>
> MJS
>
>

[Michael Shutt]

I am using v7 right now but I would hope the same principles apply in both
v7 and 2000.

"Daniel Reber" <dreber@dminconline.com> wrote in message
news:3d760f39$1@10.1.10.29...
> What version of sql server are you using?
>
> For SQL 2k I only use text datatype if I want to store more than 8000
> characters.
>
> Daniel Reber
> Datamasters, Inc
>
> "Michael Shutt" <mshutt@advectis.com> wrote in message
> news:3d7601f5$1@10.1.10.29...
> > I have read conflicting information from several sources about when it
> makes
> > sense to use the text data type vs the varchar data type and I was
hoping
> > someone here could confirm my understanding of how these two data types
> > work. From what I think I know, the basic difference is that the
contents
> > of a varchar field are stored with each record, where as the contents of
a
> > text field are stored separately and the record only contains a pointer
to
> > the actual data. My rules of thumb as far as when to use each type:
> >
> > Use varchar if:
> >
> > 1.) You need to query on the text field
> > Or
> > 2.) The content of the field is relatively small (<50 characters),
> negating
> > the advantages of use a pointer.
> > Or
> > 3.) The content of the field is larger but is retrieved often (its
faster
> to
> > retrieve it if the data is stored in the record).
> >
> > Use text if:
> >
> > 1.) You don't need to query on the text field
> > And
> > 2.) The data stored is relatively large (>50 characters).
> > And
> > 3.) The contents of the text field are rarely retrieved compared to the
> > number of times the table is queried.
> >
> > MJS
> >
> >
>
>

[Daniel Reber]

yes, SQL 7 can also have a length of up to 8000. I wasn't 100% sure but I
just tested it.

Daniel Reber
Datamasters, Inc


"Michael Shutt" <mshutt@advectis.com> wrote in message
news:3d76369a$1@10.1.10.29...
> I am using v7 right now but I would hope the same principles apply in both
> v7 and 2000.
>
> "Daniel Reber" <dreber@dminconline.com> wrote in message
> news:3d760f39$1@10.1.10.29...
> > What version of sql server are you using?
> >
> > For SQL 2k I only use text datatype if I want to store more than 8000
> > characters.
> >
> > Daniel Reber
> > Datamasters, Inc
> >
> > "Michael Shutt" <mshutt@advectis.com> wrote in message
> > news:3d7601f5$1@10.1.10.29...
> > > I have read conflicting information from several sources about when it
> > makes
> > > sense to use the text data type vs the varchar data type and I was
> hoping
> > > someone here could confirm my understanding of how these two data
types
> > > work. From what I think I know, the basic difference is that the
> contents
> > > of a varchar field are stored with each record, where as the contents
of
> a
> > > text field are stored separately and the record only contains a
pointer
> to
> > > the actual data. My rules of thumb as far as when to use each type:
> > >
> > > Use varchar if:
> > >
> > > 1.) You need to query on the text field
> > > Or
> > > 2.) The content of the field is relatively small (<50 characters),
> > negating
> > > the advantages of use a pointer.
> > > Or
> > > 3.) The content of the field is larger but is retrieved often (its
> faster
> > to
> > > retrieve it if the data is stored in the record).
> > >
> > > Use text if:
> > >
> > > 1.) You don't need to query on the text field
> > > And
> > > 2.) The data stored is relatively large (>50 characters).
> > > And
> > > 3.) The contents of the text field are rarely retrieved compared to
the
> > > number of times the table is queried.
> > >
> > > MJS
> > >
> > >
> >
> >
>
>