Clustered Index on table

• prafullaahirrao

  Old Hand

  Points: 308

  More actions

  December 12, 2016 at 11:43 pm

  #332703

  Hi All,

  I have one table Employee which has 2 columns EmpId and EmpName with thousand's of rows in it.

  Table does not have any key as of now.

  If I execute a query on this table say for e.g. Select * from Employee where EmpName='John'

  What will execution plan show?

  Now if I create Clustered Index on EmpId.

  Again I execute same query for e.g. Select * from Employee where EmpName='John'

  What will execution plan show?

  Is there any difference in both the execution plan?

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  December 13, 2016 at 1:14 am

  #1917512

  prafullaahirrao (12/12/2016)

  ---

  Hi All,

  I have one table Employee which has 2 columns EmpId and EmpName with thousand's of rows in it.

  Table does not have any key as of now.

  If I execute a query on this table say for e.g. Select * from Employee where EmpName='John'

  What will execution plan show?

  Now if I create Clustered Index on EmpId.

  Again I execute same query for e.g. Select * from Employee where EmpName='John'

  What will execution plan show?

  Is there any difference in both the execution plan?

  Without any index the server will do a table scan of the whole table (heap), if you add a clustered index which does not cover the search predicate then the server will do a clustered index scan of the whole table. Only if you add an index that cover the column you are searching on will you see any substantial difference.

  😎

• prafullaahirrao

  Old Hand

  Points: 308

  More actions

  December 13, 2016 at 10:02 pm

  #1917767

  Thanks 🙂

• kevaburg

  SSCoach

  Points: 18131

  More actions

  December 14, 2016 at 4:51 am

  #1917826

  prafullaahirrao (12/12/2016)

  ---

  Hi All,

  I have one table Employee which has 2 columns EmpId and EmpName with thousand's of rows in it.

  Table does not have any key as of now.

  If I execute a query on this table say for e.g. Select * from Employee where EmpName='John'

  What will execution plan show?

  Now if I create Clustered Index on EmpId.

  Again I execute same query for e.g. Select * from Employee where EmpName='John'

  What will execution plan show?

  Is there any difference in both the execution plan?

  A little bit lost here.....

  Why don't you use the Execution Plan Tools in SSMS yourself and experience what the plans look like first hand?