Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

The question is, Why SharePoint stores all list data in one table - AllUserData?

I found numerous blog posts and articles about fact, that all list data stored in AllUserData. But could you explain why? Why microsoft decided to store data in one single table? What is pros and cons, and why not to use pattern one list - one table?

Thanks in advance.

share|improve this question

2 Answers 2

SharePoint Databases aren't relational data. It's also stored as binary data, making it faster to process (The unmanaged DLL can read it directly into variables to pass back to the managed dll faster, binary values often also provide a small amount of compression). These things offset the requirement of having a traditional database setup. The indexes are all it needs.

Basically its faster this way.

Dynamics, SQL, OLAP (Power BI) etc are for other forms of data storage, IE tabular and relational. We can connect to these directly using BCS or in preference if one exists, an out of the box connector.

share|improve this answer
    
Why they can't create content database as a relational database? –  Kai Mar 21 at 8:46

I think it has to do with the fact that since there is one content database per site collection the design choice was made to have a single table for all lists in that collection so that MS didn't have to get into complex scenarios of creating/droping tables in the database for each list (from a database driven approach this would be very much a headache).

If you create another collection it will have it's own content database with it's own table. So you could say this helps explain why MS warns of having any single collection grow too large - all the docs/list items are in one table.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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