SQL Interview Questions on Indexes

<<PREV	SQL Interview Questions on Indexes	NEXT >>
11. What is Index? It’s purpose?
Indexes in databases are similar to indexes in books. In a database, an index allows the database program to find data in a table without scanning the entire table.
12. How to choose between a Clustered Index and a Non-Clustered Index?
Consider using a clustered index for: Columns that contain a large number of distinct values. Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=. Columns that are accessed sequentially. Non-clustered indexes have the same B-tree structure as clustered indexes, with two significant differences: data rows are not sorted and stored in order based on their non-clustered keys. The leaf layer of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Each index row contains the non-clustered key value and one or more row locators that point to the data row (or rows if the index is not unique) having the key value. Per table only 249 non clustered indexes.
13. Disadvantage of index?
Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much.
14. Given a scenario that I have a 10 Clustered Index in a Table to all their 10 Columns. What are the advantages and disadvantages
Only 1 clustered index is possible.
15. How can I enforce to use particular index?
You can use index hint (index=<index_name>) after the table name.SELECT au_lname FROM authors (index=aunmind).
16. What is Index Tuning?
One of the hardest tasks facing database administrators is the selection of appropriate columns for non-clustered indexes. You should consider creating non-clustered indexes on any columns that are frequently referenced in the WHERE clauses of SQL statements. Other good candidates are columns referenced by JOIN and GROUP BY operations. You may wish to also consider creating non-clustered indexes that cover all of the columns used by certain frequently issued queries. These queries are referred to as “covered queries” and experience excellent performance gains. Index Tuning is the process of finding appropriate column for non-clustered indexes. SQL Server provides a wonderful facility known as the Index Tuning Wizard which greatly enhances the index selection process.
17. Difference between Index defrag and Index rebuild?
When you create an index in the database, the index 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 reorganizes the storage of the index data (and table data in the case of a clustered index) to remove fragmentation. This can improve disk performance by reducing the number of page reads required to obtain the requested data DBCC INDEXDEFRAG - Defragments clustered and secondary indexes of the specified table or view.
18. What is sorting and what is the difference between sorting & clustered indexes?
The ORDER BY clause sorts query results by one or more columns up to 8,060 bytes. This will happen by the time when we retrieve data from database. Clustered indexes physically sorting data, while inserting/updating the table.
19. What are statistics, under what circumstances they go out of date, how do you update them?
Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query. Some situations under which you should update statistics: 1) If there is significant change in the key values in the index 2) If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated 3) Database is upgraded from a previous version.
20. What are different data types in SQL?
bigint Binary Bit char cursor datetime decimal float image int money Nchar Ntext nvarchar real smalldatetime smallint smallmoney text timestamp tinyint varbinary varchar uniqueidentifier
<<PREV	SQL Interview Questions on Indexes	NEXT >>