Home Forums SQL Server 2008 T-SQL (SS2K8) Guidance on table valued types. RE: Guidance on table valued types.

  • March 4, 2013 at 2:09 pm

    #1593173

    mayur birari (3/4/2013)

    Thanks everybody for your responses. I could not post the table structure etc because of policies. Here is what I found out after more testing.

    From the execution plans, it looks like the SQL server has to use all the records from the tvp anyways as it does index seeks against the main physical table. That is why having an index on tvp does not matter.

    There is one thing to notice here and that is how do you join the tvp against the physical table:

    1. With the non-indexed tvp, when I use where exists() to filter records from the physical table, then the SQL Server does add Sort() in the execution plan after the Table scan of the tvp . If I convert it inner join then it removes that Sort().

    2. With the indexed tvp, it does need that Sort() even if where exists() is used.

    So when inner join is used, both non-indexed and indexed tvps perform same.

    So I think the only advantage that an index gives is the uniqueness of the records coming in. The "Ordered" part of the index does not come in handy. I tried this out with the inserts as well and found out the same thing.

    Let me know what do you guys thing.

    Thanks,

    Mayur

    We said the unique constraint helps defend against duplicates from entering the mix due to sloppy development and gives the optimizer more information with which to base its decisions. In your specific case maybe uniqueness is all you will benefit from initially however an index comes as a package deal with a unique constraint like a primary key. In your specific case you may not see a benefit with or without the index in your join however if the plan ever changes due to changes in the data distribution, or possibly the introduction of a new index definition, then sorting requirements may also change on either side of the join and at that point the index on the TVP may come in handy in guiding the engine into a consistent data access pattern across concurrent requests. This is where you will help the engine avoid deadlocks.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato