Workspace database queries that hold up on real data

Hosted workspace databases refuse a query past a few fixed limits, and each of them hides during testing. An app that looked up one row per price-list line worked on a short test list and failed on the real one. Specialists now write queries that stay inside the limits however much data arrives, and when a query does go over, the error says how to fix it.

How it works

• Long lists go in as one value. A lookup by hundreds of ids passes the whole list as a single JSON value instead of one placeholder per id, so it works past the limit of 100 values per statement.
• Search that takes long phrases. A search box matches with a substring check rather than a LIKE pattern, which is capped at 50 bytes and fails once someone searches for a long phrase.
• Wide imports fit. A table holds at most 100 columns, so a wide spreadsheet keeps its extra fields together in one JSON column.
• Errors that name the fix. A query refused for any of these limits, or for a statement over 100 KB, comes back with the limit it hit and the way around it, so the agent corrects its own query.

Also shipped

• Several statements, one transaction. A query of several statements now runs all of them or none and answers with the last statement's rows, where before only the first statement's rows came back.
• SQL Server text stays as written. A dollar amount inside quoted text, such as 'Save $10', is no longer mistaken for a query parameter on connected SQL Server databases.