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.
Using Static SQL has a benefit which is the optimization of the statement that results an application with high performance as it offers a good flexibility better than Dynamic SQL, and since access plans for dynamic statements are generated at run-time so they must be prepared in the application, and this is something you will never look at in the static SQL, but these are not the only differences between them, so we can say that dynamic SQL has only one advantage over static statements which can be clearly noticed once the application is edited or upgraded, so with Dynamic statements there’s no need for pre-compilation or re-building as long as the access plans are generated at run-time, whereas static statements require regeneration of access plans if they were modified, in addition to the fact that Dynamic SQL requires more permissions, it also might be a way to execute unauthorized code, we don’t know what kind of users we’ll have, so for security it can be dangerous if the programmer didn’t handle it.
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.
- Difference between Static and Dynamic Routing
- Difference between Static and Dynamic Web Pages
- Difference between Static and Dynamic IP address
- Difference between static and non-static variables in Java
- Difference between Static and Shared libraries
- Difference between var and dynamic in C#
- Difference between Fixed and Dynamic Channel Allocations
- Difference between Static variables and Register variables in C
- Internal static variable vs. External static variable with Examples in C
- Difference between Basic Disk and Dynamic Disk
- Static type in Cassandra
- Static type using batch in Cassandra
- Greedy approach vs Dynamic programming
- Extendible Hashing (Dynamic approach to DBMS)
- Differences between Dynamic Binding and Message Passing in Java
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.
Improved By : NouraElAllam