Maximum amount of columns per table

[pschneider]

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

[cchannon · Accepted solution]

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.

[pschneider]

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.

[cchannon]

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.