Hi Jayanth,
QUESTION
Hope you are doing good.
My question is :
Is there a way 2 select from tables with no timeout when u r inserting data into them via BCP/bulk insert with tablock? does nolock work on select statement tables works?
ANSWER
yes, it's called optimistic concurrency. If you enabled the above feature you will get committed data only, i.e. none of the data from BCP will show up until the BCP completes.
If you use NOLOCK you get the data with dirty reads since NOLOCK reads uncommitted data.
Depending on the nature of the requirement either of the above should work for you. My recommendation is Optimistic concurrency but keep in mind the temp db usage.
ALTER DATABASE dbnamehere
SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE dbnamehere
SET ALLOW_SNAPSHOT_ISOLATION ON;Please Consider Subscribing
