Dataverse Column Types Reference

Overview

Dataverse offers a rich set of column (field) types for modelling your data. Choosing the right type matters — it affects storage, performance, delegation in Power Apps, available filters in views, and what you can do in Power Automate expressions. This reference covers every column type, its limits, and when to use it.

Text Columns

Single Line of Text

Property	Detail
Max length	4,000 characters (default 100)
Formats	Text, Email, URL, Phone, Ticker Symbol, Text Area
Searchable	Yes (included in Dataverse search by default)
Sortable	Yes
Delegable in Power Apps	Yes — equality, StartsWith, contains (with Dataverse)
Indexable	Yes

When to use: Names, titles, short identifiers, email addresses, URLs, phone numbers. Use the Email/URL/Phone formats to get built-in validation and clickable rendering in model-driven apps.

Gotcha: If you set max length to 4,000 and the column is included in Dataverse search, it increases the search index size. Keep the max length realistic for your data.

Multiple Lines of Text

Property	Detail
Max length	1,048,576 characters (1 MB)
Formats	Text, Rich Text, Memo
Searchable	Only first 500 characters indexed for search
Sortable	No
Delegable in Power Apps	Limited — no sorting, limited filtering
Indexable	No

When to use: Descriptions, notes, comments, long-form content. Use Rich Text format if users need basic formatting (bold, lists, links) in model-driven apps.

Gotcha: Rich Text stores HTML markup, which counts towards the character limit. A 500-word note with formatting can easily use 3-4x the characters of plain text.

Number Columns

Whole Number

Property	Detail
Range	-2,147,483,648 to 2,147,483,647
Formats	None, Duration, Time Zone, Language
Sortable	Yes
Delegable	Yes — all comparison operators
Indexable	Yes
Calculated/Rollup	Can be used in both

When to use: Counts, quantities, integer identifiers, age, scores.

Gotcha: The Duration format stores minutes as an integer but displays as hh:mm. The Time Zone format stores a whole number representing a Windows time zone index.

Decimal Number

Property	Detail
Range	-100,000,000,000 to 100,000,000,000
Precision	0 to 10 decimal places
Sortable	Yes
Delegable	Yes
Indexable	Yes

When to use: Measurements, percentages, scientific values, any number needing decimal precision that is not currency.

Gotcha: Default precision is 2. Once you increase precision after data exists, existing rows keep their original precision — they are not retroactively reformatted.

Float (Floating Point)

Property	Detail
Range	-100,000,000,000 to 100,000,000,000
Precision	0 to 5 decimal places
Sortable	Yes
Delegable	Yes
Indexable	Yes

When to use: Similar to Decimal but with less precision. Microsoft recommends Decimal over Float for most scenarios. Float is a legacy type.

Currency

Property	Detail
Range	-922,337,203,685,477 to 922,337,203,685,477
Precision	0 to 4 decimal places (default 2)
Auto-creates	A read-only “Base” column for exchange rate conversion
Sortable	Yes
Delegable	Yes
Indexable	Yes

When to use: Any monetary value. Currency columns automatically integrate with the organisation’s base currency and transaction currency for multi-currency support.

Gotcha: Each currency column creates a hidden _base companion column storing the value in the organisation’s base currency. This doubles the storage per currency field.

Date and Time Columns

Date Only

Property	Detail
Behaviour	User Local or Date Only
Format	Date only (no time component)
Sortable	Yes
Delegable	Yes
Indexable	Yes

Date and Time

Property	Detail
Behaviour	User Local, Time-Zone Independent, or Date Only
Format	Date + time
Sortable	Yes
Delegable	Yes
Indexable	Yes

Behaviour Modes Explained:

Behaviour	Stored As	Displayed As	Use Case
User Local	UTC	Converted to user’s time zone	Meeting times, deadlines
Time-Zone Independent	Raw value, no conversion	Exactly as entered	Birthdays, policy effective dates
Date Only	Date without time	Date only	Due dates, hire dates

