Some argue that all queries should only return the exact data they need as this would reduce I/O etc...
And they are correct!
...others argue that it is OK to have a query that returns a few extra columns such as int or nvarchar(50) as this reuses execution plans and makes development faster
These folks are also correct!
There are always tradeoffs with these things.
Imagine a web page where users can edit their profile, populated by "query 1" here:
SELECT Id, FullName, UserName, Phone
FROM dbo.Users
WHERE Id = @Id;
And then there's an admin page that uses "query 2" here:
SELECT Id, FullName, UserName, Phone, IsActive, AdminComments
FROM dbo.Users
WHERE Id = @Id;
Performance
For the situation where we're using both queries, we have (non-exhaustive list of pros and cons):
- Con: two different plans taking up space in the plan cache
- Con: developers had to write two different queries / stored procedures
- Pro: the first query will only be doing the work it needs to (no extra I/O)
- Pro: the two queries can be optimized differently
- Pro: the two queries can be indexed for separately
If we just have query 2, the situation is basically inverted (only one plan in the cache, only one query / proc had to be written, but the query uses more I/O, etc).
Security
There's also an aspect of security: you might not want those "admin only" columns getting queried for non-admin pages, even if they are never presented to the user.
#Maintainability
Sharing the same query for multiple different pages that don't need all that data can also lead to maintainability problems down the road. What if query 2 suddenly needs to join to other tables, in order to pull in additional information about user activity that's not available to regular users?
Now the normal user page is being impacted by the admin query. And developers at that time might not realize they need to go back and check on the regular user page.
What if some enterprising developer removes columns from query 2, because it's decided admins don't care about them? Now the regular user page is actually broken.