Static SQL refers to those SQL statements which are fixed and can be hard coded into the application. As static sqls are fixed queries, these statements can be analysed and optimized and do not require any specific handling for security purpose.
Dynamic SQL refers to those SQL statements which are generated dynamically based on user's input and run in the application. Dynamic Sqls helps to develop general and flexible applications. Dynamic SQL may need more permissions and security handling and a malicious user can create dangerous code as well.
Following are some of the important differences between Static Routing and Dynamic Routing.
|Sr. No.||Key||Static SQL||Dynamic SQL|
|1||Database Access||In Static SQL, database access procedure is predetermined in the statement.||In Dynamic SQL, how a database will be accessed, can be determine only at run time.|
|2||Efficiency||Static SQL statements are more faster and efficient.||Dynamic SQL statements are less efficient.|
|3||Compilation||Static SQL statements are compiled at compile time.||Dynamic SQL statements are compiled at run time.|
|4||Application Plan||Application Plan parsing, validation, optimization and generation are compile time activities.||Application Plan parsing, validation, optimization and generation are run time activities.|
|5||Use Cases||Static SQL is used in case of uniformly distributed data.||Dynamic SQL is used in case of non-uniformly distributed data.|
|6||Dynamic Statements||Statements like EXECUTE IMMEDIATE, EXECUTE, PREPARE are not used.||Statements like EXECUTE IMMEDIATE, EXECUTE, PREPARE are used|
|7||Flexibility||Static SQL is less flexible.||Dynamic SQL is highly flexible.|