sp_tableoption in SQL Server

Question

Hello everyone,

I want to know and pros and cons of setting sp_tableoption of "table lock on bulk load" for SQL Server 2005/2008? My scenario is, I have 3 threads working on database, one query thread, one bulk insert thread and one delete thread.

Here is the MSDN related link for sp_tableoption,

http://msdn.microsoft.com/en-us/library/ms173530.aspx

thanks in advance, George

Answer 1

just do it in the BULK INSERT instead

examples

Bulk Insert which will lock the table

BULK INSERT Northwind.dbo.[ORDER Details]
   FROM 'f:\orders\lineitem.tbl'
   WITH
     (
        FIELDTERMINATOR = '|',
        ROWTERMINATOR = ':\n',
        FIRE_TRIGGERS,
        TABLOCK  --this is it
      )

This statement will not lock the table

BULK INSERT Northwind.dbo.[ORDER Details]
       FROM 'f:\orders\lineitem.tbl'
       WITH
         (
            FIELDTERMINATOR = '|',
            ROWTERMINATOR = ':\n',
            FIRE_TRIGGERS
          )

BCP with tablock by using the -h (for hint) switch and "TABLOCK"

bcp pubs..authors2 in authors.txt -c -t, -Sservername -Usa -Ppass -h "TABLOCK"

you could do a bulk insert from within SSIS or DTS and to the table lock there

for example in DTS under transform data task look at the options tab, check use fast load and table lock SSIS has something similar