Simplifying IT, Empowering Businesses

Indexes

Introduction:

• An index is a data structure that improves the speed of operations in a table.
• We can create an index using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.
• Without an index, SQL must begin with the first row and then read the entire table to find the relevant rows.
• The larger the table, the more the cost. If a table has an index for the column in question, SQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data.
• If a table has 10000 rows, this is the least 1000 times faster than reading sequentially.

Usage of Indexing:

• Let us understand this with an example:
• If you had a book on databases and indexed the word “database”, you would see that it is mentioned on pages 1-70, 70-190, and 191 to 300. In such a case, the index is not much help and it might be faster to go through the pages one by one (in a database, this is “poor selectivity”).
• For a 10-page book, it makes no sense to make an index, as you may end up with a 10-page book prefixed by a 5-page index.
• The index also needs to be useful – there is generally no point to index e.g. the frequency of the letter “L” per page.

Advantages:

• Use of Indexing usually results in much better performance.
• They make it possible to quickly fetch data.
• They can be used for sorting. A post-fetch-sort operation can be eliminated.
• Unique indexes guarantee uniquely identifiable records in the database.

Disadvantages:

• They decrease performance on inserts, updates, and deletes.
• They take up space (these increases with the number of fields used and the length of the fields).
• You should only create indexes when they are actually needed.
• Take care not to add an index on something that has already been indexed. If you need a more detailed index, you can add fields to an existing index as long as it is not a unique index.

Stored Procedure

Introduction:

• A stored procedure is a bunch of SQL commands that can compile and stored in the server.
• It includes all the statements of SQL, but we can also use if, then, else blocks and while loops.
• SQL Server, My SQL etc. have many built-in stored procedures, called System Stored Procedures, which are, used for maintenance and management activities, they start with ps_suffix.
• They can accept input parameters.
• They can return various output types.

Syntax:

CREATE [OR REPLACE] PROCEDURE <ProcedureName> (<ParamList>)

<localDeclarations>

Begin

<procedureBody>;

End;

/

ParamList: A parameter in the paramList is specified below

<name> <mode> <type>

Mode:

• IN => Input Parameter (Default)
• OUT => Output Parameter
• INOUT => Input and Output Parameter

Stored Procedure Structure in MYSQL:

Stored Procedure Example in MYSQL:

Write a stored procedure that receives as input the id of an employee and displays his name and his salary. Then call this procedure for the employee id=2
Employee (id, FNAME, LNAME, SALARY)

Value Assignments

• We can set a value into a variable
• Its value last for a specific session
• The name should start with a “@”
• The value assignment is done using the SET command
• SET @x=4;
• SET @y=7;
• SET @z=@x-@y;
• We can print the value of a variable using the select command, e.g:
• Select @x;

Declarations in a SP

• We can create a variable in a procedure.
• Each variable lasts for the specific session
• We can use “DECLARE” command to declare a variable
• We have to declare its data type e.g:
• DECLARE id INT;
• DECLARE name VARCHAR(20);
• DECLARE birthday DATETIME;
• The declarations should be made at the beginning of the stored procedure

Advantages

• Code Reusability
• SQL Server compiles and stores these in memory for future use
• Enhanced Security
• Requires valid permissions to execute
• Users can have permission to execute a stored procedure, without having permission to read from the underlying tables or views
• You can specify the security context
• Owner, Caller or User
• Speed / Optimization
• The stored procedures are cached on the server

Disadvantages

• Limited Coding Functionality
• The stored procedure code is not as robust as app code, particularly in the area of looping (not to mention that iterative constructs, like cursors, are slow and processor intensive)
• Testing
• Any data errors in handling Stored Procedures are not generated until runtime
• Portability
• Complex Stored Procedures that utilize complex, the core functionality of the RDBMS used for their creation will not always port to upgraded versions of the same database. This is especially true if moving from one database type (Oracle) to another (MS SQL Server).

Trigger

Introduction:

A database trigger is a stored PL/SQL program unit attached with a specific database table. ORACLE executes (fires) a database trigger automatically when a given SQL operation (like INSERT, UPDATE or DELETE) affects the table. Unlike a procedure, or a function, which must be invoked explicitly, database triggers are invoked implicitly.

Database triggers can be used to perform any of the following:

• Audit data modification
• Log events transparently
• Enforce complex business rules
• Derive column values automatically
• Implement complex security authorizations
• Maintain replicate tables

You can associate up to 12 database triggers with a given table. A database trigger has three parts: a triggering event, an optional trigger constraint, and a trigger action. When an event occurs, a database trigger is fired, and a predefined PL/SQL block will perform the necessary action. The owner, not the current user, must have appropriate access to all objects referenced by the trigger action.

Basic Commands

• Creation
• CREATE TRIGGER
• Delete
• DROP TRIGGER < trigger name >
• Show Trigger Code
• SHOW CREATE TRIGGER < trigger name >
• Show list of created Triggers
• SHOW TRIGGERS
• Call/execute a trigger
• We cannot call a trigger, like a procedure
• It is executed when an event happens to call it

Designing a Trigger

• To design a trigger, we have to determine:
• In which table it would be applied
• With what event it will be linked
• e.g. INSERT, UPDATE, DELETE
• When it will be executed
• Before the event
• After the event
• It’s functionality
• At the main body of the trigger, we can write SQL code

Trigger Creation

CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name
FOR EACH ROW trigger_body

• trigger_name
• trigger_time -> When it will be executed (after or before)
• trigger_event -> the connected event
• ON table_name -> the table it belongs to
• FOR EACH ROW -> it will be executed for each row
• trigger_body -> the SQL scripts

Trigger Events

• The Triggers events could be
• INSERT
• UPDATE
• DELETE
• TRUNCATE & DROP don’t call a Trigger
• We could have at max 6 triggers in every table

Trigger Example