Storing stories in sql server 2008?

Question

I am going to store stories in nvarchar(MAX) fields in SQL Server, but I know the stories will be much longer than MAX allows, so what approach should I take? Should I split the story across multiple rows or should I skip using a database and use text files?

Answer 1

I believe the confusion stems from a misunderstanding of terms here.

nvarchar(n) is a data type where n can be a number from 1-4000. The number n in this case has a max of 4000, which adds up to 8000 bytes (2 bytes per character).

nvarchar(MAX) is a different data type altogether - the keyword MAX is a literal, and it is not a synonym for any potential value of n in my example above. Fields of this type have a maximum length of 2^31-1 characters, or over 1 billion, which adds up to over 2 billion bytes (2 bytes per character).

The same principles apply to varchar(n) and varchar(MAX), except each character may only be 1 byte, in which case the number of characters that can be stored is double. Whether it is only 1 byte depends on the collation, as Martin Smith notes in a comment!

Answer 2

Store them in chapters.

This is not technical - it is pretty much impossible to have astory of 1 billion nvarchar characters (and nvarchar(max) is the "new" TEXT data type.

BUt loading and processing them will be painfull.

Store them as chapters and store a start / end page number for every chapter when it makes sense, so you can navigate a little easier.

Btw., you posted you thought it is 800 chars - that was NEVER trhe case. The limit would be 8000 bytes - if it would apply - and that would be 4000 chars unicode.