Share
Explore BrainMass

Subquery and Join

1. Stored procedures in SQL 2005 can be written in a variety of languages. Why is this important in the development of database systems? Provide an example of a stored procedure that a database designer might write to demonstrate why this capability is important. Please include references.

2. Which is better to use, a sub query or a join? Why? Please include example of each to support your reasoning. Also, please include references.

Solution Preview

1. Stored procedures in SQL 2005 can be written in a variety of languages. Why is this important in the development of database systems? Provide an example of a stored procedure that a database designer might write to demonstrate why this capability is important. Please include references.

Benefits of Stored Procedures [1]:
* Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.
* Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.
* Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.
* Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions.

Example
======
Assume we have the table shown below, named ...

$2.19