SQL Server Interview Questions on Indexes - Part 1

What is the use of an Index in SQL Server?
Relational databases like SQL Server use indexes to find data quickly when a query is processed. Creating the proper index can drastically increase the performance of an application.



What is a table scan?
or
What is the impact of table scan on performance?
When a SQL Server has no index to use for searching, the result is similar to the reader who looks at every page in a book to find a word. The SQL engine needs to visit every row in a table. In database terminology we call this behavior a table scan, or just scan. A full table scan of a very large table can adversely affect the performance. Creating proper indexes will allow the database to quickly narrow in on the rows to satisfy the query, and avoid scanning every row in the table.


What is the system stored procedure that can be used to list all the indexes that are created for a specific table?
sp_helpindex is the system stored procedure that can be used to list all the indexes that are created for a specific table.

For example, to list all the indexes on table tblCustomers, you can use the following command.

EXEC sp_helpindex tblCustomers


What is the purpose of query optimizer in SQL Server?
An important feature of SQL Server is a component known as the query optimizer. The query optimizer's job is to find the fastest and least resource intensive means of executing incoming queries. An important part of this job is selecting the best index or indexes to perform the task.


What is the first thing you will check for, if the query below is performing very slow?
SELECT * FROM tblProducts ORDER BY UnitPrice ASC


Check if there is an Index created on the UntiPrice column used in the ORDER BY clause. An index on the UnitPrice column can help the above query to find data very quickly.When we ask for a sorted data, the database will try to find an index and avoid sorting the results during execution of the query. We control sorting of a data by specifying a field, or fields, in an ORDER BY clause, with the sort order as ASC (ascending) or DESC (descending).


With no index, the database will scan the tblProducts table and sort the rows to process the query. However, if there is an index, it can provide the database with a presorted list of prices. The database can simply scan the index from the first entry to the last entry and retrieve the rows in sorted order.


The same index works equally well with the following query, simply by scanning the index in reverse.
SELECT * FROM tblProducts ORDER BY UnitPrice DESC



What is the significance of an Index on the column used in the GROUP BY clause?
Creating an Index on the column, that is used in the GROUP BY clause, can greatly improve the perofrmance. We use a GROUP BY clause to group records and aggregate values, for example, counting the number of products with the same UnitPrice. To process a query with a GROUP BY clause, the database will often sort the results on the columns included in the GROUP BY.


The following query counts the number of products at each price by grouping together records with the same UnitPrice value.
SELECT UnitPrice, Count(*) FROM tblProducts GROUP BY UnitPrice


The database can use the index (Index on UNITPRICE column) to retrieve the prices in order. Since matching prices appear in consecutive index entries, the database is able to count the number of products at each price quickly. Indexing a field used in a GROUP BY clause can often speed up a query.


What is the role of an Index in maintaining a Unique column in table?
Columns requiring unique values (such as primary key columns) must have a unique index applied. There are several methods available to create a unique index.
1. Marking a column as a primary key will automatically create a unique index on the column.
2. We can also create a unique index by checking the Create UNIQUE checkbox when creating the index graphically.
3. We can also create a unique index using SQL with the following command:
CREATE UNIQUE INDEX IDX_ProductName On Products (ProductName)


The above SQL command will not allow any duplicate values in the ProductName column, and an index is the best tool for the database to use to enforce this rule. Each time an application adds or modifies a row in the table, the database needs to search all existing records to ensure none of values in the new data duplicate existing values.

15 comments:

  1. Whats the difference between unique key and Unique Index? Does both have the same impact on a table?

    ReplyDelete
    Replies
    1. Both are the same

      Delete
    2. There are no major difference between UNIQUE KEY and UNIQUE INDEX. In Fact when you add a UNIQUE KEY a UNIQUE INDEX is created behind the scene.

      Delete
    3. When you create UNIQUE INDEX directly rather than a UNIQUE KEY CONSTRAINT, there won't be a UNIQUE key present in the database that matches your INDEX, thats the difference. If you decide to drop the constraint it will also remove the associated INDEX. If you created UNIQUE KEY CONSTRAINT on the other hand and then decide to DROP the INDEX, there will be an error that the INDEX is currently used by the associated UNIQUE KEY.

      -- EXAMPLE:

      CREATE UNIQUE INDEX UQ_Employees_Name ON Employees (Name);
      DROP INDEX Employees.UQ_Employees_Name;

      ALTER TABLE Employees ADD CONSTRAINT UQ_Employees_Name UNIQUE (Name);
      ALTER TABLE Employees DROP CONSTRAINT UQ_Employees_Name;

      Delete
  2. Unique Key Includes Null Values But Unique Index is same as clusterd index it didnt have a null column

    ReplyDelete
    Replies
    1. This is Manoj Sharma,
      Abhishek Sir Both are Same
      as i have tested
      by
      Creating table
      create table tbl_Sample
      (
      id int
      )
      create unique index
      create unique index uindex on tbl_Sample(id)
      insert into tbl_Sample values(null)
      i hope it will help you to undestand that there is no difference.

      Delete
  3. If I have a table with 10 rows starting from 1 to 10. Then how can i insert another row in between the already entered rows of the table. For example if i wanted to insert the new row at 5th position, how can it be done using query ?? And of course almost all in all databases ??

    ReplyDelete
    Replies
    1. You can use identity insert and set the reseed value to 5

      Delete
  4. What is the difference between cursor and while loop?

    ReplyDelete
  5. hi venkat it could be more effective if you include all topics like case statements,subprocedure.

    ReplyDelete
  6. On what basis we create indexes on particular column in a table

    ReplyDelete
  7. Hi Venkat Sir,
    table1 has two columns FName and LName
    Suppose i have created clustered index on FName now i am fetching data based on LName
    like select * from tblName where LName='Prasad'
    so Sql Engine have clustered index table where it may have FName and row_address Now when it will index Search it will not find row address based on LName So it should Not give any result.So my actual question is cluster index is dependent on that column, on which we are creating index.

    ReplyDelete
  8. Hello Champions,

    I've one small query- I want Emp_First Name, Emp_LastName From Employee Table. And Max Incentive from Incentive Table without using join.

    Could someone please help me about this?

    Thanks!
    Rajveer

    ReplyDelete
    Replies
    1. select first_name,last_name,incentive from employee where incentive >=
      (select max(incentive)from employee);

      Delete
  9. Can you please explain surrogate key. I have visited many websites after getting this question in my interview but didn't the explanation correctly.

    ReplyDelete

If you are aware of any other sql server questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

 
Disclaimer - Terms of use - Contact Us