How many bytes can I fit on a row/page in SQL Server and why? Are there any workarounds?

A. Rows can never cross page boundaries - page size in 6.5 and earlier is 2K, in SQL 7.0 it is 8K.

For 6.5 and earlier :-

Each 2048 byte page has a 32 byte header leaving 2016 bytes for data. You can have 2 rows of 1008 bytes or 10 rows of 201 bytes. The row size also includes a few bytes of overhead in addition to the data itself; there is more overhead if there are variable length columns.

One row cannot be 2016 bytes - this is because when you insert/update/delete a row, the entire row must be written to the transaction log in a log record. Log pages also have 2016 available bytes per page, and need 50 bytes for transaction log specific information, so this gives a maximum size for a single row of 1962 bytes.

For SQL 7.0 :-

Each 8192 byte page has a 32 byte header leaving 8060 bytes for data. 

For all versions :-

You CAN define a table with a rowsize greater than this as long as it contains variable/NULL columns. However, if you try at any point to insert/update a row that has data that actually exceeds this limit then the operation will fail.

There are no workarounds for this. You will have to split the table into multiple tables if you need more data than a row can take.

For more details on page layouts see "Inside SQL Server 6.5" by Ron Soukup or "Inside SQL Server 7.0" by Kalen Delaney. Both highly recommended. Both by Microsoft Press.