Such is the case with any large forum web site, such as Lottery Post.  Each forum post — whether humongous or a couple of words — occupies just one row [record] in the database.  In fact, the entire content of a forum post is contained within just one cell [field].

The Lottery Post database was formerly stored using SQL Server 2000, before it was upgraded to SQL Server 2005 in November 2005.  At the time, the only way to store the forum posts was using a text data type, which was fairly inefficient.

A text data type stores all of its data — for the one column — in an external text file, and each row in the data table contains a pointer into the text file, to where the data for each row is stored.  Using that model, the performance of data updates can be handicapped by all the file I/O necessary to maintain the linkages and separate data files.

SQL Server 2005 introduced new data types for storing huge blocks of data: varchar(MAX), nvarchar(MAX), and varbinary(MAX).  Like the regular versions of these data types (using a number, 8,000 or less, to represent the maximum size instead of MAX), the new large value data types store the data in the data file itself, not an external file.  Thus, the new data type are far more efficient.

It turns out there are additional benefits to these new large value data types as well.  By accident, and because of my curiosity, I discovered that there have been modifications to the SQL UPDATE statement specifically for the new data types.

The new syntax allows the developer to modify a large value in the same way that the STUFF() function works.  That is, it can insert a character string directly into the current value, and optionally replace the number of characters you specify.  It's called a partial update.

This is a tremendous leap in efficienc....

[ More ]