If you're really interested in this subject, I recommend picking up a copy of Inside SQL Server 2000 by Delaney.
Here's the short of it (from Inside SQL Server 2000).
A "data row" has this basic layout:
Some reserved data (2 bytes)
Size of the fixed length data (2 bytes)
Fixed length data (fixed size - 4)
Number of columns (2 bytes)
bitmap indicating NULL columns (#cols/8)
Number of var columns (2 bytes)
Offset array (2 * var column cnt)
variable length data (??)
So to answer you question directly...
>Does a varchar put additional overhead on the dbms making it store an offset value for each row?
Yes, there is additional overhead in maintaining an offset array. Does it affect things? Most people agree that the benefit of being able to get more data on a page out weighs the overhead of maintaining the metadata in the row.
If you're curious, you can use DBCC PAGE() to a page in the database.
-Mike