Explore BrainMass

Explore BrainMass

    data manipulation capabilities

    Not what you're looking for? Search our solutions OR ask your own Custom question.

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

    What are the data manipulation capabilities found in SQL Server 2005? Examine the broad range of data manipulation features and provide a summary.

    © BrainMass Inc. brainmass.com March 4, 2021, 7:27 pm ad1c9bdddf

    Solution Preview

    Please see the attached file.

    Overview of T-SQL Enhancements
    Most of SQL Server 2005's T-SQL enhancements focus on offering greater expressiveness to queries. We will look at each of these individual enhancements in detail. The list of enhancements includes:
    • Relational operators
    • TOP
    • Recursive queries
    • XML showplan
    • Snapshot isolation level
    • Data types
    • DDL triggers
    • DML with output
    • Exception handling
    • Database Schemas
    • Script Storage in Project
    • Xquery - XML Query Language
    • XML Data Type
    • varchar(max) & nvarchar(max) Data Types
    • varbinary(max) Data Type
    • Recursive Query
    • Common Table Expression - CTE
    • NTILE
    • Partitioned Table, Partition Function, & Partition Scheme
    • .NET Assembly support
    • OUTPUT Clause
    • TRY and CATCH for Exception Handling
    • TOP and TABLESAMPLE Clauses
    • EXECUTE AS Clause
    • MARS - Multiple Active Result Sets
    • Snapshot Isolation
    • Indexes with Included Columns
    Relational Operators
    SQL Server 2005 introduces three new relational operators: PIVOT, UNPIVOT, and APPLY.
    The PIVOT operator is very similar to the TRANSFORM operator that Microsoft Access provides. It is used to rotate rows into columns. The best way to learn about PIVOT is through an example (see Figure 1).
    This table lists the sales of a Toyota dealership. Notice that some models have more than one row for a particular year. Now we apply the following SELECT query (which uses the PIVOT operator) to the SalesSummary table. The result we get is shown in Figure 2.
    SELECT *
    FROM [SalesSummary]
    PIVOT (SUM ([UnitsSold]) FOR [Year] IN ([2003], [2004])) AS [T]
    The table was PIVOTed on the year column. Notice how the unique year values have taken the shape of columns and the corresponding number of units sold have been summed up.
    The UNPIVOT operator does the opposite of what PIVOT does - it rotates columns into rows. Let's say we want to rotate the table in Figure 2 (let's call it Year WiseSalesSummary), so that the number of units sold is displayed for each year. All we have to do is apply the following SELECT query. The result of the UNPIVOT operation is shown in Figure 3.
    SELECT *
    FROM [YearWiseSalesSummary]
    UNPIVOT (UnitsSold FOR [Year] in ([2003], [2004])) AS T
    The APPLY is another new operator available in SQL Server 2005. When used in the FROM clause, it invokes a table-valued UDF for each row of a table. The UDF can optionally use the columns of the table as arguments.
    Let's say we have a table called ...

    Solution Summary

    The data manipulation capabilities found in SQL Server 2005 are determined.