SharePoint 2010: SQL limitations in lookup columns

This post is a contribution from Balaji Sadasivan, an engineer with the SharePoint Developer Support team.

As most of you are already aware SharePoint stores both the person/group field and the metadata fields as lookup values internally.

SharePoint dynamically creates a stored procedure to store the lookup values in SQL.  The stored procedure accepts a number of parameters from SharePoint to perform these operations.  And when the number of parameters exceeds 2100, it will throw an error.  This limitation of SQL is documented in this TechNet article Maximum Capacity Specifications for SQL Server.

So now the question arises as to how many lookup values can we add before we hit this limitation?

Here’s what my testing so far shows.  By no means is this an authoritative or an official data.

Field Type	Approximate Limit
Managed metadata lookup	250
Users lookup	330
Text/Numeric lookups	510

Now we need to note that the SQL limitation is for a list item and not for individual fields.  This means that if you have 2 metadata columns that total values present in both the columns combined cannot exceed approximately 250 values.  This becomes exceedingly complicated when you have multiple types of lookups grouped together and hence you must exercise caution considering these limitations.

Comments

• Anonymous
  October 08, 2013
  Have you considered normalizing the columns instead of having collections of columns named after the data types they store. Seems like the columns that store the multiple values are stored as delimited strings as well.  Those could be constructed in the object model and passed to SQL as one parameter instead of doing the concatenation with tsql.