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
OK, so you could not create a case where a deadlock occurred, but they can happen more often when TVPs are used if the underlying table type does not have a clustered index. You'll find this case documented (I am on my phone so don't have easy way to look it up) mostly when the TVP participates in a MERGE statement but I could also see it being common when it participates in any JOIN within a transaction too.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato