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.
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.