1

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?

1

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 viewname all day, but the underlying definition is whatever you want.

  • They use development and acceptance databases. I do care because if everyone keeps firing queries on huge tables, these might affect negatively other developers, and they would have a wrong impression when evaluating their own query performance. – Junior M Aug 15 '14 at 14:43
  • 2
    The idea of using views is really nice. – Junior M Aug 15 '14 at 14:49
0

No, there is no policy for this. You could perhaps figure out a way, e.g. via Group Policy somehow, to set this value to 100...

enter image description here

...but that doesn't really solve the problem, and will affect all environments they connect to, including their local instance(s).

You can prevent SELECT * by adding this computed column to the table (or to a view that references the table):

ALTER TABLE dbo.whatever ADD [No Select Star!] AS (CONVERT(INT,1/0));

But again, that doesn't prevent them from selecting all rows. I have to agree with Thomas here - why are these developers allowed to run whatever ad hoc queries they feel like against critical systems? Do you really want them to even be able to do this:

SELECT TOP (100) * FROM dbo.BigTable, dbo.BigTable AS b ORDER BY NEWID();

(Go ahead, try it - even a query that is somehow artificially limited to return 100 rows can bring your system to its knees.)

What you could do is deny them direct access to the tables, and force their access through stored procedures - which take parameters for row counts etc., and the code could cap how high those could go (either by checking or using a parameter of type TINYINT).

  • I am aware of this configuration on SSMS. I was thinking if SQL Server had a special configuration on server level. Deny access to those tables could be a way. It would be nice to intercept those queries, identify which tables are being used, and limit results. But it seems SQL Server do not offer that on server level. :( – Junior M Aug 15 '14 at 14:47
  • @Junior it still wouldn't solve the problem, even if SQL Server really wanted to provide you a way to handcuff your queries. I demonstrated a TOP (100) query that will bring your system down. Forcing access through stored procedures might be a little more work but gives you much more control than even views would (since you can't parameterize a view, or perform conditional logic etc). – Aaron Bertrand Aug 15 '14 at 14:50
  • Yes good point! – Junior M Aug 15 '14 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.