Fixing issues with "Column Limit Exceeded"

If any of you have ever got this error when trying to create a custom list in WSS 3.0, you can understand the frustration and lack of clear documentation (like most of Sharepoint anyway).

If you look for details about the number of fields, you are brought to this page (http://support.microsoft.com/kb/823555 or http://msdn2.microsoft.com/en-us/library/ms998711.aspx) which states limits that are really not applicable to WSS 3.0, making things even more frustrating

Now after finding that columns could be added without error manually from the interface and reading that version 3.0 was supposed to have remove the column limits, I went digging and found out that the new table that holds list data, AllUserData, is actually partitioning data in multiple records when the number of columns exceed what a single record allows.

Here are the limit of a single record in AllUserData:

• nvarchar1 to nvarchar64
• ntext1 to ntext32
• int1 to int16
• float1 to float12
• datetime1 to datetime8
• bit1 to bit16
• sql_variant1 to sql_variant8

Now if you try to create a custom column that has a colName that doesn't fall within those values you will get the dreaded "Column Limit exceeded" error.

If you look at a field definition like this:

Field ID="{27CB1283-BDA2-4ae8-BCFF-71725B674DBB}" Name="CompanyNumber" DisplayName="Company Main Phone" Group="Core Contact and Calendar Columns" Type="Text" Sealed="TRUE" AllowDeletion="TRUE" Customization="" SourceID="{6412e43c-2a64-47cc-adff-38430c8ea370}" StaticName="CompanyNumber" ColName="nvarchar6" RowOrdinal="0"

you will notice that ColName is followed by the innocent looking RowOrdinal attribute which is documented nowhere and that people seem convinced is always zero.

Well this RowOrdinal value is the key to breaking the column limit barrier. When you have fields of type that exceeds the limit imposed by the table, you simply increase the RowOrdinal by 1 and you now have access to a completely new row with room for another bunch of fields. In theory you should be able to keep increasing this value as long as needed in order to save all of your fields.

Here is an example using RowOrdinal of 1, which happens to be the 9th datetime field in one of my list:

Field Type="DateTime" DisplayName="Submitted Date" Required="FALSE" Format="DateOnly" ID="{db42c6d6-4076-4bc9-85e8-76cc1c9dee6d}" SourceID="{1fe23d74-6481-42eb-b2e3-3d4b10a6eb74}" StaticName="SubmittedDate" Name="SubmittedDate" CalType="0" Version="2" Customization="" ColName="datetime1" RowOrdinal="1"

Now let's go and have fun creating those huge lists :)