What Is The Difference Between Static and Dynamic SQL?

Shares

Many people are using databases but they actually don’t have an understanding of the difference between static and dynamic SQL. Their purpose is to get the output of the query either it is static or dynamic. In this article, you will learn about static vs dynamic SQL queries with a detailed discussion, how SQL statement is processed, Dynamic DBMS (Database Management System), Static vs Dynamic reports and embedded SQL example of an application.

What is Embedded SQL?

Embedded or Static SQL queries are those instructions that do not change at the execution or runtime and can be hard-coded (where data or parameters cannot be altered) into different applications.

Embedded SQL Application

Consider the example of an embedded application where the statements are mostly embedded in C++ source files where a preprocessor translates the statements into calls to a runtime library. It is portable to other environments and delivers the same functionality to each operating environment.

Embedded SQL application consists of SQL statements and these statements are converted to C or C++ code before compiling. As you can see in the diagram, application at the runtime uses SAP IQ interface library which is known as DBLIB and used to communicate with database servers. DBLIB is a dynamic link library (DLL) which can be shared on all major platforms.

What is Dynamic SQL?

As the name indicates, it is a technique that allows professionals to build SQL statements that can be changed dynamically at the runtime. A dynamic query is a statement that can be constructed at execution or runtime; for example, the application may allow users to run their own queries at execution.

What is Dynamic Database Management System?

Dynamic DBMS is a database having “value-based” relationships of objects, which is specified at retrieval time and locations of related records are discovered during retrieval.  

Which Of The Following Statements About Static And Dynamic Reports Is Not​ Accurate?

Static And Dynamic Reports

A printed sales analysis is an example of a dynamic report is the wrong statement about the static and dynamic reports. Following statements are true about the reports:

  • Static reports are published as a PDF document or Business Intelligent (BI) documents that are fixed at the time of creation, so these reports not modified.

  • Publishing the dynamic report requires Business Intelligent (BI) applications with the access of data source when it delivered to the user.

  • Always remember, Printed sales analysis is an example of static reports.

  • Dynamic reports are Business Intelligent (BI) documents and updated at the time whenever a user makes a request for this file.

How SQL Statement is Processed?

Before proceeding next, it is necessary to understand how the Structured Query Language statements are processed. DBMS performs the following steps shown in the diagram:

How SQL Statement is Processed?

1. Parse Statement

For example, the user wants to run the following query:

SELECT P, Q, R

FROM A, B

WHERE P> 800

AND R = ‘XYZ’

The DBMS will parse the query into individual words, known as tokens, to make sure the statement is free of spelling mistakes and syntax errors. It will be done very quickly because there is no need to access the database while parsing the statement.

2. Validate Statement

Database Management System will check that the tables (in case of our example table A and B) are existed in the database (system catalog) or not? Does the user have privileges to run these statements? DBMS validates the Column names are not ambiguous and existed.

3. Optimize Statement

DBMS optimizes the statement by exploring different ways. Can an index (primary key) be used to speed a search? Should the Database Management System first apply a search condition to the first Table and then join it to the second table, or should it begin with the join and use the condition afterwards? After exploring all the possible conditions, DBMS will choose one which is more suitable. It may explore thousands of possible different ways for the same query, so it is a CPU-intensive process.

4. Access Plan or Binary Form

In this step, the access plan or binary form of SQL statements will be generated.

5. Execution of The Access Plan

At the final step, the Access plan will be executed.

Basic Differences Between Embedded and Dynamic SQL

Static Dynamic
In Static or Embedded SQL, how the database will be accessed is predetermined in the embedded SQL statement and performed by the preprocessor, where user cannot run queries on runtime.

How database will be accessed is determined at runtime and user is also able to run Structured Query Language instructions on runtime.

It is swifter and more efficient.

It is less swift and efficient.

All database queries are compiled at compile-time.

All database queries are compiled at application runtime.

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.

It is mostly used for situations where data is distributed consistently.

It is used for situations where data is distributed non uniformly.

PREPARE, EXECUTE and EXECUTE IMMEDIATE statements are not used.

PREPARE, EXECUTE and EXECUTE IMMEDIATE statements are used.

It is less flexible because of hard-coded into the application.

We can run our SQL statements at the runtime, so it is more flexible.  

Shares
Avatar

David Max

Follow me on

Comments

2 Comments on “What Is The Difference Between Static and Dynamic SQL?”

  1. Avatar Ram Charan says:

    Would you please let me know which comes under what?
    Dynamo Db vs Cassandra, I’m not sure which one of these come under which SQL.

    • Avatar David Max says:

      DynamoDB:
      DynamoDB uses a mechanism to store and retrieve data without using any (tabular relations) relational databases, so it is a NoSQL database.
      How we can access DynamoDB?
      AWS Management Console or the AWS Command Line Interface (AWS CLI) are used to send ad hoc requests to DynamoDB in order to view the results.

      Apache Cassandra:
      Apache Cassandra is a NoSQL column-oriented database and capable to handle structured, Semi-Structured and Non-Structured data.
      How we can access Apache Cassandra?
      Cassandra Query Language (CQL) is a simple interface which is used to access Cassandra.

Leave a Comment

We keep your privacy and not published your email in site

Confirm you are not a robot

Shares