SQL interview Question Answer- Part -  2

Question 16. What is an Index?

Answer : Indexes are used to retrieval data more quickly from database. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.

Question 17. What are the types of Indexes available in SQL Server?

Answer: There are two types of indexes available in SQL Server.

1.     Clustered Index
2.     Non-Clustered Index

Question 18. What is Clustered index and how many clustered indexes there can be in one table?

Answer: A clustered index is special type if index that reorder the way records in the table are physically stored. Only one clustered index is possible in one table.

Question 19. Why only one clustered index per table should be created in SQL Server? 

Answer. Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be stored in only one order.

Question 20. Can we create a clustered index on multiple columns in SQL Server?

Answer. Yes we can create clustered index on multiple columns in Sql Server but there is limitation only we can take max 16 column.

Question 21. Is it necessary to declare column primary or unique key for creating clustered index.

Answer. Answer is NOT. but it is recommended to declare the clustered index key column or combination of columns as unique to improve the queries performance. Otherwise, SQL Server will automatically add a uniqueifier (this four bite value is not visible to user) column to enforce the clustered index key uniqueness.

Indexes are automatically created when PRIMARY KEY and UNIQUE constraints are defined on table columns. For example, when you create a table with a UNIQUE constraint, Database Engine automatically creates a nonclustered index. If you configure a PRIMARY KEY, Database Engine automatically creates a clustered index, unless a clustered index already exists. When you try to enforce a PRIMARY KEY constraint on an existing table and a clustered index already exists on that table, SQL Server enforces the primary key using a nonclustered index. later we can drop indexes.

Question 22. What is ideal characteristics of clustered index key.

Answer.

1. Short : as short as possible, in terms of the number of bytes it stores.
2. Static : ideally, never updated.
3. Unique: to avoid the need for SQL Server to add a 'uniqueifier' to duplicate key values.
4. Accessed Frequently.
5. Used in Order By Clause

Question 23. Why it is not recommended to use GUID and Character column as clustered index key.

Answer. There are two main reasons why we don’t do this. First is data size. The SQL Server UNIQUEIDENTIFIER data type is 16 bytes convertible to the char(36) data type while BIGINT is only 8 bytes and INT is only 4 bytes.

Second one the most damaging thing that GUIDs cause is severe index fragmentation.  To understand this, we need to review how clustered indexes work in SQL Server.  The clustered index for a table or view determines the physical order of the rows on a data page.  So we like to make sure we are clustering on a value that is continuously increasing, like an integer generated by the IDENTITY property or via a sequence.  By doing this we ensure that new data always gets added to the end of the table and pages are filled sequentially.  The very definition of a GUID is that it is not sequential.  So every time a new row is inserted, SQL Server has to move things around to make room for it in the correct place.  Not only is this an expensive operation, but a GUID will never be accessed in a range so we lose that benefit of a good clustering key as well.  If a page is full and we need to insert a row into it, we get a Page Split, meaning SQL Server has to allocate a new page, move the rows that spill off the original page to it, and then add our new row to the original page.

Question 24.  What is Non Clustered Index.

Answer. First of the non-clustered index does not sort the data rows physically as clustered index does.

It creates a separate key-value structure from the table data where the key contains the column values 

(on which a non-clustered index is declared) and each value contains a pointer to the data row that 

contains the actual value, similar to textbook indexing.

Question 25. How many Non Clustered index can be created?

Answer. For Sql Server  2005 it is 249 and Onwards Sql Server 2008  it is 999 Non Clustered index can be created in a single table.

Question 26. What is advantages and disadvantages of indexes?

Answer. Advantages.

1. Quickly returns a large range of data.
2. Physical ordering of the data of the column.
3. A Index improve the performance of data retrieval.

Disadvantages

1. Frequently updated data increases the overhead of rearranging the B-tree.
2. Indexes have a poor consequence on the performance of the data modification statements like INSERT, UPDATE, or DELETE. Every time a query asks to modify the data in the table, the database updates itself with the new index where the data changes. 
3.  Only one Clustered Index per table.
4.  If the size of the table is small then it is not effective.

Question 27. Why do we need Indexes in SQL Server?

Answer. In above answer we can consider all advantages.

Question 28. When should we create indexes on a table?

Answer. We can create an index in columns that are common in WHERE, ORDER BY and GROUP BY clauses. You may consider adding an index in columns that are used to relate other tables (through a JOIN, for example).

Question29. What is difference between cluster and Non-cluster index?.

Answer. 

1. Clustered index is faster while Non-clustered index is slower.
2. Clustered  index has the natural ability to store data on the disk while Non-clustered index does not have the natural strength to store data on the disk.
3. Clustered index requires less memory for operations while Non-Clustered index requires more memory for operations.
4. A table can have only one clustered index. while we can have have multiple non-clustered index in a table.
5. In Clustered index leaf nodes are actual data itself while In Non-Clustered index leaf nodes are not the actual data itself rather they only contains included columns.

Question 30. Which Index is faster, Clustered or Non Clustered and why?

Answer. Clustered Index is slightly faster than the Non-Clustered Index. This is because when a Non Clustered Index is used there is an extra look up from the Non-Clustered Index to the table, to fetch the actual rows.

Question 31. What is difference between rebuild index and organize index?

Answer. 

Index Rebuild : This process drops the existing Index and Recreates the index.

Index Reorganize : This process physically reorganizes the leaf nodes of the index.

Recommendation: Index should be rebuild when index fragmentation is great than 40%.Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

 

Question 32. How can we improve the performance of poor-performing SQL queries?

Answer. We can improve the performance of poor-performing SQL queries in the following ways:

1. Using indexes properly
2. Creating primary and foreign keys.
3. Not making table scans.
4. Avoiding using cursors.
5. Using partitioned views.

Question 33.  How can you improve the performance of stored procedures in the SQL server?

Answer. 

1. Using SET NOCOUNT ON messages, information messages can be prevented from the result set. It will reduce network traffic and increase operational performance.
2. When fully qualified procedure name, the SQL server finds the compiled plan quickly, which in turn increases the performance
3. Specifying stored procedures as 'sp_procedurename' must be avoided because the SQL server will search the master database first if it finds ‘sp’ in the procedure name. It would decrease the performance and yield error results at times.
4. Transactions need to be shorter so that deadlocks and blocking can be prevented.