Explore BrainMass
Share

Explore BrainMass

    Subquery and Join

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    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.

    © BrainMass Inc. brainmass.com October 9, 2019, 7:50 pm ad1c9bdddf
    https://brainmass.com/computer-science/sql/subquery-and-join-130877

    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