SharePoint is a framework. As such, it’s generic. Many times you need a site to be stretched to fulfill a specific mission – and in many cases, this involves large lists with a high quantity of columns. You really have to keep an eye on the site, and the SQL server behind your farm.
Large lists are defined within a spectrum that includes record counts of higher than 2,000 items. Although, a list with 2,000 items can have its own set of issues….
I have supported lists with over 2,000,000 items – clearly not recommended – but many times you inherit a farm that you need to re-architect while keeping the business running.
Here’s some very important tips:
- Never use calculated columns – they will bring queries to their knees. They also effectively kill the sustainability of InfoPath forms. If you have a large list and need to make a change to an InfoPath form, calculated fields will cause the publishing process to time out. You will be stuck with the form and won’t be able to make changes to it (I’ve observed this at about the 10,000+ list item level).
- Lookup fields – at some point a large list will truly become too large and you will need to re-architect it. If you have been using lookup fields, you will find it very difficult to “re-wire” the list. It also can have very detrimental impacts to SQL server performance if you have instituted too many.
- Strictly monitor and restrict who can create views. I use a PowerShell script to frequently inventory the views on my lists – some lists have hundreds of views – it may only take one view to bring the whole list crashing down.
- Multi-Line text fields configured to append – this provides an excellent business tool and always answers a business requirement – however, they become an administration nightmare. It starts out providing great auditing, interaction, item history, etc., but when the list really becomes too large, or users want more transparency into that data – SharePoint doesn’t easily expose the versioned-appended fields. The business users will also insist that you then cannot limit the number of versions as this will delete their appended text data. Now you have multiplied the problem by having a large list with millions of versions as well (each list item, in effect, becomes a list! Now you have lists inside of lists!).
- Attachments – if you anticipate a list is going to grow – disable attachments. Plenty of PowerShell scripts can help administer attachments, but from experience, it’s difficult to sustain. Users may be expecting the kind of version control, metadata, and historical details from attachments that they expect from document libraries – but they will be disappointed.
- When possible, even avoid using People fields – if views filtered by user, or assignments are required, you will probably need to use a People field. In many instances, the outcome of a large list solution is to graduate the solution to a different application. People fields can make it challenging to connect the data to other systems. When sites become several years old, the users are no longer valid and become problematic when moving the lists around to different sites and different environments.