This is my scenario: I have an external SQL Server db with about 100.000 items in one table. These items may be grouped into 5 categories. Now the we'd like to add a sharepoint interface on top of this list for a familar editing experience inside SharePoint. There will be about 20 simultaionius users on this list, we're using sp foundation in a minimal configuration.

What of the following options would you suggest:

  1. Create one sp list for each category. Now each list contains about 20.000 items, and this would be no problem for sharepoint. We could even let the user use the datasheet view without any problems. Every 15 min we do a sync with the external database using a 3rd party syncing component.

  2. We use only 1 list with 5 different views, no problem. Syncing as in option 1

  3. We create a external content type and use the bcs feature. This feature works fine, and handles 100.000 items without any problems. We could easily provide filters and also do sorting and paging. And use the datasheet view to edit data. BSC (or is it bcs or bdc - impossible to remember) is a perfect match for this scenario.

Ok, that was a bit exaggerated but hopefully I can get some ideas of wether using external content types is a good fit or not. I find it difficult to figure out the downsides of it.

Thanks for any help



2 Answers 2


Large List Threshold in 5,000 items for users, and 20,000 for site collection admins. When list hits this threshold you can't query whole list, view filters can be applied only to indexed columns. On paged views there is no difference, on Data Sheet View will fail.

External lists have no Data Sheet View (!), REST interface, RSS. You can't query more than 2,000 entities from external system at single query. For External lists you nedd to carefully design data access methods to work with over 2,000 external entities.

In general 100,000 items is too many. You should consider what operations executed on this dataset, yhan design data access in sharepoint.

  • Argh! I knew there was something... The exteranl content type functionality seemed so promising. Thanks a lot for your comments!
    – Larsi
    Commented Apr 14, 2011 at 6:26

Option 3 does seem like a no brainer until you start learning about all the limitations of external lists, such as:

Also, gandjustas has a good point about item threshold.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.