The take
A SharePoint List is not a database, and using it as one is a bet that your solution will die before it succeeds. The 5,000-item view threshold, the indexing cutoff at 20,000 items, no enforced referential integrity, no transactions - none of these limits announce themselves on day one. They wait until the list has been quietly accumulating rows for eighteen months, until people depend on it, until migrating away is genuinely painful. SharePoint Lists are excellent at what they're designed for. A database is not on that list.
Why people do it
Because it's the rational choice at the start, and I say that as someone who built yesterday's Build It on exactly this setup. The list is already paid for. Creating one requires no ticket, no DBA, no procurement conversation - for most citizen developers it's the only data store they have permission to create. The editing UI is genuinely good. Power Apps and Power Automate connect to it natively. And the project is always small at the beginning: a register of 200 equipment checks, a request log, a tracker. Nobody provisions Azure SQL for 200 rows, and nobody should.
The trouble is that nothing about that decision gets revisited. The list that was the right call at 200 rows is still there at 20,000, structurally load-bearing, and now it's a different thing wearing the same URL.
Why it bites
The limits are real and they arrive on a delay. The list view threshold is 5,000 items - past that, any view or query that sorts or filters on a non-indexed column fails outright, and in SharePoint Online there is no admin setting to raise it. The escape hatch is indexing, and that hatch closes too: you can't add an index past 20,000 items. Storage isn't the issue - a list holds 30 million items - but a store you can't query freely is a filing cabinet with a jammed drawer. The cruelty is in the timing. Day one, everything works. The failure ships eighteen months later, to whoever is on duty when the register crosses the line.
The relational features are cosplay. A lookup column looks like a foreign key. It isn't one. Nothing stops a flow writing an orphaned reference, there's enforcement you can switch on but almost nobody does, and queries are capped at 12 lookup-type columns before SharePoint refuses them. There are no transactions: when your Power Automate flow writes three related rows and dies on the second, the list keeps the half-finished state forever, and no rollback is coming. A database's entire job is to make inconsistent states impossible. A list's job is to hold what it was given.
The data becomes valuable and wrong at the same rate. This is the part that earns the "actively dangerous" label. By the time the limits bite, the list holds years of operational history - which means the orphaned lookups, the half-written flow runs, and the duplicate entries nobody could prevent have been compounding all along, inside the dataset everyone now trusts because it's been around so long. Longevity reads as reliability. In a store with no integrity enforcement, longevity is just more time for the rot to spread.
The better way
Use lists for what they're for: human-edited data, hundreds-to-a-few-thousand rows, forms in front, no relational integrity riding on it. The equipment register, the leave tracker, the intake form - all genuinely great fits.
The moment the data is relational, transactional, or growing without limit, move - to Dataverse if you live in the Power Platform, to Azure SQL if you have anyone who can spell DDL. And move early: the migration costs a day at 3,000 rows and a quarter at 30,000, because by then it's not data you're migrating, it's dependencies. If you're staying in scope, yesterday's Build It shows the way to connect Power BI to a list that respects the thresholds. If you're reading the warning banner right now, that's not a tip, it's a deadline.
Frequently Asked Questions
Can't an admin just raise the 5,000 limit?
Not in SharePoint Online. The list view threshold was adjustable in on-premises SharePoint Server, which is where the folk memory of "ask IT to bump it" comes from. In the cloud it's fixed, because the throttling protects shared infrastructure, and Microsoft's position is to design within it rather than around it.
Doesn't the 30-million-item capacity mean lists scale fine?
Capacity and queryability are different promises. A list will store 30 million items, but every view, filter, and query past 5,000 items has to be servable by an index, and you can't create new indexes past 20,000 items. Scale that you can't query isn't scale, it's hoarding.
What about Microsoft Lists - isn't that the new product?
Same engine, friendlier front door. Microsoft Lists is SharePoint lists, and every limit in this post applies to it identically. A rebrand is not a re-architecture.
When is a SharePoint List genuinely the right choice?
When humans are the main writers, volumes sit in the hundreds to low thousands, rows are independent of each other, and the built-in forms, views, and permissions are doing real work for you. That's a long and honourable list of use cases - intake forms, registers, trackers, reference data that changes quarterly. The test isn't "is this data important", it's "does this data behave like rows in a tracker or like a system of record".
Dataverse or Azure SQL when I outgrow it?
Dataverse if your solution already lives in Power Apps and Power Automate - the migration is the gentlest, the integration is native, and you inherit actual relational behaviour and row-level security. Azure SQL if you have SQL skills available and want the data in the wider analytics estate without an export step. The wrong answer is neither: deciding the migration is too hard and feeding the list another year of data.
The most expensive database in your organisation is the SharePoint List that's been working fine for two years.