INDEXES
Indexes are used by queries to find data from tables quickly.
An index is a data structure that enables data to be found quickly.
Indexes are created on tables and views.
An index on a table or a view is very similar to an index that we find in a book.
*Table and View indexes can help the query to find data quickly.
EX. CREATE Index IX_tblEmployee_Salary
ON tblEmployee (SALARY ASC)
In the object explorer, Expand the Indexes folder.
Alternatively use sp_helptext system stored procedure.
The following command query returns all the indexes on the tblEmployee table.
*Execute sp_helptext tblEmployee*
The following are the different types of indexes in SQL Server
1. Clustered
2. Nonclustered
3. Unique
4. Filtered
5. XML
6. Full Text
7. Spatial
8. Columnstore
9. Index with included columns
10. Index on computed columns
Type of Indexes -Clustered and Non-Clustered indexes
1.Clustered Index
A clustered index determines the physical order of data in a table.
For this reason, a table can have only one clustered index.
**Note that the Id column is marked as the primary key.
Primary key, constraint create clustered indexes automatically
if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint.
To confirm this, execute sp_helpindex tblEmployee, which will show a unique clustered index created on the Id column.
Type of Indexes -Unique and Non-Unique Indexes
A unique index is used to enforce the uniqueness of key values in the index.
UNIQUENESS is a property of an Index, and both CLUSTERED and NON-CLUSTERED indexes can be UNIQUE.
VIEW
What is a View?
A view is nothing more than a saved SQL query. A view can also be considered as a virtual table.
Views are virtual tables that hold data from one or more tables.
Types Of View
1.System View
2.User Define View
ex....Create View vWEmployeesByDepartment
as
Select Id, Name, Salary, Gender, DeptName
from tblEmployee
join tblDepartment
on tblEmployee.DepartmentId = tblDepartment.DeptId
SELECT * from vWEmployeesByDepartment
Advantages of using views:
1. Views can be used to reduce the complexity of the database schema
2.Views can be used as a mechanism to implement row and column level security.
3.Views can be used to present only aggregated data and hide detailed data.
Updateable Views
ex..
Update vWEmployeesDataExceptSalary
Set Name = 'Mikey' Where Id = 2
INDEX VIEW -
If you want to create an Index, on a view, the following rules should be followed by the view.
For the complete list of all rules, please check MSDN.
1. The view should be created with the SchemaBinding option
2. If an Aggregate function in the SELECT LIST, references an expression,
and if there is a possibility for that expression to become NULL, then, a replacement value should be specified.
In this example, we are using, ISNULL() function, to replace NULL values with ZERO.
3. If GROUP BY is specified, the view select list must contain a COUNT_BIG(*) expression
4. The base tables in the view, should be referenced with 2 part names. In this example,
tblProduct and tblProductSales are referenced using dbo.tblProduct and dbo.tblProductSales respectively.
Limitations of views -
1. You cannot pass parameters to a view.
Table-Valued functions are an excellent replacement for parameterized views.
2. Rules and Defaults cannot be associated with views.
3. The ORDER BY clause is invalid in views unless TOP or FOR XML is also specified.
4. Views cannot be based on temporary tables.
TRIGGER
A trigger is a special kind of Stored Procedure or stored program that is automatically fired or executed when some event (insert, delete and update) occurs."
[ If you write a trigger for an insert operation on a table, after firing the trigger, it creates a table named “INSERTED” in memory.
Then it does the insert operation and then the statements inside the trigger execute. We can query the “INSERTED” table to manipulate or use the inserted row(s) from the trigger.
You have a table that changes frequently, now you want to know how many times and when these changes take place.
In that case, you can create a trigger that will insert the desired data into another table whenever any change in the main table occurs.
]
We use a trigger when we want some event to happen automatically on certain desirable scenarios.
*Types of Triggers
In SQL Server we can create the following 3 types of triggers:
~Data Definition Language (DDL) triggers
~Data Manipulation Language (DML) triggers
~Logon triggers
*DDL Triggers
In SQL Server we can create triggers on DDL statements (like CREATE, ALTER and DROP) and certain system-defined Stored Procedures that does DDL-like operations.
*DML Triggers
In SQL Server we can create triggers on DML statements (like INSERT, UPDATE and DELETE) and Stored Procedures that do DML-like operations. DML Triggers are of two types.
~After trigger (using FOR/AFTER CLAUSE)
The After trigger (using the FOR/AFTER CLAUSE) fires after SQL Server finishes the execution of the action successfully that fired it.
Example: If you insert a record/row into a table then the trigger related/associated with the insert event on this table will fire only after the row passes all the constraints,
such as primary key constraint and some rules. If the record/row insertion fails, SQL Server will not fire the After Trigger.
~Instead of Trigger (using INSTEAD OF CLAUSE)
The Instead of Trigger (using the INSTEAD OF CLAUSE) fires before SQL Server starts the execution of the action that fired it.
This is different from the AFTER trigger that fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but
does not include the actual insert/update/delete to the table.
Example: If you insert a record/row into a table then the trigger related/associated with the insert event on this table will fire before the row passes all the constraints,
such as primary key constraint and some rules. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.
DML
Instead of Trigger: An Instead of the trigger is fired instead of the triggering action such as an insert, update, or delete
After Trigger: An After trigger executes following the triggering action, such as an insert, update or delete
DDL Trigger
This type of trigger is fired against DDL statements like Drop Table, Create Table or Altar Table. DDL Triggers are always After Triggers.
INDEXES
No comments:
Post a Comment