This page will provide links to helpful information, with examples, about using SQL Server to create views, stored procedures and more.
A view is, essentially, a query design that is stored on the server. Views can be used to provide a table-like structures that provide summarised or complex data in a simple format. We would recommend that all external reports (e.g. Crystal, Reporting Services, Excel etc) should be written against views or stored procedures. This can dramatically simplify report design and can improve reporting performance. It can also make it easier to deal with database changes during an upgrade.
Stored procedures are, effectively, mini SQL programs. They can be used to re-organise data, automate tasks or provide reporting data. We would normally write reports to run against stored procedures, as it gives us the maximum flexibility in deciding how to assemble and present the data.
SQL Server functions can used to return a single value from a complex set of instructions. One example might be to return the previous year's YearCode, i.e. fn_PriorYear('2005/06')='2004/05'.
Database Indexes provide a means of looking up information in a table more quickly than simply scanning all the rows. The tables in your finance system will already have a number of Indexes on them that are created by the finance system itself. If, however, your reports are very slow it may be because you need extra Indexes to help the Server locate the information you need. If, for example, you store vital information in a UserField and you need to return data based on criteria in the field (e.g. UserField1='AMX01') an Index on the field may help.
If you find that your reports are running more slowly than is acceptable, how do find out what is going wrong? It can be helpful to use SQL Server Profiler to trap the SQL generated by the report, paste it into SQL Server Query Analyzer and use Tools/Display Estimated Execution Plan to see how the Server is accessing the tables. This can help you to decide whether to re-write the query, or try adding another Database Index.