Critical warning: You cannot change the behaviour mode after creation if the column contains data. Plan this carefully before deployment.

Relationship Columns

Lookup

Property	Detail
Type	Many-to-one reference to another table
Sortable	Yes (by primary name of related record)
Delegable	Yes — filter by lookup value
Indexable	Automatically indexed
Cascade behaviours	Assign, Share, Unshare, Reparent, Delete

When to use: Anytime you need to reference a record in another table — customers on an order, managers on an employee, categories on a product.

Gotcha: Each lookup column creates a relationship. You can have up to 500 relationships per table. Polymorphic lookups (Customer, Owner) reference multiple tables from a single column but are more complex to work with in Power Apps.

Many-to-Many

Not a column type per se — it creates an intersection table behind the scenes. Useful when records in both tables can relate to multiple records in the other (e.g., students and courses).

Choice Columns

Choice (Single Select)

Property	Detail
Options	Up to 2,000 options per choice
Stored as	Integer value + label
Sortable	Yes (by label)
Delegable	Yes
Indexable	Yes
Global vs Local	Can use a global choice (shared) or local (table-specific)

When to use: Status fields, categories, types — any fixed list of options where users pick exactly one.

Choices (Multi-Select)

Property	Detail
Options	Up to 2,000 options
Stored as	Comma-separated integer values
Sortable	No
Delegable	Limited — `contains` only
Indexable	No

When to use: Tags, skills, categories where multiple selections are valid.

Gotcha: Multi-select choices are harder to filter, sort, and report on. If you need robust querying, consider a many-to-many relationship with a separate options table instead.

Yes/No (Boolean)

Property	Detail
Stored as	Bit (0 or 1)
Labels	Customisable (e.g., Active/Inactive, Yes/No, True/False)
Default	You must set a default value
Sortable	Yes
Delegable	Yes
Indexable	Yes

When to use: Flags, toggles, binary states. Keep it simple — if you might need a third state later, use a Choice column instead.

File and Image Columns

File

Property	Detail
Max size	10 GB per file (configurable, default 32 MB)
Stored in	Azure Blob Storage (managed by Dataverse)
Multiple files	One file per column per row
Searchable	No (file contents not indexed)

When to use: Document attachments, PDF uploads, spreadsheet imports.

Image

Property	Detail
Max size	30 MB
Formats	JPG, PNG, GIF, BMP
Thumbnail	Auto-generated, configurable size
Primary image	One column per table can be the primary image (shown in views)

When to use: Profile photos, product images, logos. For document storage, use File instead.

Gotcha: Image columns store a thumbnail in the row data and the full image in blob storage. This means the thumbnail is fast to load in galleries, but the full image requires an additional API call.

Computed Columns

Formula

Property	Detail
Language	Power Fx
Real-time	Yes — calculated on read, always current
Can reference	Columns in the same table only
Sortable	Depends on output type
Delegable	Yes (evaluated server-side)

When to use: Derived values that must always be current — full name from first + last, age from date of birth, status labels.

Example formula:

If(ThisRecord.'Due Date' < Now(), "Overdue", "On Track")

Calculated

Property	Detail
Language	Workflow expression editor (not Power Fx)
Recalculation	Every 24 hours + on record retrieval
Can reference	Same table columns + related table columns (one level)
Sortable	Yes
Delegable	Yes

When to use: Values that involve related table lookups or where the 24-hour refresh cycle is acceptable.

Gotcha: Calculated columns are not real-time. If the source data changes, the calculated value may be stale for up to 24 hours. For real-time needs, use Formula columns.

Rollup

Property	Detail
Functions	SUM, COUNT, MIN, MAX, AVG
Recalculation	Every hour (configurable, minimum 1 hour)
Can reference	Related tables via one-to-many relationships
Sortable	Yes
Delegable	Yes

When to use: Aggregations over child records — total order value, count of related tasks, average rating.

