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