Based on the value of the UpdateBatchSize, the TdDataAdapter will determine how the SQL-Statements will be sent to the Teradata Database. One of three methods is used:

1. Single-Statement Request: The SQL Statements are sent to the Teradata Database one at a time when UpdateBatchSize is set to 1.
2. Multi-Statement Request: Two or more SQL Statements separated by semicolons are sent to the Teradata Database in one Request when UpdateBatchSize is set to 0 or to a number greater than 1. The number of SQL-Statements included in the Multi-Statement Request is limited by:
   • The maximum number of Parameters in one Request: approximately 2,500.
   • The maximum length of data and SQL-Text associated with one Request: approximately 1MB although, Teradata Database version 16.0 has increased that limit.
   Refer to the "Teradata System Limits" [Appendix B] section of the Teradata Database Design guide for the exact limits.
3. Iterated Requests (a.k.a. Parameter Array): A single SQL-Statement with multiple rows of data is sent to the Teradata Database when UpdateBatchSize is set to 0 or to a number greater than 5.
   The request size limit mentioned above for Multi-Statement Request is also applicable to Iterated Requests but there is more room for Data, since the SQL-Text (CommandText) for a single SQL-Statement takes up less space within that Request.
   There is also a limit of 64,000 rows per request for Teradata Database 16.0, or 16,383 rows for earlier database versions.

The TdDataAdapter determines whether the SQL statements will be sent as a Multi-Statement or Iterated request using the following rules:

• When the property KeepCommandBatchSequence is set to true, the SQL statements will always be sent as a Multi-Statement Request.
• If KeepCommandBatchSequence is false and UpdateBatchSize is set to a number between 1 thru 5, the SQL statements will be sent as a Multi-Statement Request.
• If KeepCommandBatchSequence is false and the number of modified DataRows is 5 or less, the SQL statements will be sent as a Multi-Statement Request.
• Iterated Requests will be used when KeepCommandBatchSequence is false, and both the property UpdateBatchSize and the number of modified (CUD) DataRows is greater than 5, and TdParameter properties (DbType, TdType, Size, Precision and Scale) are specified. The CommandText must be the same. The following are some examples:
  • 10 DataRows were modified. 7 DataRows result in UPDATE statements that modify the same columns and 3 DataRows result in DELETE statements. Iterated Request will be used when submitting the data for the UPDATE DataRows while Multi-Statement Request will be used for the DELETE statements.
  • 8 DataRows were modified. Three statements are UPDATE that modify the same columns, three are DELETE, and two are INSERT. A multi-statement request will be used to update the Teradata Database.

In summary the Data Provider uses an internal algorithm to select Iterated Request vs. Multi-Statement Request when a) KeepCommandBatchSequence is set to false and b) TdCommandBuilder is used to generate DML statements or TdParameter properties (TdType, Size, Precision and Scale) are set correctly.