Dataverse vs SharePoint Lists: When to Use Which
Decision framework for choosing between Dataverse and SharePoint Lists as your Power Platform data source.
The Most Common Question in Power Platform
“Should I use Dataverse or SharePoint Lists?” is the question every Power Platform practitioner asks eventually. Both work as data sources for Power Apps and Power Automate. Both are cloud-hosted, Microsoft-managed, and accessible from anywhere. But they are fundamentally different systems designed for different purposes.
SharePoint Lists are a collaboration feature that happens to work as a lightweight database. Dataverse is a purpose-built relational database for business applications. Understanding this distinction saves you from painful migrations later.
Feature Comparison
| Feature | SharePoint Lists | Dataverse |
|---|---|---|
| Row limit | 30 million items per list (practical limit ~100,000 for good performance) | Effectively unlimited (millions of rows) |
| Column limit | 500 columns per list (including internal/system columns) | 1,024 columns per table |
| File/attachment storage | Built-in (attachments + document libraries) | File and Image columns (Azure Blob-backed) |
| Relationships | Lookup columns (single-level, limited) | Full relational model (1:N, N:N, polymorphic) |
| Security model | List/item-level permissions (SharePoint permissions) | Row-level and field-level security (security roles) |
| Calculated columns | Calculated/computed columns (limited functions) | Calculated, Rollup, and Formula (Power Fx) columns |
| Business rules | No server-side business rules | Yes — no-code validation rules, server-side |
| Auditing | SharePoint audit logs (site-level) | Built-in field-level auditing per table |
| Duplicate detection | No built-in feature | Yes — configurable duplicate detection rules |
| Views | List views (filter, sort, group) | Views + interactive dashboards + charts |
| Search | SharePoint Search | Dataverse Relevance Search |
| API | REST API (SharePoint Online) | Web API + OData + SDK |
| Offline support | No native offline for Power Apps | Built-in offline sync in Model-driven apps |
| Solution packaging | Not solution-aware | Fully solution-aware (ALM, managed solutions) |
| Environment isolation | SharePoint sites | Dataverse environments (dev/test/prod) |
| Indexing | Indexed columns (up to 20) | Custom indexes (up to 20 per table) |
Storage and Cost
This is often the deciding factor, so let us be specific.
SharePoint Lists: Included with Microsoft 365
SharePoint storage is pooled across your tenant:
- Base: 1 TB + 10 GB per licensed user
- Additional: Purchasable in 1 GB increments
- Cost: Effectively free for most organisations — the storage comes with your M365 licences
- Per-list limit: 30 million items, but Microsoft recommends staying under 100,000 for consistent performance
For Power Apps, SharePoint Lists as a data source work with standard connectors — no additional Power Apps licence needed beyond Microsoft 365 E1/E3/E5.
Dataverse: Per-Environment and Per-GB
Dataverse storage is allocated per tenant:
- Base: 1 GB database + 2 GB file + 2 GB log storage per tenant (with qualifying licences)
- Per-user allocation: Varies by licence (Power Apps Premium gives 250 MB database, 2 GB file per user)
- Additional storage: ~£30-40/GB/month for database storage
- Environment cost: Each non-default environment consumes 1 GB minimum
Using Dataverse requires a Power Apps Premium licence (or Dynamics 365 licence) for every user who accesses the data through an app.
Cost Analysis: Practical Example
Imagine an app used by 50 people, storing 10,000 records with 20 columns:
SharePoint route:
- Storage: ~50 MB (well within M365 allocation)
- App licensing: £0 (covered by M365)
- Total additional cost: £0/month
Dataverse route:
- Storage: ~100 MB (within base allocation in most cases)
- App licensing: 50 users x ~£15.50/user/month (Power Apps per-app plan) or 50 x ~£15.50 = £775/month
- Total additional cost: ~£775/month
For an organisation already paying for Power Apps Premium licences (e.g., through Dynamics 365 or an E5+ bundle), the Dataverse cost is already covered. For organisations on basic M365 E3, the licensing jump is significant.
Performance and Delegation
SharePoint Delegation Limits
When using SharePoint as a data source in Canvas apps:
| Operation | Delegable? | Notes |
|---|---|---|
Filter with =, <> | Yes | On indexed columns |
Filter with StartsWith | Yes | On text columns |
Filter with contains | No | Falls back to local filtering |
Search function | No | Not delegable to SharePoint |
| Sort | Yes | On indexed columns only |
Sum, Average, CountRows | No | Local only |
| Lookup relationships | Yes | Single-level lookups |
| Nested filters | Limited | Can cause unexpected local evaluation |
Practical impact: With SharePoint, if your list has more than 2,000 items (the maximum delegation row limit), any non-delegable operation will silently return incomplete results. This is the number one source of bugs in SharePoint-based Power Apps.
Dataverse Delegation Limits
| Operation | Delegable? | Notes |
|---|---|---|
Filter with =, <>, <, >, >=, <= | Yes | All column types |
Filter with StartsWith, EndsWith | Yes | Text columns |
Filter with contains (text) | Yes | Via Dataverse search |
Search function | Yes | Uses Relevance Search |
| Sort | Yes | All sortable columns |
Sum, Average, CountRows | Yes | Server-side aggregation |
| Lookup relationships | Yes | Multi-level supported |
in operator | Yes | With Dataverse |
And, Or in filters | Yes | Complex conditions supported |
Practical impact: With Dataverse, virtually every common operation is delegable. You can build apps against tables with hundreds of thousands of rows without worrying about incomplete results.
Security Model
SharePoint Permissions
SharePoint uses a hierarchical permission model:
- Site-level permissions: Members, Owners, Visitors
- List-level permissions: Can break inheritance, set unique permissions
- Item-level permissions: Can set per-item (but this degrades performance severely)
For Power Apps, SharePoint permissions determine what the current user can see and modify. This works well for simple scenarios but has significant limitations:
- No field-level security: You cannot hide individual columns from specific users. Everyone who can see the item sees all columns.
- Item-level permissions do not scale: Setting unique permissions on thousands of items causes performance degradation and hits administrative limits.
- No role-based filtering: You cannot define “sales reps see only their own records” without complex item-level permissions or folder-based workarounds.
Dataverse Security Roles
Dataverse provides enterprise-grade security:
- Security roles: Define CRUD permissions per table, per privilege depth (user/business unit/parent business unit/organisation)
- Row-level security: Users automatically see only records they have permission for — enforced server-side
- Field-level security: Hide sensitive columns (salary, national insurance number) from specific roles
- Business unit hierarchy: Align data access with organisational structure
- Teams: Group users for shared access patterns
This is a fundamentally different model. In Dataverse, security is declared in the schema and enforced by the platform. In SharePoint, security is bolted on to a collaboration tool.
Relationships and Data Integrity
SharePoint: Limited Relationships
SharePoint supports Lookup columns, which create a basic reference between lists. Limitations:
- Single-level lookups only (no “lookup of a lookup” in views)
- No referential integrity enforcement — deleting a parent item leaves orphaned lookups
- No cascade delete, cascade assign, or cascade share
- No many-to-many relationships (requires a junction list, managed manually)
- Lookup columns have a limit of 12 per list view threshold
Dataverse: Full Relational Model
Dataverse supports:
- One-to-many (1:N) relationships with configurable cascade behaviours (delete, assign, share, merge)
- Many-to-many (N:N) relationships (auto-creates intersection table)
- Polymorphic lookups (Customer type: references Account or Contact)
- Referential integrity: Configure whether to restrict delete, cascade delete, or remove reference
- Self-referential relationships (e.g., Manager lookup on Employee table)
If your data model has more than two or three related tables, Dataverse’s relational model saves significant development time and prevents data integrity issues that SharePoint cannot protect against.
Migration Path: SharePoint to Dataverse
If you start with SharePoint and later need Dataverse, migration is possible but not trivial.
Steps
- Design the Dataverse schema: Create tables, columns, and relationships that match (and improve upon) your SharePoint structure.
- Migrate data: Use Power Automate, the Dataverse import wizard, or a dedicated migration tool to move data. Map SharePoint internal column names to Dataverse columns.
- Recreate security: SharePoint permissions do not translate directly to Dataverse security roles. You need to design the security model from scratch.
- Update the app: Change data source connections in Power Apps from SharePoint to Dataverse. This typically requires rewriting formulas because column names and data types differ.
- Update flows: Modify Power Automate flows to use Dataverse triggers and actions instead of SharePoint.
- Test thoroughly: Delegation behaviour changes, column type mappings may introduce subtle bugs, and security model differences can cause unexpected access issues.
Common Migration Gotchas
- Column name mapping: SharePoint internal names (e.g.,
Title,OData__x0023_) do not match Dataverse logical names. Plan your mapping carefully. - Choice columns: SharePoint choice values are text strings; Dataverse choice values are integer-backed with labels. You need to map text values to the correct option set values.
- Attachments: SharePoint item attachments need to be migrated to Dataverse File or Note (annotation) records.
- Lookup columns: SharePoint lookup values reference list item IDs. Dataverse lookups reference GUIDs. You need to build a mapping table.
- Existing automations: Any Power Automate flow triggered by SharePoint needs to be rebuilt with Dataverse triggers.
Decision Framework
Use SharePoint Lists When
- Budget is constrained: You have M365 licences and cannot justify Power Apps Premium licensing
- Data is simple: Flat or lightly relational data, under 100,000 rows
- Users are few: Small team (under 50 users) with straightforward permissions
- The app is simple: Single-table CRUD, basic forms, no complex business rules
- Collaboration is primary: The data lives alongside documents and needs SharePoint’s collaboration features (co-authoring, version history, document libraries)
- Speed matters: You need something built today with minimal setup
Use Dataverse When
- Data model is complex: Multiple related tables, referential integrity matters
- Scale is large: Tens of thousands to millions of rows
- Security is critical: Row-level, field-level, or business-unit-level access control required
- Auditing is required: You need to track who changed what and when, at the field level
- Business rules are needed: Server-side validation, calculated/rollup columns, duplicate detection
- ALM matters: You need dev/test/prod environments with managed solution deployment
- You are building for the long term: The app will be maintained for years and needs to scale
- Licences are already covered: Organisation has Power Apps Premium, Dynamics 365, or appropriate E5 licences
The Grey Area
Many scenarios fall between the two. A pragmatic approach:
- Start with SharePoint if you are building a prototype, proof of concept, or a tool for a small team. Validate the idea first.
- Plan for Dataverse if the prototype succeeds and the app will scale to more users, more data, or stricter requirements.
- Start with Dataverse if you already know the app is business-critical, the data model is complex, or security requirements are non-negotiable.
Common Mistakes
Mistake 1: Using SharePoint for Complex Relational Data
If your data model has four or five related tables with integrity constraints, SharePoint will cause pain. You will end up building workarounds for missing referential integrity, fighting delegation limits on lookups, and maintaining data consistency manually via flows.
Mistake 2: Using Dataverse When SharePoint Would Suffice
Not every app needs Dataverse. A simple leave request tracker for a 20-person team, storing a few hundred records a year, does not need row-level security, business rules, or solution packaging. SharePoint is perfectly adequate and costs nothing extra.
Mistake 3: Ignoring the Licensing Conversation
Building an app on Dataverse and then discovering that 200 users need Power Apps Premium licences — after the app is deployed — is an uncomfortable conversation with finance. Have the licensing discussion early.
Mistake 4: Assuming Migration Is Easy
“We will start with SharePoint and migrate to Dataverse later” is a common plan. It works, but it is not painless. Every formula, every flow, every security configuration needs to be revisited. Budget for the migration effort upfront if you know Dataverse is the eventual destination.
Mistake 5: Not Indexing SharePoint Columns
If you use SharePoint with more than a few thousand items, you must create indexes on columns used in filters and sorts. Without indexes, views and app queries hit the list view threshold (5,000 items) and fail or return incomplete data.
Summary
SharePoint Lists and Dataverse are both valid data sources for the Power Platform, but they serve different needs. SharePoint is accessible, affordable, and perfectly fine for lightweight applications. Dataverse is robust, scalable, and designed for serious business applications. Choose based on your data complexity, security requirements, scale expectations, and licensing reality — not on what seems easiest today.