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)

La nouvelle base de données de SAP, HANA, nouveau cheval de bataille de l'éditeur allemand est disponible en version Cloud pour quelques partenaires et clients testeurs. Cette base de données approche le stockage avec une technologie "In-Memory" pour une atteindre une performance d'accès aux données améliorée. Certifiée pour certaines machines avec une configuration OS et Hardware optimisée, cette base de données prend donc logiquement l'appelation convoitée "d'Appliance" : à savoir un ensemble Hard+Soft combiné et paramétré pour des performances optimales.

SAP annonce le stockage de données BIG DATA sur cette base et des temps de réponses encore jamais vu. Pour en avoir le coeur net et pour pimenter l'essai j'ai intégré des données du réseau social professionnel LinkedIn dans cette base de données via un connecteur spécialisé pour Informatica PowerCenter, transformant ainsi le test HANA en un test multiple de connectivités.

Le socle ETL est une VM sous Windows 2008 R2 avec Informatica PowerCenter 9.1 64 bits SE.
Cotésconnecteurs :
- Les drivers ODBC 32 et 64 bits pour SAP HANA
- Le connecteur Informatica PowerExchange pour LinkedIn 9.1