MSSQL varchar vs. text

MSSQL varchar vs. text

am 07.12.2005 09:04:14 von Kimmo Laine

Hello group!

What's the essential difference between the two field types varchar and
text. I've used varchar to store strings so far, but it seems that there's a
limit of getting only the first 255 characters with a query in php. And so
far I've learned that there are work-arounds for this, but work-arounds are
always ugly so I though why not use the text then, since I learned that text
datatype has no such limitation as varchar. I've estimated that the length
of the strings would vary between 50-1000 characters so would using a text
datatype be reasonable in this case, or would a varchar with ugly
work-arounds be better in this case?

--
"En ole paha ihminen, mutta omenat ovat elinkeinoni." -Perttu Sirviö
antaatulla.sikanautaa@gmail.com.NOSPAM.invalid

Re: MSSQL varchar vs. text

am 07.12.2005 10:23:39 von Steve

> What's the essential difference between the two field types varchar and text.

I guess this refers to this and the user notes by
"marc at NOSPAM dot durdin dot net" here: .
The workaround mentioned is:

-- for example, with MyVarCharField VARCHAR(1000)
SELECT CAST(MyVarCharField AS TEXT) FROM MyTable

>From the SQL Server BOL for MSSQL2000:

"...character data is stored using the char, varchar, and text data
types. Use varchar when the entries in a column vary in the number of
characters they contain, but the length of any entry does not exceeds 8
kilobytes (KB). Use char when every entry for a column has the same
fixed length (up to 8 KB). Columns of text data can be used to store
ASCII characters longer than 8 KB."

There will definitely be a performance hit when using TEXT instead of
CHAR or VARCHAR. You'll have to make some tests to see if you could
live with that.

---
Steve

Re: MSSQL varchar vs. text

am 07.12.2005 11:03:04 von nc

Kimmo Laine wrote:
>
> What's the essential difference between the two field types
> varchar and text.

VARCHAR will store the string as is. VARCHAR fields in different
records will be of different length.

TEXT will always right-pad the string to fill all available space.
TEXT fields in different records will be of the same length.

Cheers,
NC

Re: MSSQL varchar vs. text

am 07.12.2005 11:40:14 von Kimmo Laine

"Steve" wrote in message
news:1133947419.069827.74850@z14g2000cwz.googlegroups.com...
>
>> What's the essential difference between the two field types varchar and
>> text.
>
> I guess this refers to this and the user notes by
> "marc at NOSPAM dot durdin dot net" here: .
> The workaround mentioned is:
>
> -- for example, with MyVarCharField VARCHAR(1000)
> SELECT CAST(MyVarCharField AS TEXT) FROM MyTable

Yep, as I said, it gets ugly. I'd like to keep my queries as close to
"SELECT * FROM table" as I can. If I have to write each long varchar field
separately using CAST, then I'm just gonna use the text datatype.

> There will definitely be a performance hit when using TEXT instead of
> CHAR or VARCHAR. You'll have to make some tests to see if you could
> live with that.

That's what I was afraid of. I guess I just have to do like you said and do
some performance tests and see how it affects.

Thanks for replying, both you and NC

--
"En ole paha ihminen, mutta omenat ovat elinkeinoni." -Perttu Sirviö
antaatulla.sikanautaa@gmail.com.NOSPAM.invalid