A row is maximum 8060 bytes but there is row overhead included.
CREATE TABLE dbo.FullPage1 (
ID int NOT NULL,
filler1 char(8000) NOT NULL,
filler2 char(49) NOT NULL)
GO
If you change filler2 to char(50) it breaks with
Creating or altering table 'FullPage1' failed because the minimum row size would be 8061, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.
The 8060 is over all columns. There is no column overhead except the NULL bitmap (see later)
CREATE TABLE dbo.FullPage3 (
ID int NOT NULL,
filler1 char(2683) NOT NULL,
filler2 char(2683) NOT NULL,
filler3 char(2683) NOT NULL)
GO
Here, data is 8053 bytes with overhead of 2 bytes record type) + 2 bytes NULL bitmap pointer + 3 bytes NULL bitmap.
Even though we have no NULLable columns, the NULL bitmap exists. When we add a NULLable column, we don't increase the row size.
CREATE TABLE dbo.FullPage3 (
ID int NOT NULL,
filler1 char(8000) NOT NULL,
filler2 char(49) NOT NULL,
filler3 varchar(8000) NULL)
GO
This gives a warning of course
Warning: The table "FullPage3" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.
The NULL bitmap is "one bit per column". Not just NULLable columns. Uncomment filler6v and it fails with "... including 8 bytes of internal overhead ..."
CREATE TABLE dbo.FullPage4 (
ID int NOT NULL,
filler1 char(8000) NOT NULL,
filler2 char(49) NOT NULL,
filler1v varchar(10) NULL,
filler2v varchar(10) NULL,
filler3v varchar(10) NULL,
filler4v varchar(10) NULL,
--filler6v varchar(10) NULL,
filler5v varchar(10) NULL
)
GO
For more info about on-disk structures, see Inside the Storage Engine: Anatomy of a record. Interestingly, where is the 4 byte row header? This appears excluded from the 8060 bytes.
Tested on SQL Server 2008 SP1 and SQL Server 2005 SP3
Check out the general answer I gave to you on your other question.
If you are storing your XML as a LOB, then I guess I'd see what is the common average size per table. If the average size is 4MB or less (which it sounds like), then you'd only need a 4K tablespace (see Space requirements for table, table 1).
If you are storing the XML as an XML data type in DB2, then you actually get some performance gains over LOB, especially if you inline the XML (and aren't doing updates to it all that often). This may be the best since you mention sizes less than 32K. You may wish to check out XML storage page for in-lining XML within the table vs outside for XML storage.
Best Answer
From what I have read (check out Understanding DB2: Learning Visually with Examples), IBM recommends that you place data, indexes, and long data (meaning
LOBtype data as well as deprecated types likeLONG VARCHAR) into their own tablespaces.Main reason is for maintenance and support. If the data is in different tablespaces you can backup/restore based on your needs. For example, table data is "more" important than indexes (as indexes have no purpose without data in the tables). So if your indexes are in a separate tablespace and end up corrupted or needing a restore, you only have to restore the indexes, not the whole table (of course you may need to run reorgs and runstats to make sure your indexes are up to date).
Regarding LOB's - unless they are declared INLINE, they are not stored directly with the table data anyway (and
INLINEonly works for LOB's of a certain size). Hopefully you don't have too much update activity occuring on your LOB (as that can be a little costly seeing as LOB's are never cached in a bufferpool and always retreived from disk). So having them in another tablespace can help there as well. You have a separate space to store the data and if the table data is corrupted/needing a restore, but the LOB's are fine, again, you could restore just the table data without losing your LOBs.So if you think of IBM's recommendation. They would recommend you build your table as such:
As for size....I would recommend that you only put your tables and indexes and long/LOB data in the right sized page. If your indexes don't require a 32K tablespace, why put them there? It just wastes pages (a lot of space on the page will be ununsed) and thus waste disk. If you need to later move the indexes or the tables or the long/lob to a higher page size tablespace, it isn't too hard to do that later. (Of course you may need to have a maintenance window to do it in, but again, I think...even though disk is "cheap" so to say...why waste money?).
We used to put the tables in whatever size they needed and put the indexes only in a 4K unless they needed larger, figuring we could always change them later. But we recently have started to make sure the indexes are in the same tablespace size as the table data and LOB data. Reason being is you cannot change a tablespace after a table is created. To move the data you essentially have to create a new table and move all the data from one to the other.
Same goes for a few LOB's we have in other tables. I figured out their size and put them in an appropriately sized tablespace.
I have found the following pages on Space requirements for storage very helpful for understanding what sizes for tables and LOB's determine the required page size (and thus tablespace). The Space requirements for indexes link is helpful for indexes. Note: this is for DB2 9.7. If you need a different version, trying changing the number in the link, or start from IBM's main page.