Tags: Relational databasesAIReportingDesktopCommand line
The contracts list started small. Someone built it in an afternoon because the whole team already had access to the site and the form came free. Five years on it has a lookup to Suppliers and another to Cost centres, a Power Apps front end, several Power BI reports and a flow that chases renewals. Then the complaints start. The “All contracts” view shows an error and the renewals report takes most of the morning to refresh. When someone asks Copilot which contracts expire next quarter, it misses half of them.
Eventually someone asks whether SharePoint was ever meant to be a database. A list does a database’s job for a while, and the point where it stops is predictable. This post sets each limit against its Microsoft source and gives you a decision table to run your own list through, followed by the routes open once a list has outgrown the job. One route, keeping the list for editing and adding a SQL copy for everything that reads, is what SPSync does, so we say so where it comes up.
Can SharePoint be used as a database?
Within limits, yes. A list has typed columns, an ID for every item, lookups to other lists in the same site, validation, permissions, version history and a form people can use without training. Microsoft Lists is the same thing in a different app: Microsoft’s guide to SharePoint lists in Power Apps says you can always find a list in both Microsoft Lists and SharePoint. Everything here applies whichever one you open.
What a list lacks is the part of a database you query. You cannot run joins and totals over the whole table, and no transaction covers several items. You reach the data only through a view, the REST API or Microsoft Graph, and the limits on those routes decide where a list stops working as a database.
What a SharePoint list does well
A list is very good at being edited. People open it in the browser or in Teams with nothing to install, and permissions follow the site. Version history records who changed a field and when, and Power Automate can start a flow when an item changes. All of it comes with the Microsoft 365 licences people already have.
For a register edited by a handful of people and read in its own views, those strengths outweigh every limit below. Moving that list into a database would mean building an app for the editing and paying someone to look after it, for very little gain.
Where a list stops behaving like a database
The limits arrive one at a time, usually in the order below. Each has a Microsoft source, and the SharePoint list limits cheat sheet collects them in one table with the links, so this section covers what each limit does to the people using the list.
Row count and the view threshold
A list can hold up to 30 million items, so storage is rarely the problem. The problem is the list view threshold of 5,000 items, which stops any query that is not narrowed first by an indexed column. Views without an indexed filter stop rendering, and so do the exports and flows built on them. Indexing the right column fixes simple filtered views and leaves whole-list work blocked. Our page on reporting past the 5,000-item threshold covers the index-first fixes and what they leave unsolved.
Lookups and joins
A lookup column is a join, and SharePoint caps how many joins one query can carry. The Power Query connector documentation puts it at 12 join operations per query, and person columns count towards it along with lookups. A lookup also points only at a list in the same site, so two lists in different sites cannot be joined inside SharePoint at all. In SQL a join is the ordinary way to ask a question, and a query can join as many tables as the question needs.
Many people saving at once
SharePoint saves one item at a time. When two people edit the same item, Graph protects the later save: an update that carries the item’s eTag returns 412 Precondition Failed if someone else changed it first. Changes that span several items have no such protection. Microsoft’s JSON batching page shows a batch in which some requests fail and the others succeed, so an app that moves stock from one row to another can leave the change half done. A database wraps that in a transaction. For a team where each person edits their own items, this rarely matters.
Reporting over the whole list
Reports want every row, grouped and totalled. Power BI reads a list through its SharePoint connector page by page, so a refresh gets slower as the list grows and fails once the default view carries too many lookups. Excel exports come from a view, so they hit the threshold too. Our post on slow and failing Power BI refreshes walks through the causes in order.
Apps and Power Apps delegation
A Power Apps app over a list works well while its filters can be handed to SharePoint. When a formula cannot be delegated, the app fetches the first 500 records by default and works on those, and the data row limit can be raised to 10,000 records at most. A search or a count over a larger list then gives answers that look right and miss rows. Pointing the app at SQL instead means the SQL Server connector, which is a premium connector, so it needs Power Apps licences that cover premium connectors. The Power Apps performance page covers both routes, and our post on Power Apps past the data row limit sets out which formulas delegate to each.
AI and agents reading the list
Copilot Studio can use a list as knowledge, and its quotas page says SharePoint list queries only return data from the first 2,048 rows. An agent that reads a list through search finds items that match a question, which is fine for “what does contract 4471 say”, and poor at “how many contracts expire in March”, which needs every row counted. We wrote up why Copilot cannot count your rows with the test that shows which rows an agent can see.
SharePoint list vs SQL database, concern by concern
Run your own list down the table below. If most of your answers sit in the middle column, the list is doing its job and the cheapest move is to leave it alone. If several sit on the right, the data needs a database somewhere, and the next sections cover where.
| Concern | A list is enough when | A database is better when |
|---|---|---|
| Concurrent editors | A few people each edit their own items | Several people or processes change related rows at once, and half a change is a problem |
| Row count | Every view filters on an indexed column and stays under the 5,000-item threshold | Views, exports or flows need the whole list |
| Lookups and joins | One or two lookups to lists in the same site | Reports join several lists, lists in other sites, or data from other systems |
| Reporting | List views, list charts and a small Power BI report | Totals across the whole list, trends over time, scheduled refreshes that must finish |
| Apps | A Power Apps form with delegable filters | App queries that cannot be delegated and must see every row |
| AI and agents | Questions about one item, answered by search | Questions that count, total or compare rows across the list |
| Audit and retention | Version history and the Microsoft 365 audit log cover what auditors ask | You need history in SQL, or a copy held outside Microsoft 365 |
| Cost | Microsoft 365 licences you already pay for | You can budget for a database and for someone to run it |
The audit row is the one where a list often wins. Version history keeps earlier values of each field, and the Microsoft 365 audit log records list item activities such as an item being deleted or moved to the recycle bin. A database copy does not inherit either of those, so a team that moves its data needs to rebuild that record.
Should I use a SharePoint list as a database?
The question that decides it is who reads the list. When the people who edit the records are the same people who read them, in the list’s own views and forms, a list is the right tool, and the limits above may never apply to you. When the readers are somewhere else, a dashboard in Power BI, an app with its own screens, a finance system or an agent, those readers want what a database gives them, and from their side the list feels slow and incomplete.
Most growing lists end up with both kinds of reader. The editors are still happy, because saving one item works the same in a large list as in a small one, while the reports and agents on top of it are fighting a limit. That split is why the routes below are not all-or-nothing.
What are the alternatives to a SharePoint list?
There are four broad routes, and they cost very different amounts.
Stay on the list and tune it
Index the columns your views filter on, make the first filter in each view an indexed one, and move closed items to an archive list once a year. This costs nothing and often buys years. It does not help a report that needs the whole list or a join across lists, and splitting a list by year tends to break the reporting the list was kept for.
Move the data into Dataverse
Dataverse is Microsoft’s data platform for Power Apps and Dynamics 365. It has proper tables, relationships, business rules and security roles, and model-driven apps on top. It is a good home for data that lives inside Power Platform apps, and it needs Power Apps or Dynamics 365 licensing. Dataverse can also read a list live through a virtual table, with no copy at all. Our comparison with Dataverse and virtual tables covers when a virtual table is enough and when a reporting tool still needs a SQL copy.
Move the data into a database and retire the list
Build an app on SQL Server, Postgres or Azure SQL, move the data across and switch the list off. This is the right route when the list has become the back end of a real application with its own rules, and it is the most expensive one, because the editing experience SharePoint gave you for free now has to be built. Our migration use case and the post on cutting a list over to a database cover how to do it without losing rows.
Keep the list for editing and add a SQL copy
People keep editing in SharePoint, and a replication job copies the list into a database on a schedule. Reports, apps that only read, data pipelines and agents use the copy, where joins, totals and every row are available, and the list keeps the forms and version history people are used to. There are several ways to build it:
- Fabric mirroring, which Microsoft lists as a preview source, replicates a list into OneLake with a read-only SQL analytics endpoint, and needs a Fabric capacity.
- A Power Automate flow that writes each created or changed item into a SQL table works for small lists, and you have to handle deleted items and catch-up runs yourself.
- Azure Data Factory can copy the list into Azure SQL on a schedule, usually as a full copy each run.
- SPSync, our product, replicates the list into SQL Server, Azure SQL, Postgres and other databases incrementally, with lookups as key columns.
The copy is one way. SharePoint stays the system of record, and anything that needs to change a record still changes it in the list.
How a SQL copy of a SharePoint list works
Taking SPSync as the example, the first run reads the whole list into a table and every run after that reads only the items that changed, every few minutes by default. A reconcile, weekly by default and configurable, compares the two sides as a quality check and catches the copy up after an outage on either side. Reads go through Graph’s paging and delta endpoints, so the view threshold does not apply to them.
Each item becomes one row keyed on its SharePoint item ID. A lookup becomes a key column you can join on, a person column points at a shared users table, and a multi-choice column becomes a child table. Deleted items keep their row with a deleted timestamp, so last quarter’s report still adds up. Some things stay in SharePoint: version history and attachments are not replicated, and managed metadata columns are not replicated yet.
The contracts list from the start of this post would come out as tables for Contracts, Suppliers and Cost centres, joined on their keys. The renewals report reads a SQL view, and an agent with a read-only database login can count what expires in March, while the team carries on editing in SharePoint. Write-back, with a person approving each change, is designed and on the roadmap and has not shipped. The SharePoint to SQL Server page shows the schema it writes.
When the list should stay as it is
Plenty of lists should never become anything else. A list of a few hundred items, edited and read by the same small team, is a good use of SharePoint, and any route above would add cost without solving a problem. Check it against the table when someone asks for a report the list cannot produce. If you want to see your own list as SQL tables before deciding, book a call and we will run it against your tenant with you.