Thursday, 17 March 2016

SQL Server Interview Questions Part2

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

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

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.

It is a virtual table in SQL.
Create view viewCustomer
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.

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

No comments:

Post a Comment

Dear Readers, Please post your valuable feedback in the comment section if you like this blog or if you have any suggestions. I would love to hear the same from you. Thanks