Conceptually, how are lists different from SQL tables?

Question

Total noob question here, but I've just started learning Sharepoint, having only had .NET experience in the past and I'm trying to understand conceptually the difference between a list and just an SQL table.

How are they different from SQL tables?

SQL tables have a set of columns which all have a type and each row must have values which must correspond to the definition of the column (eg. value type, nullable).

A list seems to have a list definition which all items in the list must correspond to. How is this different? Can someone please explain in laymen's terms?

Answer 1

SharePoint lists consist of rows and columns that store data in a similar fashion to a traditional relational database management system such as SQL Server. However, a benefit of lists is that SharePoint includes Web Parts that provide simple methods for managing the data. If the data was stored in a database, it would require custom user interface components to access it and manipulate it. Also, specialized skills are required to design, implement, and maintain a custom database. Another advantage of using lists is that custom workflow and event handlers can easily be registered to them.

There are also advantages to storing data inside of a database. One is the availability of all the ACID (Atomic, Consistent, Isolated and Durable) properties of transactions. If your business logic requires transactions, storing data in a database is preferable to using lists. Also, SharePoint lists are meant to store simple data structures. If you require a complex data model with intricate relationships, a database is more appropriate.

The following table summarizes the benefits of using databases and SharePoint lists.

Benefits                                   Database    SharePoint list

Handles complex data relationships            Yes             No

Handles large numbers of items                Yes             No

Handles transactions                          Yes             No

Is easy to use                                No              Yes

Accommodates workflows                        No              Yes

Includes a standard interface                 No              Yes

Can easily add binary data                    No              Yes

https://msdn.microsoft.com/en-us/library/ff647105.aspx

Answer 2

Conceptually they both are same.SharePoint lists store data in a similar way such as SQL Server.

So if you go to your content database, **

• List of all SharePoint lists is located in dbo.AllLists table

• If you want to see items of particular list copy its ID from tp_ID and query [WSS_Content].[dbo].[UserData]

But there are some drawbacks of using SharePoint list.

• SharePoint is not good at handling transactional data and will perform badly.
• If you have complex table structure it is recommended to use sql table instead of Sharepoint list

Answer 3

I'll just add an additional point. We are moving from Lists to SQL because we are running into situation where the lists hit the 5,000 record 'soft limit'. You can have more than 5,000 items in a list but you can't interact with the list column (filter, sort) if the column is not set to be indexed.