Difference between Static (Embedded) and Dynamic (Interactive) SQL : DBMS

Difference between Static and Dynamic SQL


-> 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.


-> In static SQL, the structure of the statement will remain the same, but with dynamic SQL, it may change.

In your example, you can use a parameter for the country variable to have dynamic SQL.

STATIC  SQL	DYNAMIC 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.

Limitations of Dynamic SQL -

1. Performance of these statements is poor as compared to Static SQL.

2. We cannot use some of the SQL statements Dynamically.

Limitations of Static SQL -

1. They do not change at runtime thus are hard-coded into applications.