Bcp out - order and locking

Question

I wanted to understand a bit more about the order in which data is exported using bcp out command. It's written in some places that it returns values in index order, but what if there is no index on the table?(1) I understand that if we want data to be in a particular order we should use the ORDER BY clause in bcp queryout but what about bcp out?(2) If there is no particular order for bcp out then how are batches defined while writing to a file. Internal architecture says that it does a simple select * on the table. If this is the case then is it that in bcp, select * just streams data and then data is divided into batches just for the sake of writing to a file?(3)

Also, while doing bcp out, is the lock acquired on the whole table or just the batch?(4) There is a TABLOCK option but that can be used only during bulk import of data. Is it that bcp is just a client doing select * on the table and it will follow the locking principles of whatever isolation level we set for the transaction?(5) Here, it is said that bcp cannot be used with snapshot isolation, can someone please explain the reason more clearly?(6)

Is there any article explaining bcp internal architecture in more detail with ordering and locking of rows.

There are many articles with related questions for bcp import but couldn't find much on bcp export and hence asking here. Thanks

Answer 1

order in which data is exported using bcp out command

Without an ORDER BY column in a query the order is undocumented and plan-dependent. If you care, use the queryout option and an ORDER BY clause includes some key.

bcp is just a client doing select * on the table

Yes. Nothing special. You can use Profiler if you want to see what it's doing.

Also, while doing bcp out, is the lock acquired on the whole table or just the batch?

Since BCP just issues queries like any other client, normal locking rules apply.

it is said that bcp cannot be used with snapshot isolation

There's no way to specify the snapshot isolation level when using bcp out. You can use read committed snapshot isolation (RCSI) to achieve the same effect.