I have a system that basically stores a database within a database (I'm
sure lots have you have done this before in some form or another).

At the end of the day, I'm storing the actual data generically in a
column of type nvarchar(4000), but I want to add support for unlimited
text. I want to do this in a smart fashion. Right now I am leaning
towards putting 2 nullable Value fields:

ValueLong ntext nullable
ValueShort nvarchar(4000) nullable

and dynamically storing the info in one or the other depending on the
size. ASP.NET does this exact very thing in it's Session State model;
look at the ASPStateTempSessions table. This table has both a
SessionItemShort of type varbinary (7000) and a SessionItemLong of type
Image.

My question is, is it better to user varbinary (7000) and Image? I'm
thinking maybe I should go down this path, simply because ASP.NET does,
but I don't really know why. Does anyone know what would be the benifit
of using varbinary and Image datatypes? If it's just to allow saving of
binary data, then I don't really need that right now (and I don't think
ASP.NET does either). Are there any other reasons?

thanks,
dave