Summary

This chapter looked at how Transact-SQL is used in common applications and reporting solutions. A wide range of options are available for architecting and building database application solutions. Programming models vary in purpose and complexity. It's senseless to overengineer a solution with capabilities that won't ever be used — but it's also important to plan for future requirements. Striking the appropriate balance between these two principles is often the greatest challenge in database solution design.

Scalable solutions can accommodate a larger number of users without redesigning the fundamental application architecture. Scalable solutions make appropriate use of program components to separate the user interface from business logic and data access. How you implement Transact-SQL in an application plays an important role in its ability to handle whatever requirements may be discovered or imposed after the fact. Isolating database connections to separate reusable application components can simplify ongoing application design. Keeping SQL queries in database server objects, such as views, stored procedures, and user-defined functions, not only offers improved performance, but is also much more secure.

Finding the optimal balance between an application architecture that just gets the job done quickly and being able to adapt to any needs that could potentially arise is often a challenge. Ultimately, it is the long-term business requirements and objectives that should lead to choosing an application model that meets these needs. Fortunately, building scalable solutions is less costly using more capable programming objects and development tools.

Reporting is an important part of many business applications. SQL Server Reporting Services makes use of ad-hoc Transact-SQL statements, views, and stored procedures. These queries can include parameters to filter results and modify business logic. Reporting Services takes this concept to a level beyond SQL queries by allowing reports to filter cached data results and to provide rich, dynamic reporting capabilities, using report parameters and other advanced Reporting Services features.