Sqlserver Interview questions

Total available count: 45
Subject - Databases
Subsubject - Sqlserver

Difference between Index defrag and Index rebuild in SQL Server?

When you create an index in the database, the information used by queries is stored in index pages. The sequential index pages are chained together by pointers from one page to the next. When changes are made to the data that affect the index, the information in the index can become scattered in the database.

Rebuilding an index recognizes the storage of the index data to remove fragmentation. This can improve disk performance by reducing the number of pages reads to obtain the requested data.
DBCC INDEXDEFRAG - Defragments clustered and secondary indexes of the specified table or view.

Next 5 interview question(s)

How many clustered indexes can a table have?
Disadvantage of Index in SQL Server?
What do you mean by ACID?
Explain the difference between a Local and a Global temporary table?
What is the difference between UNION and UNION ALL?