Truncating text field 2

[ksbigfoot]

I am using SQL Server 8.
I have a text field that could range from 100 characters to 2000 characters.
I am not sure on what to set my field type as?
Could someone point me in the right direction.
Thanks

 

[mrdenny]

If you do not need to store unicode data (Russian, Chineese, etc) then VARCHAR(2000) will be what you want to use. If you need need to store unicode then NVARCHAR(2000) will be what you want to use.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ptheriault]

Use Varchar. It can have up to 8000 Characters and does not have the limitations of the text datatype.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ksbigfoot]

Howdy Mr.Denny,
Thanks for the post.
I don't have any unicode characters.
I originally had Text 16, but I wasn't sure.
Thanks for clearing this up for me. Star to you.
Thanks again,
ksbigfoot

 

[ksbigfoot]

Howdy Paul,
If I use varchar, doesn't that force the field to be 8000 characters long?
Thanks

 

[ptheriault]

No. If you just use varchar with out a size it defaults to 32. You have to specify a size like Denny did in his post.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ksbigfoot]

Howdy Paul,

Does 32 mean 2 to the power of 32?
If Yes, does that mean it is always that size and doesn't shrink to the size of the field?
The table I am using only gets inserted into, no one does selections from this table. Its only purpose is to record whenever someone does an Insert, Update or Delete out of our system.

I am not sure, but I assumed that the field shrinking was better for maintaining the database size.

Thanks again,
ksbigfoot

 

[ptheriault]

No it means 32 Characters. If you specify a Varchar(2000) and you store 1 Character in it. Then the actual size of you field is 1 byte not 2000.

# Use varchar/nvarchar columns instead of text/ntext columns whenever possible. Because SQL Server stores text/ntext columns on the Text/Image pages separately from the other data, stored on the Data pages, it can take more time to get the text/ntext values.
# Use char/varchar columns instead of nchar/nvarchar if you do not need to store unicode data. The char/varchar value uses only one byte to store one character, the nchar/nvarchar value uses two bytes to store one character, so the char/varchar columns use two times less space to store data in comparison with nchar/nvarchar columns.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ksbigfoot]

Howdy Paul,

Star to you.
I didn't know that is how the field types work.
Thanks again for all your input. It is clearer now for me.

ksbigfoot

 

[ptheriault]

no problem,
I'm glad I could help.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

All of the datatypes which start with VAR are variable length so they only store the actual amount of data that you put in the field (plus a few of bytes for header information such as the length of the field). If you use CHAR, or NCHAR they are fixed length fields. So if you define a CHAR field as 1000 bytes it will always use 1000 bytes of disk space.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ksbigfoot]

I made the changes and now I am testing.
When I am in Query Analyzer, and I run my query either in Results in Text or Results in Grid, it displays the data as 256 characters long.
Is there some sort of setting that I could change to show all the data?

Because I opened Enterprise Manager and selected top 10 rows and I can see all the data in the field, so I know it is there.
Thanks

 

[ptheriault]

That's a setting in QA.

Go to tools -> Options -> Query Results and change the value of the Max number of Characters to display in each column.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ksbigfoot]

Howdy Paul,
Very nice, Star to you!!
Works perfectly.

Thanks again,
ksbigfoot