Export data from SQL Server 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
 

Pros

• Fast for single-threaded export
• No additional dependencies
• Stable and well-known

Cons

• No parallelism
• No Header :-(
• Limited control over formatting
• Limited log output or too long
• No compression
• No partitioning output
• Export only locally
• Export only CSV or SQL binary format
• Only SQL Server

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

Pros

• No code required
• Scheduling
• Logging
• Monitoring
• Work for many databases
• Can make complex transformations

Cons

• No parallelism
• Heavier infrastructure (service, database
• Slow Dev
• Slow to scale to hundreds of tables
• Lower raw performance than CLI tools
• Not ideal for simple exports
• Export only locally

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
}


 

Pros

• Very simple
• Good for automation scripts
• No additional tooling

Cons

• No parallelism
• No Logs
• Bad Header : need some trick to remove the 2nd line
• Only work with SQL Server
• Export only locally

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\Query can be splitted using a parallel method
• Multiple concurrent readers
• Linear scalability with CPU cores

Pros

• Much faster than BCP, SSIS and sqlcmd
• True parallelism
• Simple CLI usage
• Clean header without headacke
• Drivers included
• Templating for output files names and directories
• Work on other databases (Oracle, MySQL, PostgreSQL, Netezza, ODBC...)
• No installation (just copy the .exe file)
• Works on windows and linux
• Can also export to cloud (AWS S3, S3 compatibles, Azure (ABS & ADLS), GCS, OneLake)

Cons

• External tool
• Can be cpu intensive (but this is what you want to go faster)

Performance comparison (typical results)

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 some issue and limitated 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 format (parquet, json , xlsx)