Introduction
The SQL database provides a lot of commands and functions. With the help of them, we can manage a huge amount of data easily in the database. Following are some of the examples:
INDEX:
In the SQL database Index is like a schema object. It is used to fetch records from the database in a fast manner. With the help of the index, we can optimize our database performance. We can create an index on existing tables or views.
The real-world example of an index is that every book has an index. In this index, all chapters and their corresponding page numbers are declared. That’s why we find all the chapters easily.
The same concept is applied to a table in SQL. Assume that we have a ProductInfo table and it has a huge amount of data and we want to find Price which is greater than 50. It will scan all records, which is a time-consuming process. If we create an index on the price column, the query quickly fetches the records and displays it quickly. An index helps to speed up the SELECT queries but slows down the data input with INSERT and UPDATE statements.
Syntax:
CREATE INDEX index_name
ON tbl_name (clnm_name)
[ storage_setting ];
Query example: CREATE INDEX IX_ProductInfo ON tblProductInfo (Price asc)
There are two types of Index.
Clustered Index: In the case of cluster index, each table can have only one cluster index. In the cluster index, the data is physically stored in the table in a specific order. By default, the primary key has a clustered index in the table.
Note The Id column in every table treated as clustered index automatically. We can check this easily, if the Id column is clustered index or not.
Query: EXECUTE sp_helpindex tblProductInfo
Output:
Nonclustered Index: The nonclustered index is just the opposite of the clustered index. In the non-clustered index, data is unsorted. The nonclustered index and the table data are stored at two different places. It is very similar to our book. We see in our book the book index is available at the beginning of the book and the book contents are available after index.
In the index itself, the data is stored in ascending or descending order of the index key, which doesn’t in any way influence the storage of data in the table.
Query: CREATE NonClustered Index IX_ProductInfoindex on tblProductInfo
The following query shows all index available in your table.
Query: EXECUTE sp_helpindex tblProductInfo
Output:
TRIGGER:
A trigger is the most important part of the SQL. It is a special type of stored-procedure which automatically runs when an event occurs in the database.
For Example, a trigger can be executed when a row is inserted into a specified table or when certain table columns are being updated. We apply a trigger on the table or view.
DML triggers run when a user tries to modify data through a data manipulation language (Like Insert, Update and Delete) event.
DDL triggers run when the user tries to CREATE table, ALTER table and DROP table in the database.
We can apply triggers in two ways:
BEFORE
AFTER
//BEFORE: The Before trigger is fired before running the trigger statement
//AFTER: The After trigger is fired after running the trigger statements.
Syntax: create trigger [trigger_name]
[before | after]
{insert | update | delete}
on [table_name]
[for each row]
[trigger_body]
create trigger – [trigger_name]: Creates or replaces an existing trigger with the trigger_name.
[before | after]: – This specifies when the trigger will be executed.
{insert | update | delete} – This specifies the DML operation.
on [table_name] – Table name means on which table would you want to apply trigger.
[for each row] – This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected.
[trigger_body] – The Trigger body has to be performed when the trigger is fired.
Example:
We have a table ProductDetails and we want to update Quantity and Price of Id is 2.
Query:
create trigger trProductDetails_forUdate
on ProductDetails
for update
as
begin
select * from deleted
select * from inserted
End
Update ProductDetails set Quantity=10,Price=100 where Id=2
After executing the update query the output will be shown as below.
Output:
What is Index in SQL database?
In the SQL database Index is like a schema object. It is used to fetch records from the database in a fast manner. With the help of the index, we can optimize our database performance. We can create an index on existing tables or views.
What are the types of Index?
There are two types of Index:
1. Clustered Index: In the case of cluster index, each table can have only one cluster index. In the clustered index, the data is physically stored in the table in a specific order.
2. Nonclustered Index: The nonclustered index is just the opposite of the clustered index. In the non-clustered index, data is unsorted. The nonclustered index and the table data are stored at two different places.
What is a Trigger?
A trigger is the most important part of the SQL. It is a special type of stored-procedure which automatically runs when an event occurs in the database.