I wrote a Powershell script using Invoke-Sqlcmd to test some T-SQL queries in a SQL Server 2012 server. I only want to make the script check whether there is some record returned or not
, but don't care how many record.
The code I currently uses is like:
$queryResult = Invoke-Sqlcmd -Query $q -ServerInstance $serverInstance -Database $dbName -Username $dbUser -Password $dbPassword -ErrorAction Stop
I encounter an issue that some query returns millions of records, such that it takes long time for the script to run and return them all to my workstation through the network. Can you help me to find a way to speed it up? I only need the script to check whether there is data return, and don't care how many.
I tried some ways but they didn't seem to work:
1. tried parameters in Invoke-Sqlcmd (MaxBinaryLength and MaxCharLength) and hope they can limmit the return length, but doesn't work
2. try to add "SELECT TOP 1 * from () as x" around the original T-SQL query, but this does not work for SELECT statement having "WITH" clause