Post reply

Clustered Index on table

  • 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?

  • 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.

    😎

  • December 13, 2016 at 10:02 pm

    #1917767

    Thanks 🙂

  • 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?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply