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.

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.