cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pschneider
New Member

Maximum amount of columns per table

Hey all,

 

is there a maximum amount of allowed columns per table in Dataverse? 

Like the limitation within SharePoint list?

 

I didn't find anything within Microsoft documentation.

 

Thanks in advance and many greetings,

Patrick

1 ACCEPTED SOLUTION

Accepted Solutions
cchannon
Solution Sage
Solution Sage

Yes, the maximum is constrained by the maximum number of columns in a SQL table (1024), but it is not 1024; it will always be less than that. This is for two reasons:

  • OOB when you create a table, several columns are added, and not all of them are visible to you (metadata)
  • Some column types actually are composites of more than one column in the database. Lookups, for example, need a column for Name, ID, and ETN, and (I assume) the new polymorphic lookups increase that number geometrically.

So, there is no hard and fast number for the limit because it varies from table to table based on the columns you add. BUT, you can always be certain it is something less than 1024.

 

Now let me take a step back for a minute. Normalized data structures may not be the best answer for every situation, but if you EVER need to get anywhere near the max columns per table, you have almost certainly failed enormously in your design. The wider the tables, the slower the transactions, and the harder it is to perform reporting. You should NEVER have to worry about that max because--simply put--if you ever do, there MUST have been a better way.

View solution in original post

3 REPLIES 3
cchannon
Solution Sage
Solution Sage

Yes, the maximum is constrained by the maximum number of columns in a SQL table (1024), but it is not 1024; it will always be less than that. This is for two reasons:

  • OOB when you create a table, several columns are added, and not all of them are visible to you (metadata)
  • Some column types actually are composites of more than one column in the database. Lookups, for example, need a column for Name, ID, and ETN, and (I assume) the new polymorphic lookups increase that number geometrically.

So, there is no hard and fast number for the limit because it varies from table to table based on the columns you add. BUT, you can always be certain it is something less than 1024.

 

Now let me take a step back for a minute. Normalized data structures may not be the best answer for every situation, but if you EVER need to get anywhere near the max columns per table, you have almost certainly failed enormously in your design. The wider the tables, the slower the transactions, and the harder it is to perform reporting. You should NEVER have to worry about that max because--simply put--if you ever do, there MUST have been a better way.

View solution in original post

Thank you for your reply.

Regarding the table design and max column amount you're absolutely right to first of all change the basement of the data structure / table design.

So if you mention the technical limits of a SQL table (1024) are also valid for Dataverse, does this also apply for other technical limitations within Dataverse? Or in other words: Is there a Microsoft documentation where Dataverse is technically described and limitation details are documented?

 

Thanks in advance.

The SQL column count limitation applies because the product--at its heart--has long been a SQL server. That seems likely to be evolving in the online world (to something SQL-like, but not exactly the same thing customers can buy from MSFT?), but back in the onprem days you could truly interact with the underlying db, which was just commercial Sql Server. Anyway, I wouldn't count on SQL documentation as a good place to get other constraint information, just because MSFT is likely breaking lots of their own limits simply because they can. 

 

MSFT documentation does list other limits for the system, though they aren't aggregated into one convenient list because 'limits' can take many forms such as API querying limits, or miscellaneous operational limits. You'll just have to search for whether or not known constraints exist as each question comes up.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,941)