Splitting SQL tables


Source: SM HELP SERVER

The HP Service Manager server reads the row size limitation and column number limitation from file “sqldbinfo” and uses the information when making a decision about whether to split SQL tables or not. This behavior applies to all supported database servers.

Caution: Modifications to the “sqldbinfo” table require a restart of the Service Manager server.

Constraints when disabling the row size limitation on SQL Server

If you want to disable the row size limitation on SQL Server, you need to consider the following constraints:

The feature of Row-Overflow Data Exceeding 8 KB is not supported by SQL SERVER 2005. For this reason, if you are using SQL SERVER 2005, do not change the default MAX ROW SIZE value in the “sqldbinfo” table.
The length of individual columns must still fall within the limit of 8,000 bytes for varchar, nvarchar, varbinary, sql_variant, and CLR user-defined type columns. Only their combined lengths can exceed the 8,060-byte row limit of a table.
The sum of other data type columns, including char and nchar data, must fall within the 8,060-byte row limit. Large object data is also exempt from the 8,060-byte row limit.
After removing the row size limitation, if a file does have an M2 table, before you manually move all of the fields in M2 into M1, newly added fields will still be put into M2.
If you want to disable table splitting, you need to manually set the row size limitation in the “sqldbinfo” table to a very large number, which is supported by SQLSERVER 2008 and later with ROW_OVERFLOW_DATA. The Row-Overflow feature is great for allowing occasionally rows longer than 8,060 bytes, but it is not well suited for the majority of rows being over-sized, which can lead to a drop in query performance.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s