Explore topic-wise InterviewSolutions in Current Affairs.

• Index can be thought as index of the book that is used for fast retrieval of INFORMATION.
• Index uses one or more column index keys and pointers to the record to locate record.
• Index is used to SPEED up query performance.
• Both exist as B-tree STRUCTURE.
• Kind of the INDEXES are CLUSTERED and non-clustered.

• Indexes can be CLUSTERED and non-clustered indexes.
• Clustered index KEEPS all the RECORDS in the DATABASE in the order of clustered index key.
• There can be only ONE clustered index per table.
• Non-clustered indexes are stored as B-tree structure in their own storage separate from data storage in the table.

• Close cursor when it is not required.
• You shouldn’t forget to deallocate cursor after closing it.
• You should FETCH least NUMBER of RECORDS.
• You should use FORWARD ONLY OPTION when there is no need to update rows.

• SQL Server caches the plan of execution of query or stored procedure which it uses in SUBSEQUENT call.
• This is a very important feature with regards to performance ENHANCEMENT.
• You can view execution plan of data RETRIEVAL graphically or TEXTUALLY.

INDEXES improve query performance but it slows down DATA modification operations.

Indexes consume DISK space.

Indexes improve query performance but it slows down data modification operations.

Indexes consume disk space.

• We shouldn't allow input from users during a transaction.
• We shouldn't open transactions while browsing through DATA.
• We should keep the transaction as short as POSSIBLE.
• We should try to use LOWER transaction ISOLATION levels.
• We should access the least amount of data possible while in a transaction.

• We shouldn't allow input from users during a transaction.
• We shouldn't open TRANSACTIONS while browsing through data.
• We should keep the transaction as short as possible.
• We should try to USE lower transaction ISOLATION levels.
• We should access the LEAST AMOUNT of data possible while in a transaction.

Index tuning is part of database tuning for selecting and CREATING indexes. The index tuning goal is to reduce the query PROCESSING time. Potential use of indexes in dynamic environments with several ad-hoc queries in ADVANCE is a difficult task. Index tuning involves the queries based on indexes and the indexes are created automatically on-the-fly. No explicit actions are needed by the database users for index tuning.

Index tuning is part of database tuning for selecting and creating indexes. The index tuning goal is to reduce the query processing time. Potential use of indexes in dynamic environments with several ad-hoc queries in advance is a difficult task. Index tuning involves the queries based on indexes and the indexes are created automatically on-the-fly. No explicit actions are needed by the database users for index tuning.

The Index tuning wizard can be used to improve the performance of queries and databases. It uses the following MEASURES to do so:

• It uses the query optimizer to perform the analysis of queries with respect to the workload and based on this knowledge, it recommends the best USAGE of indexes.
• The CHANGES in the usage of index, query distribution and their performance are analysed for checking the EFFECT.
• It also recommends ways of tuning the database for a small set of problem queries.

The Index tuning wizard can be used to improve the performance of queries and databases. It uses the following measures to do so:

The FOLLOWING are few tips for CURSOR optimization:

• When the cursor is not needed, close the cursor
• Deallocate the cursor after closing it.
• Fetch less number of rows.
• Avoid triggers – because trigger executes whenever DATA GETS updated, leads to overload of the system.
• When the rows are not need to update, use the option FORWARD ONLY
• Use where instead of having CLAUSE unless it is essential

The following are few tips for cursor optimization:

• No indexes
• EXCESS recompilations of stored procedures.
• Procedures and triggers without SET NOCOUNT ON.
• Poorly written query with unnecessarily complicated JOINS
• HIGHLY normalized database design.
• Excess USAGE of cursors and TEMPORARY tables.