SQL Server Interview Questions Part2

Few important question for SQL Server asked to ASP.Net Developer. Part- 2 

Indexes-
Indexes are used to query data faster from a table. Indexing avoid 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

Clustered index determine the physical order of data in a table.
A table can have only one clustered index.

Generally if we create a table with id column and mark it as primary key, automatically it create clustered index if no index specified while creating table.

One clustered index can contain more than one columns then it is called composite index.
Clustered indexes does 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 gete record from view.

select * from viewCustomer

We can create 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 parameter to view. Order clause is not applicable with View.
View can not be on temporary table.


Trigger:
A trigger is a sql block which is executed when a DML statment (insert, update, delete) is executed on a table.

3 types of Triggers

  • DML Triggers
  • DDL Triggers
  • Logon Triggers



Popular posts from this blog

Calling REST APi from server side code - C#

All about SharePoint 2010 Content Type Hub

Calling ASP.Net WebMethod using jQuery AJAX