I occasionally hear questions about whether or not to use SharePoint Lists or SQL for data storage. In many cases, SharePoint lists are a powerful and feature-rich alternative to a traditional SQL database and in this post I’ll explain some of the pros and cons.
SharePoint wins this one. It’s trivially easy to create a list for storing your data. Navigate to Site Contents, and click Add, and select List. Start with a new blank list and add your columns, and you have a list for data storage. SQL involves several extra technical steps, including understand data types and management of connection strings.
How easy is it to add a new column? Users with list management permissions can add a new column in a couple of clicks.
Microsoft 365 includes a rich set of app creation and automation applications which have rich in-built integration with SharePoint Lists. For example, it’s easy to create a PowerApp with a custom UI for adding/editing items in your list. In PowerAutomate or Logic Apps, you can use the SharePoint connector to easily automate business processes triggered on adding or editing a list item. Or, create new list items when another trigger happens e.g. an email is received.
In SQL, many of these scenarios are more difficult - for example, Logic Apps use a rather tedious polling mechanism to detect addition of new items in an Azure SQL database.
UI is an easy win for SharePoint. Modern Lists include many customisation options for rendering your data in the list. Views can be created to include custom filtering and sorting. Of course, SQL databases don’t natively include any kind of data visualisation or rendering options and you’ll find yourself paying for third party products or fiddling with tricky integrations to get the functionality you need.
Not everyone is aware of the potential of calculated fields in SharePoint lists. You can create a field which, rather than storing data, is defined by an excel-like formula - and it’ll show a calculated value based on the value in other field(s). If you’re using an SQL database for your data storage, you’ll need some coding skills to replicate this functionality using Azure SQL Stored Procedures, Functions, or custom code.
In a SharePoint list, there’s various limits that must be carefully considered. These limits simply don’t exist in SQL, or can be easily mitigated. The relevant SharePoint List limits are as follows:
SharePoint has some limits related querying and display of data. This centers around the “List View Threshold”. Essentially, if your list contains more than 5000 items, then you must apply an index to columns which you intend to perform filtering on. Additionally, not all column types are filterable - eg. Multi-value people fields (single value people fields are indexable).
SQL databases don’t contain every field type you’ll want to use. For example, people fields are incredibly useful for assignment/approval scenarios. The Managed Metadata service can be used in Taxonomy fields. And the (new-ish) Location column type is a really powerful way of storing a geographical location from which you can render a map, standardize addresses, and so forth.
Lists have many advanced features, and for many businesses, version history is one of those killer features. Enable version history to store a complete log of which fields were changed, when, and by who. SQL changes are normally made under a single identity (defined by a connection string) and won’t natively store a version history.
When you think of SQL databases as a developer, one of the big stand-out features is the relational aspect. Relationships can be modelled using primary and foreign keys and one-to-one, one-to-many and many-to-many relationships are defined. What’s not always obvious is that relationships can also be modelled via SharePoint Lookup fields. They’re not difficult to create: when creating a new field, select Lookup as the type, and select the secondary list which you’d like to link. You can choose the fields of the second list which should be displayed in the first list. Then, when creating an item, the lookup UI can be used to select the correct item.
For example: a list of Orders can have a lookup field pointing to the ‘customer name’ field of a Customers list. Within the Orders list, you see at a glance the address of the customer.
Although SharePoint puts a limit of 50000 on the total number of unique permissioned items, this is already an improvement over native SQL databases, which normally don’t have security applied at the data layer. The security of a list item integrates seamlessly with your Active Directory users and groups.
A list of features of SharePoint Lists wouldn’t be complete without plugging the 365Automate Charts and Dashboards solution which will instantly render a beautiful visualisation of the data in your list. Since we’ve already mentioned an ‘Orders and customers’ example above, here’s some visualisation ideas:
Check out the Dashboard Designer for more info on our charts solution (which has a 30 day free trial).