MOSS 2007 Column Limitations

I recently had a meeting with a client where it was asked if SharePoint 2007 (MOSS) still suffered from the SharePoint 2003 limitations. I began testing and ran into no limitations. I stopped at 190 fields successfully. I also tested the 190 fields with content types, and adding and editing documents using the content type.

Background

In SharePoint 2003, the following limitations were well known (1):

  • 64 Single line of text and Choice (drop-down menu or radio buttons)
  • 31 Multiple lines of text and Choice (check boxes (allow multiple sections))
  • 32 Number and currency
  • 32 Hyperlink
  • 16 Date and time
  • 16 Lookup
  • 16 Yes/No
  • 8 Calculated

Findings

I found Microsoft literature specifying the following “guidelines for acceptable performance” for columns (2):

  • 2,000 per document library
  • 4,096 per list

“This is not a hard limit, but you might experience library and list view performance degradation as the number of columns in a document library or list increases.”

I found a blog article where Ishai Sagi tested the performance of creating 2000 text fields. Here is a summary of his findings:

Average time per fields as more fields created

I looked into SharePoint 2007 to figure out exactly how Microsoft overcame the limitations in SharePoint 2003. SharePoint has a table called “AllUserData” where they have the following fields:

  • 8 datetime (DateTime)
  • 12 float (Number, Percentage)
  • 16 int (Integer)
  • 16 bit (Boolean)
  • 32 ntext (Note)
  • 64 nvarchar (Text, Url, Choice)
  • 8 sql_variant

In a nutshell, if the limit of any of the column types is reached, SharePoint uses a second row to begin populating the data. The new row will have a field called rowOrdinal incremented to 1. Therefore, if the column once again exceed their limit, the rowOrdinal will be incremented to 2 and so on.

1 – To read more about the SharePoint 2003 limits: http://support.microsoft.com/default.aspx?scid=kb;en-us;823555

2 – To read more about the SharePoint 2007 (MOSS) limits: http://technet.microsoft.com/en-us/library/cc262787.aspx

3 – To read more about the performance testing of columns: http://www.sharepoint-tips.com/2006/06/sharepoint-2007-column-limits.html