CODEWITHSUNDEEP
sql-server-ce
David Ly
David Ly

Reputation: 31586

What is the best way to persist large strings in a database? (SQL Compact)

The string in question would be the description field of a (cooking) recipe, and the max length should be something that 99% of users should never run into. nvarchar(4000) seems like it's probably too limiting.

Is a column in a SQL table even the appropriate place for this? It doesn't feel right for storing such a (potentially) large value in a field like this, but maybe not?

Not sure it matters, but .NET 3.5 most likely going to use LINQ2SQL.

Edit: Using the VS Express Database Explorer to create the tables, it's telling me that 4000 is the max size for nvarchar (doesn't seem to have varchar listed as an option). Is this just a limitation of SQLCE and an indication that I'll have to look into something else?

If it's true that this is a limitation of SQLCE does anyone have another recommendation? For a pet project, I'd have to be something free and preferably easy to setup (preferably both for me and the end-user, but more important that it's easy to setup for the end-user). The database will be local, and performance isn't too much of a concern.

Upvotes: 4

Views: 4582

Answers (6)

Jake
Jake

Reputation: 733

ntext is your best bet as it can store half a million characters, if that's too small this could be combined with other solutions such as splitting up the row could be used.

https://technet.microsoft.com/en-us/library/ms172424.aspx

You should keep in mind that there's a max database size of 4GB with SQL compact, so migrating to a different database type might be preferable if you intend on storing a lot of these records.

Upvotes: 0

RossFabricant
RossFabricant

Reputation: 12492

An alternative approach is to save the text as a file, and the database stores only the file name.

Upvotes: 0

Mike Woodhouse
Mike Woodhouse

Reputation: 52316

Not necessarily recommended, but provided because they sprang to mind:

If the text is very rarely altered once stored, you might consider creating a new table that stores "lines" of text, something like:

recipe_id integer,
line_number integer,
line_text nvarchar(80)

Alternatively, if you don't need to search the text of the recipe, how about a simple compression algorithm? Huffman encoding is fairly effective on text and not horribly CPU-intensive.

Upvotes: 1

Gary.Ray
Gary.Ray

Reputation: 6501

Have you done any studies on existing recipes? A varchar(4000) would give you around 400-500 words and I am pretty sure not many of the recipes in the many cookbooks I have has a description longer than that.

VarBinary would get you 8000 bytes, but if you are going to be doing any searching in the description field using varbinary could require casts or other operations that will incur a performance hit.

Finally, while I don't particularly like this, you could normalize descriptions into a different table which would allow you to set a one-to-many relationship and enable a recipe to have more than one description part which you would reassemble in the interface.

Upvotes: 5

HardCode
HardCode

Reputation: 6756

I never used SQL CE, but see if it supports the VARCHAR(MAX) data size. Basically, it stores large amounts of text (up to 2GB) outside the scope of the 800K row size limit, but also lets you use ' = ' and other WHERE clause operators (the TEXT data type only supported LIKE).

Upvotes: -1

Don Werve
Don Werve

Reputation: 5120

Most SQL databases are smart enough to do this automatically for large VARCHARS and for TEXT columns. Rather than allocating space for a large column when the row is created, the data for each row is stored in such a fashion so that it only takes up slightly more space than the actual contents (rather than the maximum size).

Upvotes: -1

Related Questions