Summary


When designing a database solution, it is important to see the bigger picture. Ideally, queries and other database operations should be contained in manageable database objects. In the long run, views, stored procedures, and user-defined functions provide improved security, performance, and simplicity. In most databases, this is a significant investment and may take time and considerable effort.

Views are SELECT statements that can be treated as a table. By organizing complex queries into views, users and programmers don't have to contend with the complexities of a database design and can focus on their pertinent data and business problems. Federated and partitioned views allow data to be stored on different physical media and in different locations, all of which may be transparent to the consumer.

Stored procedures can simplify common operations, providing a standard approach for managing records and high-level data entities. Complex business logic can be processed in a stored procedure, complete with decision branching and error handling. Stored procedures are reusable objects that run efficiently because the SQL script is compiled and optimized.

User-defined functions can provide the same functionality as views and have a lot of the same capabilities as stored procedures. In some ways, UDFs are the best of both approaches because they support the more common SELECT statement rather than having to be executed like a stored procedure.

Each of these three objects still has its place in SQL programming. Views are far more common than UDFs and under certain conditions may be more efficient. Stored procedures can use nondeterministic functions, which are not allowed in UDFs. The one thing that functions provide that views and procedures do not is the ability to encapsulate the logic to return scalar values. Use UDFs to simplify parsing, calculations, and value manipulation.




Beginning Transact-SQL with SQL Server 2000 and 2005
Beginning Transact-SQL With SQL Server 2000 and 2005
ISBN: 076457955X
EAN: 2147483647
Year: 2006
Pages: 131
Authors: Paul Turley

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net