Views,Indexes, Joins in SQLServer

This article covers basic information about Views, Indexes and Joins in Sql Server

What are Views?

 A View is a logical table based on a table or another view.Only the Definition of View(Select statement) is stored.

What are the advantages of Views?

1.Views restrict access to the data because the views can display selected columns from the table.

2.View can be used to write simple queries to retrieve the results of complicated queries.

3.A View can be used to retrieve data from several tables.

4. Views provide data independence for adhoc users and applications.

Give an example of a View:

 Create View vuEmp as

SELECT EmpId,EmpName,EmpSalary

FROM Emp

WHERE DeptId=10;

View Created.

What is an Index ?

1.It is used to retrieve data quickly by using a pointer when a query is processed.

2. It provides direct and fast access to rows in a table.

3. It can be created or dropped or dropped at any time and have no effect on the tables or other indexes.

4.When a query is processed which has a where condition on some column then if you do not have an index on the column in the where clause then a full table scan occurs. That is every row in the table has to be processed to execute the query. If the table grows then processing a query becomes slow and expensive.

5. It is used and maintained automatically by SQL Server.

6. An Index stores the column value on which the index is created for each row of the table along with the RowId of that row in either ascending/descending order.

Give an example of index ?

 SELECT EmpId,Name,Sal
FROM Emp
WHERE (Sal > 1000) and (Sal < 5000)

 In this example we have to retrieve employee details whose salary is greater than 1000 and less than 5000.

If the Emp table has 50,000 records then 50,000 records needs to be scanned to get the results of the query but if you have an index on the Sal column then using the index we can retrieve data directly only from those rows which has salary >1000 and less than 5000.

What are the types of index?

There are two types of index.

1. Unique Index: IT is automatically created when you define a column in a table to have a PRIMARY KEY or a UNIQUE KEY constraint.

2. Non-Unique Index: The Index which user creates.

 When should you consider creating index ?

More indexes on a table does not mean faster query processing. For each DML operation committed on a table with indexes, the indexes must be updated. The more indexes associated with a table the more effort the sql server must make to update all the indexes after a DML operation. Therefore Indexed should be created only if

1. The column contains a wide range of values.

2. The column contains a large number of null values.

3. One or more columns are frequently used together in a where clause or join condition.

4. The table is big and most of the queries being processed are expected to retrieve less than 2 to 4 % of the rows.

What is a Composite Index?

Ans: CompositeIndex is an index that you create on multiple columns in a table.

Note:

1. NULL values are not included in the index.

 2.You cannot modify an index . To change an index, you must drop it and then recreate it. Drop index using Drop keyword. Ex: DROP INDEX indexname.

 3.If you drop a table, Index and constraints are automaticallr dropped but views and sequences remain.

When you should not create an index?

1.If the table is small.

2. If the indexed column is not often used as a condition in a query.

3.The table is updated frequently.

4.The indexed columns are referenced as part of an expression.

How to list the indexed associated with a table ?

 EXEC sp_helpindex tablename

  

What are the disadvantages of using Index?

1.As Index is stored on disk. As the size of the table grows. The size of the index also grows.

2.For every insert,update, delete operation the database has to update all of the indexes where data has changed.

What are the applications of indexes?

1.Report generation is optimized using indexes.

2.Index increase search performance.

  Joins

What are Joins?

Ans: To query data from more than one table use joins.

Consider below two tables for below examples:

Emp(EmpId,Name,DeptId,managerId)

Dept(DeptId,DeptName,LocId)

Job(Low_Sal,High_Sal,Grade)

Loc(LocId,City)

What is EquiJoin/SimpleJoin/Inner Join ?

The relationship between Emp,Dept tables is an equijoin. That is, values in the DeptId column on both tables must be equal.

Ex: To find an employee’ss department name you compare the value in the DeptId column in emp table with DeptId value in Dept Table.

SELECT e.EmpId, e.Name, e.DeptId

FROM Emp e, Dept d

WHERE e.DeptId = d.DeptId

  

What is Non-EquiJoin

 It is a join condition containing something other than an equality operator.

Ex:

SELECT e.EmpId, e.Sal,j.Grade

 FROM Emp e, Job j

 WHERE e.Sal BETWEEN j.Low_Sal AND j.High_Sal

  

What is OUTER JOIN

It is used to include rows that do not match JOIN condition. + sign is placed on the side of the join that is deficient in the information.

Ex:

SELECT table1.col, table2.col

FROM table1,table2

WHERE table1.col (+) = table2.col;

What is LEFT OUTER JOIN? Explain.

Below query retrieves all rows in Emp table which is the left table even if there is no match in Dept table.

Ex:

SELECT e.name,e.deptid,d.deptname

FROM Emp e, Dept d

WHERE d.DeptId(+) = e.DeptId

Or

SELECT e.name,e.deptid,d.deptname

FROM Emp e

LEFT OUTER JOIN Dept d

ON e.DeptId = d.DeptId

   

What is RIGHT OUTER JOIN? Explain.

Below query retrieves all rows in Dept table which is the Right table even if there is no match in Emp table i.e., even if DeptId column of Dept table contains null values.

Ex:

SELECT e.name,e.deptid,d.deptname

FROM Emp e, Dept d

WHERE d.DeptId= e.DeptId(+)

Or

SELECT e.name,e.deptid,d.deptname

FROM Emp e

RIGHT OUTER JOIN Dept d

ON e.DeptId = d.DeptId

What is FULL OUTER JOIN?

It retrieves all rows from both the tables including null values.

Ex:

SELECT e.name,e.deptid,d.deptname

FROM Emp e

FULL OUTER JOIN Dept d

ON e.DeptId = d.DeptId

Give an example of CROSS Join(Cartesian Product)?

SELECT name,deptname

FROM EMP,DEPT\

Or

SELECT name,deptname

FROM EMP

CROSS JOIN DEPT

What is NATURAL JOIN?

It is based on all columns in the 2 tables that have the same name and datatypes

NOTE: Using clause can be used to specify only those columns that should be used for an Equii Join.

ON condition is used to specify a join condition. This lets us specify join condition separate from any search / filter condition in where clause.

What is SELF JOIN ?

Joining a table to itself is self join. In the below query, you look into the table twice. the first time you look in the table to find Name in the name column and managerId value in the managerId column. The second time you look in the EmpId column to find the employee with the managerId value got from the first result and the name column to find the name.

Ex:

SELECT Employee.name  ‘Works for ‘ manager.name

FROM emp Employee , emp manager

WHERE Employee.managerId = manager.EmpId

How to join 3 tables using joins?

Joins are always performed from Left to Right. First Join condition can reference column in Emp and Dept tables but cannot reference columns in Loc Table. Second join can reference columns from all the three tables.

Ex:

SELECT EmpId,City,DeptName

FROM Emp e

JOIN Dept d

ON d.DeptId = e.DeptId

JOIN Loc l

ON d.LocId = l.LocId

Or

Select EmpId, City, DeptName

FROM Emp, Dept, Loc

WHERE Emp.DeptId = Dept.DeptId

AND Dept.LocId = Loc.LocId

.

.

.

.

.