New SQL 2008 Features

Table Valued Parameters :
Pass large amounts of data efficiently to functions and procedures using new Table-Valued Parameters.

First we have to create User defined table type parameter. see below

CREATE TYPE dut_dept AS TABLE
(
dept_id INT, deptname VARCHAR(50)
)

Next we can use this Parameter in Stored Procedures,

CREATE PROCEDURE sp_insertdept
@tbl_dept dut_dept READONLY
AS
INSERT INTO tbl_department(dept_id,dept_name)
SELECT * FROM @tbl_dept;

We can use this table parameter to Insert, Update operations in SQL.

To Pass this table valued Parameter, in ASP.Net we have to define the SQL Parameterdatatype as stuctured data type like below,

tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.dut_dept";

We can use Datatable as input for Table valued Parameter.

ROLLUP function:

This function is used to calculate the grand total in Group by function.

SELECT
CASE
  WHEN GROUPING(Department) = 1 THEN 'Company Average'
  ELSE Department
END AS Department,
AVG(Salary) as AvgSalary
FROM Employees
GROUP BY Department
WITH ROLLUP