Difference Between Dynamic And Static SQL With Examples

by

What Is Static SQL?

Static SQL are statements in an application that do not change at runtime (when source code is written) and therefore can be hard-coded into the application.  Static SQL is referred to as static because the SQL statements in the program do not change each time the program is run.

Static SQL is generally used in embedded SQL database application. The source must be processed by the DB2 database manager using a SQL precompiler before it can be compiled and executed. During this process, the SQL precompiler evaluates references to tables, columns and declare data types of all host variables and determines which data conversion methods need to be used when data is moved to and from the database.

SQL statements are generally easy to use; however, they are limited because their format must be known in advance by the precompiler and because they can only work with host variables.

Static SQL can be executed from the following interfaces:

  • Embedded SQL applications
  • Embedded SQL routines
  • SQLJ applications
  • SQLJ routines
  • SQL routines

What You Need To Know About Static SQL

  • In Static or Embedded SQL, how the database will be access procedure is predetermined in the embedded SQL statement and performed by the preprocessor where user cannot run queries on runtime.
  • All processes including Parsing, validation, optimization and generation of application access plan are done at compile time.
  • Statements like EXECUTE IMMEDIATE, EXECUTE PREPARE are not used.
  • Static SQL statements are compiled at compile time.
  • Static SQL is used in the event of uniformly distributed data.
  • Static SQL statements are faster and efficient.
  • Static SQL is less flexible.

What Is Dynamic SQL?

Dynamic SQL is generally used for sending SQL statements to the database manager from interactive query building graphical user interfaces and SQL command line processors as well as from applications where the complete structure of queries is not known at application compilation time and the programming API supports dynamic SQL.

Unlike static SQL statements which are hard-coded in the program, Dynamic SQL can be built at runtime and placed in a string host.  They are then sent to the database management system for processing. With Dynamic SQL, you can create more general purpose, flexible applications by using SQL because the full text of an SQL statement may be unknown at compilation. 

Usually, Dynamic SQL is generally slower than static SQL because the database management system has to create an access plan at runtime for dynamic SQL statements.  However, when a program containing dynamic SQL statements is compiled, the dynamic SQL statements are not stripped from the program, as in Static SQL, instead, they are replaced by a function call that passes the statement to the database management system (DNMS).

Dynamic SQL can be used from the following interfaces:

  • DB2 Command Windows
  • DB2 Command Line Processor
  • Interactive DB2 GUI interfaces including DB2 Command Editor in the DB2 Control Center.
  • Applications and external routines that employ APIs that support dynamic SQL including: Embedded SQL, JDBC, CLI and ADO.NET

What You Need To Know About Dynamic SQL

  • In Dynamic SQL, how a database is accessed is determined at runtime and user is also able to run Structured Query Language instructions on runtime.
  • All processes including Parsing, Validation, Optimization and Generation of application access plan (Binary form of SQL queries) are done at runtime.
  • Statements like EXECUTE IMMEDIATE, EXECUTE PREPARE are used.
  • Dynamic SQL statements are compiled at run time.
  • Dynamic SQL is used in the event of non-uniformly distributed data.
  • Dynamic SQL statements are less efficient.
  • Dynamic SQL is highly flexible.

Also Read: Difference Between Union And Join In SQL

Difference Between Dynamic And Static SQL In Tabular Form

BASIS OF COMPARISON STATIC SQL DYNAMIC SQL
Description In Static or Embedded SQL, how the database will be access procedure is predetermined in the embedded SQL statement and performed by the preprocessor where user cannot run queries on runtime.   In Dynamic SQL, how a database is accessed is determined at runtime and user is also able to run Structured Query Language instructions on runtime.  
Compilation Of Necessary Processes All processes including Parsing, validation, optimization and generation of application access plan are done at compile time.   All processes including Parsing, Validation, Optimization and Generation of application access plan (Binary form of SQL queries) are done at runtime.  
Statements Statements like EXECUTE IMMEDIATE, EXECUTE PREPARE are not used.   Statements like EXECUTE IMMEDIATE, EXECUTE PREPARE are used.  
Compilation Of Statements Static SQL statements are compiled at compile time.   Dynamic SQL statements are compiled at run time.  
Use Static SQL is used in the event of uniformly distributed data.   Dynamic SQL is used in the event of non-uniformly distributed data.  
Efficiency Static SQL statements are faster and efficient.   Dynamic SQL statements are less efficient.  
Flexibility Static SQL is less flexible.   Dynamic SQL is highly flexible.  

Also Read: Difference Between Normalization And Denormalization