SQL Server indexes

SQL Server indexes interview questions

<<Previous  Next>>

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.................... Read answer 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.................. Read answer Define Clustered and Non-Clustered Index. Clustered index exists as sorted row on disk. Clustered index re-orders the table record..................... Read answer 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...................... Read answer 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................... Read answer   Explain the 'Fillfactor' concept in Indexes. The fill factor option is provided for smoothening index data storage and performance..................  Read answer 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............... Read answer 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................ Read answer What is full-text indexing? Full text indexes are stored in the file system and are administered through the database................. Read answer 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.................. Read answer Describe important index characteristics. The characteristics of the indexes are: They fasten the searching of a row. They are sorted by the Key values................. Read answer What are the types of indexes? Types of indexes: Clustered: It sorts and stores the data row of the table or view in order based on the index key.  Non clustered: it can be defined on a table or view with clustered index or on a heap. Each row contains the key and row locator................ Read answer Describe the purpose of indexes. Allow the server to retrieve requested data, in as few I/O operations. Improve performance.................. Read answer Determine when an index is appropriate. a. When there is large amount of data. For faster search mechanism indexes are appropriate.  b. To improve performance they must be created on fields used in table joins............... Read answer Syntax and an example for create, rename and delete index. Create Index: CREATE INDEX index_name ON table_name (col_1,col_2..); Example: Create index index_sample ON employee(emp_id)..................... Read answer <<Previous  Next>> Also read SQL Server Optimization Tips Answer - Restricting query result means return of required rows instead of all rows of the table. This helps in reducing network traffic...... Having clause and Where clause Answer - Both are used for filtering of records based on filtered criteria..... Describe in brief authentication modes in SQL server. Answer - This is the default and recommended security mode. In this mode, access to SQL server is controlled by Windows NT..... Define @@Error and raiseerror. Answer - It is system variable that returns error code of the SQL statement..... Transferring a View from Oracle 10G XE to an SQL Server 2005 Database Here you will learn how to copy a table on an Oracle 10G XE database to a database on the SQL Server 2005. You will also learn how to install an Oracle 10G XE server and work with its database objects.