I have been seeing developers using SELECT * FROM table when testing their queries.
The issue is that many developers are executing such SELECT statements on huge tables, which slows down SQL Server performance.
We have told them to use SELECT TOP 100 * FROM table, but they don't do it.
Is there a policy or special configuration to make SQL Server show limited results (i.e. 100 rows) on huge tables? If so, which SQL Server version does that?
Best Answer
There is no native way to accomplish directly what you are looking for. Why do developers have access to systems they can inherently affect the performance on where it matters? Are these production systems? If so, do these developers need access to prod? If not (non-prod), then why do you care what developers do to development database servers? Maybe I'm missing something. Developers and non-operational professionals typically don't have access to environments where performance impact is considered.
Outside of the business process, a solution that you could use if you are unable to change access is Resource Governor to isolate resource usage.
Another thing you could do is create views where you embed your "top" logic, and give the developers access to the views but not the underlying tables. That way they could do
select * from viewnameall day, but the underlying definition is whatever you want.