Export data from SQL Server to CSV as fast as possible

Exporting large volumes of data from SQL Server is a common requirement for analytics, data migration, backups, or data sharing. However, not all export tools deliver the same performance, scalability, or ease of use.

In this article, we compare several common approaches using the TPC-H orders table as a benchmark:

• BCP (CLI – native SQL Server tool)
• SSIS (No-code ETL)
• sqlcmd (CLI – scripting oriented)
• FastBCP (CLI – high-performance parallel exporter)

---

Test context

We use the TPC-H orders table, typically containing millions of rows:

SELECT COUNT(*) FROM dbo.orders; -- ==> 15M rows

Assumptions:

• SQL Server 2019+
• Export to CSV format
• Local SSD storage
• Minimal transformation (raw export)

---

1. BCP (Bulk Copy Program)

BCP is the historical command-line tool provided by Microsoft. It is fast, lightweight, and reliable, but limited to single-threaded exports.

BCP export command

bcp "SELECT * FROM dbo.orders_15M" `
queryout D:\temp\orders_bcp.csv `
-c `
-t ";" `
-r "\n" `
-S localhost `
-d tpch_test `
-T -u

---

2. SSIS (SQL Server Integration Services)

SSIS is a graphical ETL tool suitable for non-developers and complex pipelines. It can reach good performance but requires more setup and infrastructure.

Typical SSIS flow

• OLE DB Source → orders table
• Flat File Destination → CSV

Key performance settings:

• Appropriate DefaultBufferMaxRows
• Minimal transformations

---

3. sqlcmd

sqlcmd is a scripting-oriented CLI tool. It is flexible but not designed for high-volume exports.

sqlcmd export command

measure-command {sqlcmd -S localhost -d tpch_test -E `
-Q "SET NOCOUNT ON; SELECT * FROM dbo.orders_15M" `
-o D:\temp\orders_sqlcmd.csv `
-s ";" -W
}

---

4. FastBCP (High-performance parallel export)

FastBCP is a modern CLI tool designed specifically for maximum export speed. It uses parallel extraction to fully leverage modern CPUs and storage.

Official website: https://fastbcp.arpe.io

FastBCP export command

.\FastBCP.exe `
--connectiontype "mssql" `
--server "localhost" `
--database "tpch_test" `
--trusted `
--sourceschema "dbo" `
--sourcetable "orders_15M" `
--query "SELECT * FROM dbo.orders_15M" `
--directory "D:\Data\fastbcpexport\raw\{sourcedatabase}\{sourceschema}\" `
--fileoutput "{sourcetable}.csv" `
--decimalseparator "." `
--delimiter ";" `
--dateformat "yyyy-MM-dd HH:mm:ss" `
--encoding "UTF-8" `
--method "RangeId" `
--distributekeycolumn "o_orderkey" `
--paralleldegree -2 `
--merge false

Key concepts

• Table or query can be split using a parallel method
• Multiple concurrent readers
• Linear scalability with CPU cores

---

Performance comparison (typical results)

Tool	Speed	Dev Time	Parallel	Complexity	Limited to SQL Server
sqlcmd	Slow (38s)	Fast (30s)	No	Low	Yes
SSIS	Slow (39s)	Slow (10min)	No	High	No
BCP	Fast (21s)	Fast (30s)	No	Low	Yes
FastBCP	Very fast (3s)	Fast (30s)	Yes	Low	No

---

Conclusion

If you need a quick and simple export, BCP remains a solid choice. For complex ETL pipelines and workflows, SSIS is appropriate. For scripting, sqlcmd is convenient but has some issues and is limited to SQL Server.

However, when the objective is exporting large SQL Server tables as fast as possible, FastBCP clearly stands out thanks to its parallel architecture and CLI simplicity. Bonus: it can do the same for many other databases and formats (Parquet, JSON, XLSX).