What is the difference between embedded and dynamic SQL?

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

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.