Know Your Project’s Coding Syntax

Knowing the rules that guide code development—known as Coding Standards—are important in a new project. Syntax is commonly covered in code reviews, but before reaching that stage, it is essential for the team to follow certain rules and conventions.

For the purposes of our discussion, we will address SQL, since this programming language is both specifically designed and commonly used for managing data in a relational database management system.

The “Coding Standards” is used to describe a wide range of guidelines in a coding project. This includes, for example, valid characters or functions. Having these guidelines prevents the project from having errors and returning undesired results, which come from having statements that are not structured properly.

Think of syntax as the manifestation or application of the desired logic in code.

This includes particular statements such as WHERE or UPDATE and clauses such as ON or ORDER BY.

Coding Standards may cover something as seemingly fundamental as declaring that a project will use ANSI Standard SQL.

Many database administrators may automatically assume Transact-SQL [T-SQL] is the same as Procedural Language [PL/SQL].

According to the ANSI/ISO/IEC International Standard , ANSI Standard SQL has several Language elements that compose its “Syntax”:

• Clauses: Which are constituent components of statements and queries.
• Expressions: Which can produce either scalar values, or tables consisting of columns and rows of data.
• Predicates: Which specify conditions that can be evaluated to SQL.
• Queries: Which retrieve the data based on specific criteria.
• Statements: Which may have a persistent effect on schemata and data, or may control transactions, program flow, connections, or sessions.
• Whitespace: Makes it easier to format SQL code for readability.

Coding Standards that relate to the database usually groups like below that may or may not have examples. Also I have seen a general statement such as:

“Use ANSI Standard SQL where applicable, all deviations or special cases seek approval from the appropriate manager prior to code review.”

Data Definition Language (DDL): Statements define the database structure or schema.

1. CREATE: To create objects in the database
2. ALTER: Alters the structure of the database
3. DROP: Delete objects from the database
4. TRUNCATE: Remove all records from a table, including all spaces allocated for    the records.
5. COMMENT: Add comments to the data dictionary
6. RENAME: Rename an object

Data Manipulation Language (DML): Statements for managing data within schema objects.

1. SELECT: Retrieve data from the a database
2. INSERT: Insert data into a table
3. UPDATE: Updates existing data within a table
4. DELETE: Deletes records from a table
5. MERGE: Operation
6. EXPLAIN PLAN: Explain access path to data
7. LOCK TABLE: Control concurrency

Data Control Language (DCL): Statements for managing access control

1. GRANT: Gives user’s access privileges to database
2. REVOKE: Withdraw access privileges given with the GRANT command

Transaction Control (TCL): Statements for managing transactions.

1. COMMIT: Save work done
2. SAVEPOINT: Identify a point in a transaction to which you can later roll back
3. ROLLBACK: Restore database to original since the last COMMIT
4. SET TRANSACTION: Change transaction options like isolation level and what rollback segment to use

The above is very basic and the actual Coding Standards can look very different. Depending on the rigidity of the coding standards; the location of comments, whitespace and indents may also be outlined.

You should now have some idea how the Coding Standards of an organization can aid you in your project code development effort. If you have questions about how to integrate these ideas into your operations, Datavail can work with you and your organization to effectively leverage SQL for your benefit and that of your clients and customers.

To learn more about our remote database services and how our experts can help with your ongoing SQL operations, please contact Datavail to discuss a custom solution designed for your enterprise.