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.