What is an embedded and dynamic SQL?
SQL queries can be of two types i.e. embedded or static SQL and dynamic SQL. So, in this blog, we will be learning about these two types of SQL statements.
Embedded / Static SQL
Embedded or Static SQL is those SQL statements that are fixed and can't be changed at runtime in an application. These statements are compiled at the compile-time only. The benefit of using this statement is that you know the path of execution of statements because you have the SQL statements with you, so you can optimize your SQL query and can execute the query in the best and fastest possible way. The way of accessing the data is predefined and these static SQL statements are generally used on those databases that are uniformly distributed.
These statements are hardcoded in the application, so if you want to build some application in which you need some dynamic or run-time SQL statements, then you should use the Dynamic SQL statement.
Dynamic SQL statements are those SQL statements that are created or executed at the run-time. The users can execute their own query in some application. These statements are compiled at the run-time. These kinds of SQL statements are used where there is a non-uniformity in the data stored in the database. It is more flexible as compared to the static SQL and can be used in some flexible applications.
Since the compilation is done at run-time, the system will know how to access the database at run-time only. So, no proper planning for execution and optimization can be done previously. This will reduce the performance of the system. Also, if you are taking the database query from the user at run-time, then there are possibilities that the users might enter some wrong queries and this is very dangerous because here you are dealing with lots and lots of data.
If you want to make a flexible application then you can use dynamic SQL, but make sure that your users are professionals and trained. Otherwise, you should go for static or embedded SQL. This is more efficient as compared to the dynamic SQL.
That's it for this blog.
Keep Learning :)