Content

• Temp Variables in SQL
• SQL Indexes
• SQL Views
• SQL Triggers

Temp Variables in SQL

Temp Variables are also used for holding data temporarily just like a temp table. Temp Variables are created using a “DECLARE” statement and are assigned values using either a SET or SELECT command.

Declare @My_vari TABLE  
            (  
               IID int,  
               Name Nvarchar(50),  
               Salary Int ,  
               City_Name Nvarchar(50)  
            )

We can use all the same methods of insertion that are used for tables but we cannot use a “SELECT INTO” statement for Temp Variables.

---

SQL Indexes

• Indexes are used by queries to find data from tables quickly. Indexes are created on tables and views. Index on a table or a view, is very similar to an index that we find in a book.
• If you don't have an index in a book, and someone ask you to locate a specific chapter in that book, you will have to look at every page starting from the first page of the book.On the other hand, if you have the index, you lookup the page number of the chapter in the index, and then directly go to that page number to locate the chapter.
• In a similar way, Table and View indexes, can help the query to find data quickly.
• The existence of the right indexes, can drastically improve the performance of the query. If there is no index to help the query, then the query engine, checks every row in the table from the beginning to the end. This is called as Table Scan. Table scan is bad for performance.

Example

Id	Name	Salary	Gender
1	Ashish	60000	Male
2	Ashutosh	70000	Male
3	Shree	90000	Male
4	Swapnil	50000	Male

Select * from tblEmployee where Salary > 50000 and Salary < 70000

To find all the employees, who has salary greater than 50000 and less than 70000, the query engine has to check each and every row in the table, resulting in a table scan, which can adversely affect the performance, especially if the table is large. Since there is no index, to help the query, the query engine performs an entire table scan.

Here, we are creating an index on Salary column in the employee table

CREATE Index IX_tblEmployee_Salary 
            ON tblEmployee (SALARY ASC)

The index stores salary of each employee, in the ascending order

Now, when the SQL server has to execute the same query, it has an index on the salary column to help this query. Salaries between the range of 5000 and 7000 are usually present at the bottom, since the salaries are arranged in an ascending order. SQL server picks up the row addresses from the index and directly fetch the records from the table, rather than scanning each row in the table.

---

SQL Views

A view is nothing more than a saved SQL query. A view can also be considered as a virtual table.

Create View vWEmployeesByDepartment
            as
            Select Id, Name, Salary, Gender, DeptName
            from tblEmployee
            join tblDepartment
            on tblEmployee.DepartmentId = tblDepartment.DeptId

To select data from the view, SELECT statement can be used the way, we use it with a table.

SELECT * from vWEmployeesByDepartment

• Views can be used to reduce the complexity of the database schema, for non IT users. The sample view, vWEmployeesByDepartment, hides the complexity of joins. Non-IT users, finds it easy to query the view, rather than writing complex joins.
• Views can be used as a mechanism to implement row and column level security.

---

SQL Triggers

A SQL trigger is a database object which fires when an event occurs in a database. We can execute a SQL query that will "do something" in a database when a change occurs on a database table such as a record is inserted or updated or deleted. For example, a trigger can be set on a record insert in a database table.

if you want to increase the count of blogs in the Reports table when a new record is inserted in the Blogs table, we can create a trigger on the Blogs' table on INSERT and update the Reports table by increasing blog count to 1.

• DDL Triggers : The DDL triggers are fired in response to DDL (Data Definition Language) command events that start with Create, Alter and Drop, such as Create_table, Create_view, drop_table, Drop_view and Alter_table.
• DML Triggers : The DML triggers are fired in response to DML (Data Manipulation Language) command events that start with Insert, Update, and Delete. Like insert_table, Update_view and Delete_table.

Example :

create trigger TrSamp
    on emp
    for
    insert,update,delete
    as
    print'you can not insert,update and delete this table i'
    rollback;