Table Valued Parameters (TVP) SQL Server expressions restricted by Metabase row limit

Is the following a correct assessment of this issue?

TVPs are restricted by Metabase row limits such that the maximum number of rows that can be inserted into the underlying User-Defined-Table-Type is 2,000 records:

CTEs have no such restriction:

Since Metabase does not support SQL Server as its internal database (non-open source licensing issues with doing that) Table Valued Parameters (TVP) should be reformulated as something more common to open source database engines such as views and Common Table Expressions (CTE).

From the post Metabase Rows Limitation it would seem the only other way around this issue is to increase the 2,000 row limitation per the GitHub issue Create admin-overridable constants #4564. Even so the admin would have to put in a sufficiently large number and this would inevitably have consequences on performance.


Having recently been bit in the behind by this issue and worked around it (so far) with views and CTEs, I figured at least posting it to this forum might flag other solutions in case I or anyone else gets into this kind of bind. TVPs are great in development for allot of things including zeroing in on bottlenecks because unlike CTEs they keep each layer separate. So I thought nothing of it just plopping them into Metabase. Only when putting together time-series did I notice that the further back the minimum year requested was put, the more and more recent years that were not showing up to the party! And sure enough, if any TVP in the chain exceeds 2,000 records the limit kicks in no matter how few records are in the final sql!

Metabase: v0.33.2
Host: Localhost:3000
Databases: H2 internal default, Server SQL added
Browser: Firefox 52.9.0
OS: Microsoft Windows [Version 6.0.6002]
Java: 1.8.0_45

Hi @mesquest
You’re likely hitting this issue, specific to MSSQL:
You can remove that part of the driver and build your own, then there shouldn’t be any limits on sub-queries.

1 Like