What are the max number of allowable parameters per database provider type?

by

Oracle: 64,000. Source

MySQL:

  • By default, there is no limit. The MySQL “text protocol” requires that the .NET client library substitute all parameters before sending the command text to the server; there is no server-side limit that can be enforced, and the client has no limit (other than available memory).
  • If using “prepared statements” by calling MySqlCommand.Prepare() (and specifying IgnorePrepare=false in the connection string), then there is a limit of 65,535 parameters (because num_params has to fit in two bytes).

PostgreSql: EDIT: 34464 for a query and 100 for a function as per Magnus Hagander’s answer (Answer copied here to provide a single point of reference)

SqlLite: 999 (SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999, but can be lowered at runtime) – And for functions default is 100 parameters. See section 9 Of Run-time limits documentation

In jOOQ, we’ve worked around these limitations by inlining bind values once we reach the relevant number per vendor. The numbers are documented here. Not all numbers are necessarily the correct ones according to vendor documentation, we’ve discovered them empirically by trial and error through JDBC. They are (without tying them to a specific version):

  • Ingres : 1024
  • Microsoft Access : 768
  • Oracle : 32767
  • PostgreSQL : 32767
  • SQLite : 999
  • SQL Server : 2100 (depending on the version)
  • Sybase ASE : 2000

Other databases do not seem to have any limitations – at least we’ve not discovered them yet (haven’t been looking far beyond 100000, though).

More Related Contents:

  1. SQL: Repeat a result row multiple times, and number the rows
  2. Why do results from a SQL query not come back in the order I expect?
  3. Relationship between catalog, schema, user, and database instance
  4. What resources exist for Database performance-tuning? [closed]
  5. How to select the nth row in a SQL database table?
  6. How do I decide when to use right joins/left joins or inner joins Or how to determine which table is on which side?
  7. Best practices for SQL varchar column length [closed]
  8. How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
  9. how to cast the hexadecimal to varchar(datetime)?
  10. Why in SQL NULL can’t match with NULL?
  11. In SQL Server how to get the count of child if both parent and child are available [closed]
  12. SQL Print the name of all Employees together with the name of their supervisor
  13. Comparison of full text search engine – Lucene, Sphinx, Postgresql, MySQL? [closed]
  14. Stored Procedure with optional “WHERE” parameters
  15. The equivalent of SQLServer function SCOPE_IDENTITY() in mySQL?
  16. Is there a Profiler equivalent for MySql? [closed]
  17. How to export data from SQL Server 2005 to MySQL [closed]
  18. MySQL Aggregate Functions without GROUP BY clause
  19. mysql equivalent data types
  20. Converting SELECT DISTINCT ON queries from Postgresql to MySQL
  21. How to transpose rows to columns with large amount of the data in BigQuery/SQL?
  22. Are SELECT type queries the only type that can be nested?
  23. Is there an SQLite equivalent to MySQL’s DESCRIBE [table]?
  24. How to migrate SQL Server database to MySQL? [closed]
  25. Is there a way to get the row number in Mysql like the rownum in oracle [duplicate]
  26. Postgres LEFT JOIN with WHERE condition
  27. What does SQL Select symbol || mean?
  28. How do you write a case insensitive query for both MySQL and Postgres?
  29. Foreign key to one of many tables?
  30. What happens when I exhaust a bigint generated key? How to handle it?

Leave a Comment