This chapter is from the book
This chapter is from the book
This chapter is from the book
Relational Databases and SQL
One of the major benefits of relational databases is that they virtually all use standard SQL for a query language. Initially it was hoped that with SQL, applications that were developed to work with a database from one vendor could easily be ported to work with a database from another vendor. But that has not been the case. Database vendors, in an effort to distinguish themselves from one another, have extended the SQL language in many ways.
The extensions to SQL have been both problematic and beneficial. They have been problematic in that a standard was being extended by vendors and thus reducing the benefit of having a standard. But they have been a benefit in that the extensions were often very useful (Oracle's decode statement, for example).
Part of the extensions to existing SQL implementations are the Stored Procedure Languages (SPL). Since SQL is a nonprocedural language, it has difficulties managing certain complex operations where many layers of logic must be applied, such as applying complex business rules to large amounts of report data. SPLs are procedural languages like C or Java and can manage these complex logical operations by providing procedural language facilities, such as conditional statements and flow of control operators, and the ability to declare methods or functions.
These SPL implementations are complete programming languages that are implemented within the database engine. It may seem that the inclusion of a programming language in the database engine is redundant and unnecessary when we are working with a full-fledged programming language like Java. But the advantage of using an SPL to perform data processing is that the processing is done in the database engine. The data used in the SPL procedure resides in the memory space of the database engine, so there is no need to move the data across the network to a program in order to perform the processing. While this performance advantage may not be significant for the processing of 2,000 small rows of data, it does become significant where large pools of data are being processed—for example, the processing of a million rows of data. With large blocks of data, the use of an SPL can mean the difference between only 1 hour of processing for a million rows using an SPL procedure and 8 hours of processing required to extract the data from the database and process it within a program.
Many relational databases also provide database triggers. These triggers are associated with a database table and initiate various actions when database activity takes place against the table. Database update triggers, probably the most common type of trigger, are executed when a database insert, update, or delete is run against a database table. These triggers are an excellent means of enhancing database integrity and can be used to enforce business rules, replicate data, and provide auditing type facilities by logging table updates.
Other important extensions to relational databases include data fragmentation where data for a table is distributed across separate logical devices, thus improving performance for scans of a large number of rows from the table. Also, database replication where two different database servers running on two different machines remain completely synchronized provides significant benefits.