Gotcha: Rollup columns have a Last calculated timestamp visible in model-driven apps. Users can click the refresh icon to force recalculation. The hourly cycle means dashboards may show slightly stale totals.

Auto-Number

Property	Detail
Format	Prefix + number + suffix (e.g., `CASE-{SEQNUM:5}-{DATEFULL}`)
Tokens	`{SEQNUM:n}` (sequential), `{RANDSTRING:n}` (random), `{DATEFULL}`, `{DATECOMPACT}`
Editable	No — auto-generated on create, cannot be modified
Sortable	Yes
Delegable	Yes

When to use: Case numbers, ticket IDs, invoice numbers — any human-readable unique identifier. Do not use as primary key; Dataverse already generates a GUID primary key.

Gotcha: The sequence resets if you delete and recreate the column. It does not reuse numbers from deleted records.

Column Type Decision Matrix

Use this table to pick the right column type quickly:

Scenario	Recommended Type	Why
Person’s name	Single Line of Text (100 chars)	Short, searchable, sortable
Long description	Multiple Lines (Plain or Rich)	Supports large content
Quantity/count	Whole Number	Integer, full delegation
Price/cost	Currency	Multi-currency support, base conversion
Percentage (0-100)	Decimal (precision 2)	More reliable than Float
Due date	Date Only (Date Only behaviour)	No time zone confusion
Meeting time	Date and Time (User Local)	Auto-converts to user’s zone
Birthday	Date Only (Time-Zone Independent)	Must not shift with zones
Status	Choice	Fixed options, good filtering
Tags	Choices (multi-select) or M:N relationship	M:N if you need reporting
Active/inactive flag	Yes/No	Simple boolean
Reference to another record	Lookup	Creates relationship
Document attachment	File	Up to 10 GB
Unique case number	Auto-Number	Auto-generated, sequential
Derived value (real-time)	Formula	Always current
Aggregate of children	Rollup	SUM/COUNT/AVG/MIN/MAX

Indexing and Performance Considerations

Dataverse automatically indexes the primary key (GUID), primary name column, and lookup columns.
You can create custom indexes on up to 3 columns (composite index).
Columns used frequently in filters, sorts, or views should be indexed.
Multiple Lines of Text, Choices (multi-select), File, and Image columns cannot be indexed.
Each table supports up to 20 custom indexes.
Over-indexing slows down write operations. Index only columns you actively filter or sort on.

Delegation Considerations in Power Apps

Delegation determines whether filtering and sorting happens on the server (good) or in the app (limited to 500/2,000 rows). Here is a quick summary by column type:

Column Type	Filter Delegable	Sort Delegable	Notes
Single Line of Text	Yes	Yes	StartsWith, =, <>
Multiple Lines	Limited	No	Equality only
Whole Number	Yes	Yes	All comparison operators
Decimal / Float	Yes	Yes	All comparison operators
Currency	Yes	Yes	All comparison operators
Date/DateTime	Yes	Yes	All comparison operators
Lookup	Yes	Yes	Filter by GUID or primary name
Choice	Yes	Yes	= and <>
Choices (multi)	Limited	No	Contains only
Yes/No	Yes	Yes	= and <>
Formula	Yes	Depends	Depends on output type
Calculated	Yes	Yes	Treated like base type
Rollup	Yes	Yes	Treated like base type

Tips from the Field

Set max lengths realistically. A “First Name” column with 4,000 characters wastes search index capacity.
Use global choices when the same option set appears in multiple tables (e.g., Country, Department). This keeps labels consistent and simplifies reporting.
Avoid multi-select Choices for reporting-critical data. Power BI struggles with comma-separated values. Use a many-to-many relationship instead.
Test time zone behaviour before go-live. Create test records in different time zones and verify dates display correctly.
Formula columns are your friend. They replaced many calculated column use cases and are always up to date.
Document your schema. Keep a data dictionary — future you (and your colleagues) will thank you when the table has 60 columns and no one remembers what cr7a2_customfield3 does.