CODEWITHSUNDEEP
sql-server-2005types
Stuart Helwig
Stuart Helwig

Reputation: 9454

What is the best datatype to use for storing moderate amounts of text in SQL Server (2005)?

What is the best datatype to use for storing moderate amounts of text in SQL Server (2005)?

For example, imagine a table, storing information on downloads available on a website. I want a title for the download which is brief, say varchar(20). A path, say varchar(255) then I want to store some user friendly text about the download - a description. In some cases it could be as short as the title, other times you may need a few of paragraphs of explanation.

What's best? A varchar with a size of say 4000, or a varchar(max) or something else?

Upvotes: 1

Views: 332

Answers (3)

HLGEM
HLGEM

Reputation: 96542

Size should be dependant on the size of the data being stored. Don't use varchar (max) for a state field with only 2 characters. I usually look at the largest value I expect to get and then add a fudge factor of 10-20% when determining the size. Don't forget to use nvarchar if you need to store unicode characters.

Upvotes: 0

Chris Roland
Chris Roland

Reputation: 877

The PAD (Portable Application Description) specification highest character limit for program description is 2000 chars *see Program Descriptions section.

http://www.asp-shareware.org/pad/spec/spec.php

Download site submission tools use this spec to make it easier for authors to submit their software. Also I think there are a couple lists you can download that follow the PAD spec.

So I would suggest varchar(2000) based on that fact I wouldn't want to store more than I need to. One record would be fine with varchar(max), but when you have thousands the cost of storage might go up.

Edit: varchar(max) can be 2GB - 1, that's why I would limit it. http://msdn.microsoft.com/en-us/library/ms176089(SQL.90).aspx

Upvotes: 4

usman shaheen
usman shaheen

Reputation: 3766

use varchar(max), you can put a huge text in it and its storage size increases as required. you can have sql group functions like having, group by on varchar(max). or you can go for text data type if looking for full text search in SQL

Upvotes: 2

Related Questions