SQL Server indexes

SQL Server indexes interview questions

SQL Server index - August 29, 2008 at 18:00 PM by Nishant Kumar What is an index? Indexes of SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker. Indexes are of two types. Clustered indexes and non-clustered indexes. Rows in the table are stored in the order of the clustered index key. There can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures. Leaf level nodes having the index key and it's row locater. Disadvantages of the Indexes Use of intexes slow down Data modification operations (such as INSERT, UPDATE, DELETE). Every time data changes in the table, all the indexes need to be updated. Indexes need disk space, the more indexes you have, more disk space is used. Define Clustered and Non-Clustered Index. Clustered index exists as sorted row on disk. Clustered index re-orders the table record. Clustered index contains record in the leaf level of the B-tree. There can be only one Clustered index possible in a table. Non-clustered is the index in which logical order doesn’t match with physical order of stored data on disk. Non-clustered index contains index key to the table records in the leaf level. There can be one or more Non-clustered indexes in a table. What is Unique Index? Unique index is the index that is applied to any column of unique value. A unique index can also be applied to a group of columns. Difference between clustered and non-clustered index. Both stored as B-tree structure. The leaf level of a clustered index is the actual data where as leaf level of a non-clustered index is pointer to data. We can have only one clustered index in a table but we can have many non-clustered index in a table. Physical data in the table is sorted in the order of clustered index while not with the case of non-clustered data. SQL Server indexes - Posted on August 29, 2008 at 18:00 PM by Amit Satpute Explain the 'Fillfactor' concept in Indexes. The fill factor option is provided for smoothening index data storage and performance.  The percentage of space on each leaf level page to be filled with data is determined by the fill factor value When an index is created. This reserves a percentage of free space for future growth.  How do you maintain a fill factor in existing indexes? Usually the data keeps getting added, deleted, or updated in the table due to which the fill factor is implemented during the index creation itself. Fill factor is not maintained after an index is created. The changes in the future can be determined only when a new index is created on a table that has an existing data. It is beneficial to set the fill factor then itself. Maintaining extra space on the data pages obviates the purpose of using the fill factor. The SQL Server would then have to split pages to maintain an amount of free space per the fill factor, on each page. Thus, when data is added filling up the empty space, an index can be created and the fill factor can be re-specified distribute the data again. What is it unwise to create wide clustered index keys? A clustered index is a good choice for searching over a range of values. After an indexed row is found, the remaining rows being adjacent to it can be found easily. However, using wide keys with clustered indexes is not wise because these keys are also used by the non-clustered indexes for look ups and are also stored in every non-clustered index leaf entry. What is full-text indexing? Full text indexes are stored in the file system and are administered through the database. Only one full-text index is allowed for one table. They are grouped within the same database in full-text catalogs and are created, managed and dropped using wizards or stored procedures. What is fill factor and pad index? A fill factor is a specification done during the creation of indexes so that a particular amount of space can be left on a leaf level page to decrease the occurrence of page splits when the data has to be accommodated in the future. A pad index specifies index padding. When it is set to ON, then the free space percentage per the fill factor specification is applied to the intermediate-level pages of the index. When it is set to OFF, the fill factor is not specified and enough space is left for a maximum size row that an index can have. Describe important index characteristics. The characteristics of the indexes are: They fasten the searching of a row. They are sorted by the Key values. They are small and contain only a small number of columns of the table. They refer for the appropriate block of the table with a key value. The answers to following questions will be made available soon. Keep visiting. What is an index? What are the types of indexes? Describe the purpose and structure of indexes. Explain the difference between clustered and non-clustered index. Determine when an index is appropriate. Syntax and an example for create, rename and delete index.