Interview Questions.

Q1. Do You Know What Are The Ways To Code Efficient Tractions?

We shouldn't allow input from customers in the course of a traction.

We shouldn't open tractions even as browsing via records.

We ought to keep the traction as short as possible.

We need to try to use decrease traction isolation levels.

We ought to get right of entry to the least quantity of records possible even as in a traction.

Q2. Tell Me In Brief The Cursor Optimization Tips?

The following are few tips for cursor optimization:

When the cursor is not needed, close the cursor

Deallocate the cursor after closing it.

Fetch much less range of rows.

Avoid triggers – due to the fact trigger executes on every occasion records receives up to date, ends in overload of the system.

When the rows aren't want to update, use the option FORWARD ONLY

Use in which rather of getting clause except it's miles essential

Q3. Explain Some Disadvantages Of The Indexes?

Indexes enhance question overall performance but it slows down facts modification operations.

Indexes consume disk area.

Q4. Reasons Of Poor Performance Of Query?

No indexes

Excess recompilations of saved processes.

Procedures and triggers without SET NOCOUNT ON.

Poorly written question with unnecessarily complex joins

Highly normalized database design.

Excess utilization of cursors and transient tables.

Q5. What Are The Ways To Code Efficient Tractions?

We shouldn't allow input from users during a traction.

We should not open tractions at the same time as surfing via statistics.

We need to hold the traction as quick as possible.

We should try to use decrease traction isolation degrees.

We should access the least quantity of information possible while in a traction.

Q6. Explain In Brief The Cursor Optimization Tips?

Close cursor when it isn't always required.

You shouldn’t forget to deallocate cursor after ultimate it.

You need to fetch least quantity of information.

You must use FORWARD ONLY choice when there may be no want to update rows.

Q7. How Is Index Tuning Used To Improve Query Performance?

The Index tuning wizard may be used to improve the performance of queries and databases. It uses the subsequent measures to accomplish that:

It uses the question optimizer to carry out the evaluation of queries with recognize to the workload and based totally in this know-how, it recommends the first-rate utilization of indexes.

The adjustments within the usage of index, query distribution and their overall performance are analysed for checking the effect.

It also recommends approaches of tuning the database for a small set of hassle queries.

Q8. Tell Me What Is Index Tuning?

Index tuning is a part of database tuning for choosing and creating indexes. The index tuning aim is to lessen the question processing time. Potential use of indexes in dynamic environments with several ad-hoc queries in advance is a difficult mission. Index tuning includes the queries based on indexes and the indexes are created mechanically on-the-fly. No explicit moves are wished via the database users for index tuning.

Q9. Tell Me What Are The Types Of Indexes?

Indexes can be clustered and non-clustered indexes.

Clustered index keeps all the information within the database in the order of clustered index key.

There can be most effective one clustered index consistent with table.

Non-clustered indexes are stored as B-tree structure of their personal storage become independent from information storage inside the desk.

Q10. Explain Execution Plan?

SQL Server caches the plan of execution of query or stored manner which it makes use of in subsequent name.

This is a very vital characteristic with reference to performance enhancement.

You can view execution plan of information retrieval graphically or textually.

Q11. What Are Indexes?

Index can be thought as index of the ebook that is used for immediate retrieval of statistics.

Index makes use of one or more column index keys and pointers to the file to discover record.

Index is used to speed up query overall performance.

Both exist as B-tree shape.

Kind of the indexes are clustered and non-clustered.