data manipulation capabilities

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
• PIVOT and UNPIVOT
• INTERSECT and EXCEPT
• OVER(PARTITION BY...)
• Recursive Query
• Common Table Expression - CTE
• RANK and DENSE_RANK
• NTILE
• ROW_NUMBER
• Partitioned Table, Partition Function, & Partition Scheme
• .NET Assembly support
• CROSS APPLY and OUTER APPLY
• 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 ...