When looking at the SQL used in a report in CUIC, it suggests that ALL the fields from the associated Report Definition are used in the query sent to SQL Server even though most of the fields don't appear in the actual report. Does CUIC optimise the query before it sends it out to SQL Server to query the database so it only returns the fields needed to display in the report?
I ask the question as I don't know whether to create one very large Report Definition, suitable for many reports, or should I create smaller (more optimised) Report Definitions one per report?
I'm pretty sure there's no optimization occurring in the SQL that CUIC executes. It should be executing exactly the SQL statement that you see when you click the "SQL" button in a report.
Having fewer Report Definitions is much, much simpler from an administrative standpoint. If you make multiple for the same data, you run into all sorts of hassles if you later find out you built a field wrong, or weren't specific enough in a JOIN statement, or whatever. With the maximum output for reports set at 8000 rows for historical and 3000 rows for real-time, I've found that I rarely ever have a useful report that takes very long to run. What I would recommend, if you're concerned about server resources, is to do "SELECT TOP 8001" in your historical queries, and "SELECT TOP 3001" in your real-time queries... this keeps most reports from getting out of control, and on any filters that provide tons of data, the user will still see the "More than 8000 rows, please narrow your filter criteria" message.
One of the benefits of having Report Definitions that bring in all/most fields from a set of tables is that you can build Reports with multiple useful Views on them, reducing the number of entries in your Reports folder structure. I find that my users just get confused if there are too many Reports for them to choose from. It's easier as a user to just pick "Call Type Historical Daily", select the days and Call Types to report on, and flip through the different Views to get the data you're looking for.
I only create new Definitions if I need significantly different output... different "GROUP BY", summarizations, bringing in completely different tables, or providing different Filters.