Most of the PowerShell guys may need to extract data from SQL databases. There could be two or more ways to do the same task however as per my latest findings in one of the project I figured out including BCP in our script is the best way to achieve that. In my following two examples I am using AdventureWorksDB and a SQL query that write data into a text file.

Example 1 using Invoke-SQLCmd.

$SQLQueryy= "USE AdventureworksDW2016CTP3
SELECT  ProductKey, OrderDateKey, ShipDate FROM [dbo].[FactResellerSalesXL_CCI]
WHERE ProductKey =532"
$StartTime = (Get-Date)
Invoke-Sqlcmd -ServerInstance SQL2 -Query $SQLQueryy -QueryTimeout 65535 |Format-Table -HideTableHeaders -AutoSize |Out-File 'C:\temp\via_InvokeSQL.txt'  -Width 500
$Endtime=(Get-Date)
$TotalTime = "Total Elapsed Time : $(($Endtime-$StartTime).totalseconds) seconds"
$TotalTime

This script takes almost 14 seconds to extract the rows from SQL.

Example 2 Using BCP

$StartTime = (Get-Date)
bcp  "USE AdventureworksDW2016CTP3 SELECT  ProductKey, OrderDateKey, ShipDate FROM [dbo].[FactResellerSalesXL_CCI] WHERE ProductKey =532"  queryout 'C:\temp\via_BCP.txt' -T -c -S SQL2
$Endtime=(Get-Date)
$TotalTime = "Total Elapsed Time : $(($Endtime-$StartTime).totalseconds) seconds"
$TotalTime

This query take less than 1 seconds with neat & clean output file.