Overview

This document outlines best practices for creating and managing custom columns (fields) in Dataverse tables for akoyaGO. It explains the distinctions between Dataverse and SQL columns to help you design solutions that are efficient, scalable, and compliant with Microsoft guidelines—preventing potential disruptions to critical akoyaGO updates.

Dataverse columns are business-friendly fields you see in apps, with rules and meaning built in.
SQL columns are the raw database fields behind the scenes that just store the data.
- In short: Dataverse columns are for people and apps; SQL columns are for the database

Analogy:

Imagine a restaurant:

Dataverse columns are like the menu items you see as a customer. They're organized, named clearly, and come with descriptions so you know what you're getting
SQL columns are like the ingredients in the kitchen. They're the raw materials stored behind the scenes. They make the dishes possible, but you don't interact with them directly.
- In short: Dataverse = the menu for users; SQL = the pantry for the database

1. Why This Matters

Custom columns (also known as ‘fields’) let you tailor akoyaGO to your needs. However, adding too many can cause:

Upgrade failures: When tables exceed safe limits, Microsoft Dataverse cannot apply schema changes required for akoyaGO product updates or hotfixes. This means your environment could miss critical fixes and new features.
Performance issues: Slow queries, reporting delays.

22. Our Recommendation

2. Our Recommendation

Maximum: 100 custom columns per table.
Why: Dataverse tables have an underlying SQL limit of ~1,024 columns, but system fields and multi-part columns reduce this number significantly. Staying under 100 custom columns ensures:
- Smooth updates and hotfix deployments.
- Better performance and maintainability.

3. How different field types impact capacity

3. How Different Field Types Impact Capacity

Field Type	Impact on Capacity	Why It Matters
Single Line of Text	Low (~1)	Adds 1 column
Whole Number / Decimal / Currency	Low (~1)	Adds 1 column
Option Set / Choices	Low (~1)	Adds 1 column
Lookup	High (~3–4)	Adds multiple underlying columns (ID, name, type). Many lookups reduce your effective limit—100 lookups could consume 300+ SQL columns.
Customer / Owner	High	Similar to lookup
File / Image	High	Requires extra storage and metadata
Multi-table/polymorphic lookup	Very High	Creates multiple underlying 1:N metadata tables

4. Why does exceeding limits block updates

akoyaGO updates often include new tables, columns, and relationships. If your environment is near or over the Dataverse column limit:

Microsoft cannot add the required schema changes.
Updates and hotfixes fail, leaving your system unsupported and potentially unstable.
You may need a costly remediation project to reduce columns before updates can resume.

5. Splitting Lookup Fields (Normalization Strategy)

If you need many relationships, create a junction (intersect) table instead of adding multiple lookups to one table.

Example: Instead of 30 lookups on Order, create an Order–Region table with many-to-many links to Order and Region.

6. Monitor & Manage Usage

You can utilize the resources below to find out how many custom columns you have in your environment right now.

Power Apps / Maker:
- Export the Columns list to Excel
- Filter Managed = No
Admin Center:
- Use Dataverse for Analytics under the Power Platform Admin Center to track customizations.
API/PowerShell:
- GET [org].api/data/v9.0/attributes?$filter=IsCustomAttribute eq true&$select=LogicalName,AttributeTypeName

Recommendation: ≤ 100 custom columns per table; adjust lower if you use many lookups.
Benefit: Ensures smooth performance, easier updates, and simpler upgrades.
Tools: Power Apps, API queries, Admin Center analytics.

Microsoft Learn - Helpful Resources

Keywords: fields, columns, capacity, Power Apps, custom fields, Dataverse, tables, entities

akoyaGO Guidance - Custom Column Limits per Table