Indexes-
Indexes are used to query data faster from a table. Indexing avoids full table scan.
Create Index IX_customer_amount on tblcustomer (purchase_amount ASC)
So this index will store purchase amount in ascending order.
Purchase amount
2000
5000
5500
7500
9000
To view all indexes on a table either go to object explorer in SQL management studio or write below query which will list all indexes on given table.
sp_Helpindex tblCustomer
To drop an index:
drop index <index_name>
Clustered and NonClustered Indexes
The clustered index determines the physical order of data in a table.
A table can have only one clustered index.
Generally, if we create a table with an id column and mark it as primary key, automatically it creates a clustered index if no index specified while creating a table.
One clustered index can contain more than one columns then it is called composite index.
Clustered indexes do not require any additional storage.
Non Clustered index contain multiple columns.
Create NonClustered Index IX_customertbl_amount on customertbl
Non clustered indexes require additional storage.
Too many indexes may affect performance as apart from select there are insert, update and delete operation also.
View:
It is a virtual table in SQL.
Create view viewCustomer
as
select id, cust_name, amount,city from customertbl
Now we can get the record from view.
select * from viewCustomer
We can create a view for join query, so in this case, we can query data directly from view and
so we can avoid using join query.
We can not pass the parameter to view. Order clause is not applicable with View.
The view cannot be on the temporary table.
Trigger:
A trigger is a SQL block which is executed when a DML statement (insert, update, delete) is executed on a table.
3 types of Triggers
- DML Triggers
- DDL Triggers
- Logon Triggers
