What is the difference between embedded and dynamic SQL?
Embedded and dynamic SQL are two of the most commonly used types of SQL. Each one has its own set of properties and attributes, which makes it better suited for a particular usage. The following is the list of the primary differences between Embedded and Dynamic SQL.
Embedded SQL |
Dynamic SQL |
It is hardcoded in the program itself and the changeable value is the value in the host variable. |
Has the capability of changing the columns, tables during the execution of the program. |
How the database will be accessed is predetermined in the SQL statement. |
How database will be accessed is determined at run time. |
Faster and efficient. |
Slower and less efficient. |
Less flexible |
More flexible |
Contain host variables |
Does not contain any host variables |
SQL statements are compiled at compile time |
SQL statements are compiled at run time. |
Parsing, validation, optimization, and generation of application plan are done at compile time |
Parsing, validation, optimization, and generation of application plan are done at run time. |
Better suited for situations where data is distributed uniformly. |
Better suited for situations where data is distributed non-uniformly. |
EXECUTE IMMEDIATE, EXECUTE and PREPARE statements are not used. |
EXECUTE IMMEDIATE, EXECUTE and PREPARE statements are used. |
The BIND is done prior to the execution and is stored in a PLAN. |
The BIND occurs at execution time |
Access Path is generated during BIND time. |
Access path is generated during Run-time |
If VALIDATE(BIND) is used, the table authorizations are checked during BIND (). |
Table authorizations can only be checked during run-time. |
Add new comment