What is the best way to save large rows in a database? (SQL Compact)

Question

This line will be the recipe (cooking) description field, and the maximum length should be something that 99% of users should not encounter. nvarchar (4000) seems like it is probably too restrictive.

Is the SQL table column the right place for this? He does not consider it appropriate to store such a (potentially) significant value in such a field, but perhaps not?

Not sure if this is important, but .NET 3.5 is likely to use LINQ2SQL.

Edit: using VS Express Database Explorer to create tables, this tells me that 4000 is the maximum size for nvarchar (it looks like varchar is not specified as an option). Is it just a limitation of SQLCE and an indication that I will have to look at something else?

If it is true that this is an SQLCE limitation, does anyone have any other recommendation? For a pet project, I have to be something free and, preferably, easy to set up (preferably both for me and the end user, but more important to make it easy to configure for the end user). The database will be local and performance is not too important.

Answer 1

Have you done any research on existing recipes? Varchar (4000) will give you about 400-500 words, and I am sure that not many recipes in many cookbooks that I have have a longer description.

VarBinary will provide you with 8000 bytes, but if you intend to do any search in the description field using varbinary, you may need to drop or other operations that could lead to performance degradation.

Finally, although I don’t particularly like it, you can normalize the descriptions to another table, which will allow you to establish a one-to-many relationship and include the recipe to have more than one part of the description that you reassemble in the interface.

Answer 2

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

If the text rarely changes after saving, you might consider creating a new table in which the "rows" of text are stored, for example:

recipe_id integer, line_number integer, line_text nvarchar(80) 

Alternatively, if you don’t need to look for the recipe text, what about a simple compression algorithm? Huffman's encoding is pretty efficient in text, not terribly intense in CPU.

Answer 3

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

Answer 4

ntext is your best bet as it can store half a million characters, if it is too small it can be combined with other solutions such as line splitting can be used.

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

Keep in mind that the maximum database size is 4 GB with an SQL CD, so moving to a different type of database may be preferable if you plan to store a lot of these records.

Answer 5

Most SQL databases are smart enough to do this automatically for large VARCHARS columns and for TEXT. Instead of allocating space for a large column when creating a row, the data for each row is stored so that it takes up a little more space than the actual content (rather than the maximum size).

Answer 6

I have never used SQL CE, but I see if it supports VARCHAR (MAX) data size. Basically, it stores a large amount of text (up to 2 GB) outside the 800 Kbyte line size range, but also allows the use of "=" clauses and other WHERE clauses (the TEXT data type is supported only by LIKE).