BCP out sorting and first last row parameters

Question

I want to export a large table using bcp. I get the count of the table, then divide that by 250k, then run one bcp export for each batch. (it's much faster this way)

bcp my_table out c:\filepath1 -F 0 -L 249999
bcp my_table out c:\filepath2 -F 250000 -L 499999
bcp my_table out c:\filepath3 -F 500000 -L 749999
etc

How does it know to sort these all the same way? What's the default sort of a bcp export?

Answer 1

Hi @meyerovb ,

> What's the default sort of a bcp export?

Quote from an old thread, use "queryout" rather than "out" with a query ordering by the clustered index. Then, if you don't change the indexed values, you can load using the ORDER hint.

bcp "SELECT * FROM <table> ORDER BY <clustered index column(s)>" queryout <filename> -T –N  

Please also check if this old thread Does bcp out maintain row order while exporting into a data file could help you.

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.

Answer 2

It doesn't sort. It is using the clustered index.

However, the method you are using will not result in proper files, if any change is done to the table. You may get duplicate or missing rows.

I find it hard to believe it is faster exporting the data. The BCP command still has to run the entire table to determine the row numbers.

Answer 3

How does it know to sort these all the same way?

I don't think it cares.

What's the default sort of a bcp export?

Whatever it sees fit.

That is, you could get the same data in all three files.

In practices, it will go by the clustered index - but hey, would there be an NCI index with all columns it may go with that time. And how knows, maybe flip a coin from time to time.