hi2u (1/8/2013)
Sometimes i get the following error on usp_Table1"Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in onject 'dbo.Table1'
i am not agree you will get this error instead use should get "incorrect syntax near table1 as truncate missing the 'table' word moreover , do you have any DML trigger ?
hi2u (1/8/2013)
The profiler showed that the update was called while the truncate/insert was processing
Confusion here, whatever the sequence you use to exec the Sps, thet are not going to conflict or block eachother..unless you explicitly not COMMITing the transaction in case of second SP.
hi2u (1/8/2013)
I thought a select generated a shared lock for the exact purpose of blocking the update.
from BOL
The shared lock is placed on the "resource" only as long as it is needed. Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction
hi2u (1/8/2013)
the bit index was useless or not, i removed it for performance testing and i found out that the duplicate key error never showed up again.
index ae not related with error or vice versa. index are here to speed up the query result.
What i can sense here that you need to go through folowing topics.indexes, locks, how to read profiler trace,transations
can you post the sequence you used to exec the SPs plus the screenshot of profiler trace where u get PK violation error.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)