Column limits
SharePoint Server 2010 data is stored in SQL Server tables. To allow for the maximum number of possible columns in a SharePoint list, SharePoint Server will create several rows in the database when data will not fit on a single row. This is called row wrapping.
Each time that a row is wrapped in SQL Server, an additional query load is put on the server when that item is queried because a SQL join must be included in the query. To prevent too much load, by default a maximum of six SQL Server rows are allowed for a SharePoint item. This limit leads to a particular limitation on the number of columns of each type that can be included in a SharePoint list. The following table describes the limits for each column type.
The row wrapping parameter can be increased beyond six, but this may result in too much load on the server. Performance testing is recommended before exceeding this limit. For more information, see the “Designing Large Lists and Maximizing List Performance” white paper that can be accessed from Performance and capacity test results and recommendations (SharePoint Server 2010).
Each column type has a size value listed in bytes. The sum of all columns in a SharePoint list cannot exceed 8,000 bytes. Depending on column usage, users can reach the 8,000 byte limitation before reaching the six-row row wrapping limitation.
| Limit | Maximum value | Limit type | Size per column | Notes |
| Single line of text | 276 | Threshold | 28 bytes | SQL Server row wrapping occurs after each 64 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 384 Single line of text columns per SharePoint list (6 * 64 = 384). However, because the limit per SharePoint list item is 8000 bytes, of which 256 bytes are reserved for built-in SharePoint columns, the actual limit is 276 Single line of text columns. |
| Multiple Lines of Text | 192 | Threshold | 28 bytes | SQL Server row wrapping occurs after each 32 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 192 Multiple lines of text columns per SharePoint list (6 * 32 = 192). |
| Choice | 276 | Threshold | 28 bytes | SQL Server row wrapping occurs after each 64 columns in a SharePoint list. The default row wrapping value of 6 allows for a maximum of 384 Choice columns per SharePoint list (6 * 64 = 384); ); however because the limit per SharePoint list item is 8000 bytes, of which 256 bytes are reserved for built-in SharePoint columns, the actual limit should be 276 Single line of text columns. |
| Number | 72 | Threshold | 12 bytes | SQL Server row wrapping occurs after each 12 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 72 Number columns per SharePoint list (6 * 12 = 72). |
| Currency | 72 | Threshold | 12 bytes | SQL Server row wrapping occurs after each 12 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 72 Currency columns per SharePoint list (6 * 12 = 72). |
| Date and Time | 48 | Threshold | 12 bytes | SQL Server row wrapping occurs after each eight columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 48 Date and Time columns per SharePoint list (6 * 8 = 48). |
| Lookup | 96 | Threshold | 4 bytes | SQL Server row wrapping occurs after each 16 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 96 single value Lookup columns per SharePoint list (6 * 16 = 96). |
| Yes / No | 96 | Threshold | 5 bytes | SQL Server row wrapping occurs after each 16 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 96 Yes / No columns per SharePoint list (6 * 16 = 96). |
| Person or group | 96 | Threshold | 4 bytes | SQL Server row wrapping occurs after each 16 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 96 Person or Group columns per SharePoint list (6 * 16 = 96). |
| Hyperlink or picture | 138 | Threshold | 56 bytes | SQL Server row wrapping occurs after each 32 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 192 Hyperlink or Picture columns per SharePoint list (6 * 32 = 192) ); however because the limit per SharePoint list item is 8000 bytes, of which 256 bytes are reserved for built-in SharePoint columns, the actual limit should be 138 Hyperlink or Picture columns. |
| Calculated | 48 | Threshold | 28 bytes | SQL Server row wrapping occurs after each eight columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 48 Calculated columns per SharePoint list (6 * 8 = 48). |
| GUID | 6 | Threshold | 20 bytes | SQL Server row wrapping occurs after each column in a SharePoint list. The default row wrapping value of six allows for a maximum of 6 GUID columns per SharePoint list (6 * 1 = 6). |
| Int | 96 | Threshold | 4 bytes | SQL Server row wrapping occurs after each 16 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 96 Int columns per SharePoint list (6 * 16 = 96). |
| Managed metadata | 94 | Threshold | 40 bytes for the first, 32 bytes for each subsequent | The first Managed Metadata field added to a list is allocated four columns:
Each subsequent Managed Metadata field added to a list adds two more columns:
The maximum number of columns of Managed Metadata is calculated as (14 + (16 * (n-1))) where n is the row mapping value (default of 6). |
External Data columns have the concept of a primary column and secondary columns. When you add an external data column, you can select some secondary fields of the external content type that you want to be added to the list. For example, given an External Content Type “Customer” which has fields like “ID”, “Name”, “Country”, and “Description”, when you add an External Data column of type “Customer” to a list, you can add secondary fields to show the “ID”, “Name” and “Description” of the Customer.
Overall these are the columns that get added:
Primary column: A text field.
Hidden Id column: A multi-line text field.
Secondary columns: Each secondary column is a text/number/Boolean/multi-line text that is based on the data type of the secondary column as defined in the Business Data Catalog model. For example, ID might be mapped to a Number column; Name might be mapped to a Single line of text column; Description might be mapped to a Multiple lines of text column.
PDF Pages: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
Tags: 
One Response
[...] Know Your Limits: Capacity Planning in SharePoint 2010 | Concurrency Blog (tags: sharepoint architecture sql) Like this:LikeBe the first to like this post. [...]