I have been reading some sites, where they talk about the behavior of
I read that:
a varchar (or the other data types that i told above) can not be, in size , bigger that 8000 bytes.
But if they are combined (two or more) of this columns in a table, they can exced the 8060 bytes that a data page can support .
In this case SQL Server 2005 puts a pointer of 24 bytes in the original page where the row is stored (IN_ROW_DATA) and puts the value of the column or comumns, that are bigger in a place called ROW_OVER_FLOW_DATA (i don't know where this is stored inside SQL Server).
then, when a update is executed to this row, if new data is smaller(the row is equal or small that 8060 bytes) then the the data on the ROW_OVER_FLOW_DATA is back to the original data page of the row.
SQL Server does this dymanic.
My questions are:
Where is ROW_OVER_FLOW_DATA inside SQL Server storage? is this a collection of datapages (always the same), that are only dedicated to this? what is the size of this in a database?
Or this is a funcionality that allocate datapages when it needs?
what about large value ( Varchar (MAX),Nvarchar(MAX) and VARBINARY(MAX))?
how are they stored? it's in the same way as this types abouve?