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

Arjun Sivadasan (3/3/2013)

---

Jeff, in this case, won't optimizer choose to scan rather than to seek? Will optimizer seek blindly because the index is covering? Or does it depend?

mayur birari (3/6/2013)

---

Hi @opc,

I did some testing with the indexed tvps interms of deadlocks. I did not find any difference between non-indexed and indexed versions. I created a simple application which spaws n number of threads and then each thread would insert m number of items into the tables. The surrogate PK in our database is Guid and we generate it in .Net with Guid.NewGuid(). The deadlocks that occur are index locks mainly because of the foreign checks.

Regards,
Mayur

mayur birari (3/6/2013)

---

Thanks for the quick reply. What I meant to say is I was able to produce deadlocks even with the indexed tvps.