Static or Embedded SQL are SQL statements in an application that do not change at runtime and, therefore, can be hard-coded into the application. Dynamic SQL is SQL statements that are constructed at runtime; for example, the application may allow users to enter their own queries.
Dynamic SQL is a programming technique that enables you to build SQL statements dynamically at runtime. You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation.
Below mentioned are the basic differences between Static or Embedded and Dynamic or Interactive SQL:
Static (Embedded) SQL | Dynamic (Interactive) SQL |
---|---|
In Static SQL, how database will be accessed is predetermined in the embedded SQL statement. | In Dynamic SQL, how database will be accessed is determined at run time. |
It is more swift and efficient. | It is less swift and efficient. |
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. |
It is generally used for situations where data is distributed uniformly. | It is generally used 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. |
It is less flexible. | It is more flexible. |
Limitation of Dynamic SQL:
We cannot use some of the SQL statements Dynamically.
Performance of these statements is poor as compared to Static SQL.
Limitations of Static SQL:
They do not change at runtime thus are hard-coded into applications.
leave a comment
0 Comments