How to Enable Copilot Agent to Query Large Datasets from SQL?

Question

Hello,

I'm using SQL tables from a Synapse Dedicated Pool as a knowledge source in a Copilot agent. The datasets are quite large, but I've noticed that the Copilot agent only queries up to 2,048 rows (tested across multiple tables).

How can I configure my Copilot agent to query the entire dataset and generate accurate responses?

Note: The data is structured (row-based), which is why I’m not considering Azure AI Search for this scenario.

Any insights or recommendations would be greatly appreciated!

Answer 1

Hello,
Microsoft Copilot Studio limits SQL or Synapse knowledge-source retrieval to 2,048 rows per table, and this limit cannot be configured or increased. The platform does not perform live SQL queries; instead, it samples rows (up to ~2,048), converts them into text, and uses them for RAG-style grounding. This design exists because large language models are not intended to reason over very large structured datasets. As a result, Copilot Studio does not support pagination, full-table queries, or text-to-SQL execution when SQL/Synapse is used as a knowledge source.

For large datasets, Microsoft recommends alternative architectures such as exposing the database through APIs or tools (e.g., Azure Functions, custom connectors, or MCP servers) so the agent can query only the required data. Other options include pre-aggregating data to stay under the row limit or using a hybrid approach where SQL knowledge provides schema context while APIs retrieve actual data. This approach ensures scalability, accuracy, and proper handling of large structured datasets.
This behavior is by design.

Answer 2

Hello,
Microsoft Copilot Studio limits SQL or Synapse knowledge-source retrieval to 2,048 rows per table, and this limit cannot be configured or increased. The platform does not perform live SQL queries; instead, it samples rows (up to ~2,048), converts them into text, and uses them for RAG-style grounding. This design exists because large language models are not intended to reason over very large structured datasets. As a result, Copilot Studio does not support pagination, full-table queries, or text-to-SQL execution when SQL/Synapse is used as a knowledge source.

For large datasets, Microsoft recommends alternative architectures such as exposing the database through APIs or tools (e.g., Azure Functions, custom connectors, or MCP servers) so the agent can query only the required data. Other options include pre-aggregating data to stay under the row limit or using a hybrid approach where SQL knowledge provides schema context while APIs retrieve actual data. This approach ensures scalability, accuracy, and proper handling of large structured datasets.
This behavior is by design.

Answer 3

Additional question in regard to this . Does it use text to sql query in the backend or it vectorize the data in the backend?