What can speed up a SQL count query?
Netezza is an appliance that is designed to excel at large table scans, so that's why you're getting such fast results on that system.  

For your SQL Server, you can greatly speed up the row count by querying from the sys.dm_db_partition_stats DMV. 

SELECT s.name AS [Schema], o.name AS [Table], SUM(p.row_count) AS [RowCount]
FROM sys.dm_db_partition_stats p JOIN sys.objects o
ON p.object_id = o.object_id JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE p.index_id &lt; 2
AND o.object_id = object_id('MyTable')
GROUP BY o.name, s.name;


In a high transaction environment, this DMV is not guaranteed to be 100% accurate.  But from your question, it sounds like you are just doing row counts to verify each table after your migration, so this query should work for you.