Difference between Static SQL and Dynamic SQL

MySQLDatabaseMySQLi

Static SQL

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

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.KeyStatic SQLDynamic SQL
1Database AccessIn 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.
2EfficiencyStatic SQL statements are more faster and efficient.Dynamic SQL statements are less efficient.
3CompilationStatic SQL statements are compiled at compile time.Dynamic SQL statements are compiled at run time.
4Application PlanApplication Plan parsing, validation, optimization and generation are compile time activities.Application Plan parsing, validation, optimization and generation are run time activities.
5Use CasesStatic SQL is used in case of uniformly distributed data.Dynamic SQL is used in case of non-uniformly distributed data.
6Dynamic StatementsStatements like EXECUTE IMMEDIATE, EXECUTE, PREPARE are not used.Statements like EXECUTE IMMEDIATE, EXECUTE, PREPARE are used
7FlexibilityStatic SQL is less flexible.Dynamic SQL is highly flexible.
raja
Published on 15-Apr-2020 10:27:27
Advertisements