Varchar datatype greater than 8000 bytes and row-overflow

I still see out in tables at various locations the use of varchar (8000) for some reason or another, so let's review what happens when someone specifies this in DDL. Run the following:

DROP TABLE LongString
go
CREATE TABLE LongString 
        (ID int IDENTITY NOT NULL PRIMARY KEY
        ,Filler varchar (8000)
        ,LongStr varchar (8000)
        )
GO

INSERT INTO LongString 
    (Filler
    ,LongStr
    )
SELECT 
    REPLICATE('Y',8000)
    ,REPLICATE ('X',8000)

First off, you notice that in SQL Server 2005/08, you don't get the old warning that you used to about data type length that you did back in SQL 2000:

Warning: The table 'LongString' has been created but its maximum row size (16029) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

The reason for this (beginning in SQL 2005) was that a concept of "Row-Overflow" was added to the SQL Engine. So all this means is that you can have more than one column of varchar (not char) greater than 8000 bytes. Suggestion - don't do it anyway. BOL states a number of reasons, including query performance and overhead. Update operations that change the length can invariable move to and from the "overflow" portion of the record, creating unnecessary IOs. It also states that the records are processes synchronously rather than asynchronously as well, which goes against how the database handles data as contrasted with transaction log processes.

So, if you query the table you get the following:

SELECT ID,DATALENGTH(LongStr), DATALENGTH(Filler)
FROM LongString

This all seems fine and dandy, but laziness at its finest and I personally do not recommended. In SQL Server you will have, and probably always will from here on out, 8kb pages. DB2 has them, and several other RDBMS do as well.  Chatting last month with a few guys from the storage engine team, this isn't going to change while you and I are using the product i.e. the foreseeable future. Although we get the value of overflow, it doesn't buy us much (other than getting by with a poorly-designed entity). Run the rest of the code and examine the output from working with varchar (8000).

UPDATE LongString
SET LongStr = LongStr + 'A'
WHERE ID=1

SELECT RIGHT(LongStr,1)
FROM LongString

 

UPDATE LongString 
SET Longstr = LEFT(Longstr,7999) + 'C' + 'd' 
WHERE ID=1 SELECT SUBSTRING (LongStr,8000,1) FROM LongString

--------------------------

Anyone for a 2^31 value varchar (max) column for a million rows or two in an OLTP high-transaction table?