Understanding Rows In SharePoint 2010
Understanding columns and rows in SharePoint 2010 is important when considering the holistic product. As you add columns to a list in SharePoint 2010 they get mapped to columns in the SQL Server database table. The rows in the database table supports a fixed number of column types. With a single database table row you can support eight date and time columns and then twelve number columns. When you have more than eight date and time columns then you need to use two database table rows.
With small lists the performance of row wrapping can be defective. When you use large lists though this will have a major effect for you. The limit for any number of columns before you use row wrapping does occur. However, that will only be the case if one column type goes over the limit. The number of columns for different data types without row wrapping happening is:
- Single line of text 64 columns
- Choice & multiple lines of text – 32
- Date & time 8 columns
- Yes/No 16 columns
- Number and Currency 12 columns
- Calculated 8 columns
- Single value look up 16 columns
- Unique identifier 1 column
With row wrapping you get to decrease the throughput by ~35% (give or take) for each additional row with most of the operations. Find out how many rows for a list you need by analyzing the line schema. You can also examine the column types for the different fields on the list.
Columns can be added to a list into columns with the Microsoft SQL Server database table. In order to support a large number of columns the SharePoint allows multiple rows to store the data. For a small list the performance of row wrapping may not work as well but it is excellent for larger sized lists. The performance is, as mentioned, about 35% per additional row. However, it is a good idea with the large lists to avoid wrapping more than one or two additional rows.
The size limits of the rows will be specified depending on the maximum number to table rows for the internal database. This applies to each of the items in a list so that they can be accommodated. This requires several columns with can being wrapped in as many as six internal table rows. When you have a list with many small columns then you can add more of them if you need to. This means that additional rows for larger lists can be done in order to minimize the number of table rows for the database used for each item in a list.