Is there a way to tell SQLCMD not to output rows to text or a file?

Question

I am testing the behavior of a SELECT statement that returns 170,000,000 rows using SQLCMD. My goal is to cut out the time spent receiving the result-set on the client end (the time the process shows the ASYNC_NETWORK_IO waittype while an output file is being written to disk). I would like to have SQLCMD run my SELECT statement but not actually save or output the result-set. Just returning the number of rows affected would be fine. Is this possible?

sqlcmd -E -t 0 -S server\instance -d databaseName -i hugeselect.sql -o none?

Answer 1

This isn't an answer for SQLCMD specifically (I don't know of an option to discard results), but an option for testing the performance of a query and ignoring the network overhead or client-side rendering is to use the free version of SQL Sentry Plan Explorer. By design we drop the results at the server and don't bring them across, allowing you to focus on the actual performance on the query rather than the performance of your network or the power of your desktop.

Disclaimer: I work for SQL Sentry.

Answer 2

You could try piping it to nul, see if that helps, eg:

sqlcmd -E -t 0 -SyourServer -Q"SELECT * FROM sys.objects" > nul

Answer 3

Remus Rusanu had the best answer in his first comment.

sqlcmd -E -t 0 -S server\instance -d databaseName -i hugeselect.sql -o nul:

While this didn't completely eliminate the client time, it cut it down to being as fast as possible.