Sql server ___________session 3(sql 2008)

Enhancements in
DML and XML DML
4

New Features of SQL Server 2008 / Session 4 2
Introduction

Some enhancements to Transact-SQL in DML
statements are:

Table value constructors

Table-valued parameters

MERGE statement

Enhanced GROUP BY clause, ROLLUP, and
CUBE operations

Enhanced XML SQL data type and XML
schema

Table Value Constructor 1-4
VALUES ( <row value expression list> ) [ ,...n ]
<row value expression list> ::=
{<row value expression> } [ ,...n ]
<row value expression> ::=
{ DEFAULT | NULL | expression }
Syntax
where,
VALUES: row value expression lists enclosed in
parentheses and separated by a comma
DEFAULT: inserts the default value defined for a column
if defined, otherwise, NULL
expression: can be a constant, a variable, or an
expression

Are also called row constructors

Allows to add multiple rows to a table using a
single INSERT statement

Example
--Inserting multiple rows using table value constructor
INSERT INTO Customer (CustomerId, Name, Grade, Rank) VALUES
(1 , 'Timothy Brown','A', 1),
(2 , 'Ambrose Pears' ,'B', 1),
(3 , 'Betty Scott', 'A', 2)
Here, using only a single INSERT statement, three rows have been
inserted. This is because of table value constructors.
Output


Features:

Table value constructor allows to insert
maximum 1000 rows

To insert more than 1000 rows, use:

multiple INSERT statements

bcp utility

BULK INSERT statement

Can also be used to insert multiple rows
having DEFAULT and NULL columns

Example
INSERT INTO Customer VALUES
(14, 'Weng Lee', NULL, 12),
(15, 'Rose Sanders', 'B', NULL),
(16, 'Mark Preston', NULL, NULL),
(17, 'Rob Mallory', 'A', 11)
For the first customer, the grade is optional, for the
second, the rank is optional, for the third both are
optional and for the last, both are specified.
Output

Table-Valued Parameters 1-3

Enables multiple rows of data to be sent to a
stored procedure

Need not use several parameters, XML, or
temporary tables to pass multiple rows of data

Very tedious in earlier versions of SQL Server


Is newly introduced in SQL Server 2008

Minimizes the additional server-side processing

Allows to create a variable of type table and pass
it to a stored procedure

Must be declared with READONLY attribute

Are actually temporary tables that are stored on
the server

Support both joins and queries
where,
schema_name: is the schema name
type_name: is the user-defined type name or alias type
<column_definition>: defines the columns for a
user-defined table type
<table_constraint>: can be PRIMARY KEY, UNIQUE, or
CHECK
CREATE TYPE [ schema_name. ] type_name
{
AS TABLE ( { <column_definition> }
[ <table_constraint> ] [ ,...n ] )
} [ ; ]
SyntaxThe following example creates a table type, called
EmployeeType.
Example
--Creating a TABLE type named EmployeeType
CREATE TYPE EmployeeType
AS TABLE
(EmployeeId int, Name varchar(50), Salary int,
DepartmentId int);

--Creating stored procedure with table valued parameter of type
--EmployeeType
CREATE PROCEDURE AddEmployee (@EmployeeList EmployeeType READONLY)
AS
SELECT * FROM @EmployeeList
GO
--Creating variable of type EmployeeType and populating it with
--values
DECLARE @EmployeeTable EmployeeType;
INSERT INTO @EmployeeTable VALUES
(4, 'Ethel George', 75000, 2);
INSERT INTO @EmployeeTable VALUES
(5, 'Robert Bernstein', 50000, 2);
--Passing the variable as an argument to the stored procedure
EXEC AddEmployee @EmployeeTable
OutputA stored procedure, AddEmployee, is created and a
variable, EmployeeList, of type EmployeeType is
passed to it.
EmployeeList is a table-valued parameter.

MERGE Statement 1-3

Allows to maintain a target table based on join
conditions on a source table using a single
statement

Enables to perform following in one MERGE
statement:

Insert a new row from the source if the row is missing
in the target

Update a target row if a record already exists in the
source table

Delete a target row if the row is missing in the source
table

MERGE Statement 2-3
MERGE target_table USING source_table
ON match_condition WHEN MATCHED THEN UPDATE SET Col1 =
val1 [, Col2 = val2...]
WHEN [TARGET] NOT MATCHED THEN INSERT (Col1 [,Col2...]
VALUES (Val1 [,Val2...])
WHEN NOT MATCHED BY SOURCE THEN DELETE [OUTPUT $action,
Inserted.Col1, Deleted.Col1,...] ;
Syntax
where,
target_table: is the table where changes are being made
source_table: is the table from which rows will be inserted, updated,
or deleted into target table
match_conditions: are the JOIN conditions and any other
comparison operators
MATCHED: true if a row in the target_table and source_table matches
the match_condition
NOT MATCHED: true if a row from the source_table does not exist in the
target_table
SOURCE NOT MATCHED: true if a row exists in the target_table but not in
the source_table
OUTPUT: An optional clause that allows to view those records that have
been inserted/deleted/updated in target_table
MERGE statements are terminated with a semi-colon (;)

MERGE Statement 3-3
Example
--Create a MERGE statement with CustomerMaster as
target table and CustomerTemp as source table
MERGE INTO CustomerMaster AS T USING CustomerTemp AS
S ON T.CustomerId = S.CustomerId
WHEN MATCHED THEN UPDATE SET T.Name = S.Name,
T.ContactNumber = S.ContactNumber WHEN NOT MATCHED
THEN INSERT (CustomerId, Name, ContactNumber) VALUES
(S.CustomerId, S.Name, S.ContactNumber)
WHEN NOT MATCHED BY SOURCE THEN DELETE
--Display the results
OUTPUT $action, Inserted.CustomerId, Inserted.Name,
Deleted.CustomerId, Deleted.Name;
In this code, CustomerTemp and CustomerMaster table are the
source and target tables respectively. The match condition is the column,
CustomerId, of both tables. If the match condition evaluates to false
(NOT MATCHED), then new records are inserted in the target table.
If match condition evaluates to true (MATCHED), then records are updated
into the target table from the source table.
If records present in the target table do not match with those of source
table (NOT MATCHED BY SOURCE), then these are deleted from the target
table.
The last statement displays a report consisting of rows that were inserted/
updated/ deleted as shown in the output.
Output

“GROUPING SETS” 1-2

Supports aggregation of multiple column
groupings and an optional grand total

More efficient to use GROUPING SETS
operators than multiple GROUP BY with
UNION clauses
where,
grouping set list: consists of one or more
columns, separated by commas
A pair of parentheses, (), without any column name
denotes grand total.
GROUP BY
GROUPING SETS ( <grouping set list> )
Syntax

“GROUPING SETS” 2-2
The example uses GROUPING SETS to display average marks
for every student. NULL values in Name indicate average marks
for every student. NULL value in both Id and Name columns
indicate grand total.
Example
SELECT Id, Name, AVG(Marks) Marks FROM Students
GROUP BY GROUPING SETS
(
(Id, Name, Marks),
(Id),
()
)
Output

"ROLLUP()", "CUBE()", and "GROUPING_ID()” 1-6

ROLLUP()

Is used with GROUP BY clause and can generate
aggregate rows, super-aggregate rows, or
cumulative rows, and a grand total row

Returns n + 1 groupings including a grand total
row, where n denotes number of grouping
elements

Changing of column order changes the output of
ROLLUP()
SELECT a, b, c, function ( <expression> )
FROM <object>
GROUP BY ROLLUP (a,b,c)
Syntax
where,
a, b, c: are elements in composite column list
function: is an SQL function, such as avg(), sum()
<expression>: is an SQL expression

GROUPING_ID() function – newly
introduced

Existing ROLLUP() and CUBE()
operators - enhanced

Example
SELECT DepartmentId, EmployeeId, Avg(Salary) AS
Average_Salary FROM Employee
GROUP BY ROLLUP (DepartmentId, EmployeeId);
Output


CUBE()

Is used with GROUP BY clause

Can generate a grouping of all permutations of
elements in the <composite element list>
present in GROUP BY clause

Number of groupings after performing CUBE
operation equals 2n, where n is the number of
elements in the <composite element list>

Column order does not change the output of
CUBE()
SELECT a, b, c, function ( <expression> )
FROM <object>
GROUP BY ROLLUP (a,b,c)
Syntax
It generates one row for each unique combination of values of
(a, b, c), (a, b), (a, c), (b, c), (a), (b), and (c).
It also calculates a subtotal for each row and a grand total row.

Example
Output
SELECT EmployeeId, DepartmentId, Avg(Salary) AS
Average_Salary FROM Employee GROUP BY CUBE
(EmployeeId, DepartmentId);


GROUPING ID()

Is a function used to find out the grouping set
responsible for producing a particular result row

One can provide all those elements as input to
the function that were used in a grouping set
after a ROLLUP or CUBE operation

Returns a unique integer for each grouping set
that can be used to filter out all unrelated rows
and display only relevant rows
GROUPING_ID()
GROUPING_ID ( <column_expression> [ ,...n ] )
Syntax
where,
<column_expression>: A column expression used in
a GROUP BY clause

Example
SELECT GROUPING_ID(EmployeeId, DepartmentId) AS
GROUP_ID, EmployeeId,
DepartmentId, Avg(Salary) as Average_Salary
FROM Employee
GROUP BY CUBE (EmployeeId, DepartmentId)
HAVING GROUPING_ID(EmployeeId, DepartmentId) > 0;
Output

GROUPING SETS Algebra 1-2
The example contains a combination of two
GROUPING SETS in the GROUP BY clause.
Example
SELECT Id, Name, AVG(Marks) AverageMarks FROM Students
GROUP BY
GROUPING SETS
(
(Id, Marks),
()
),
GROUPING SETS
(
(Name)
);
Output
 A query can have multiple GROUPING
SETS with a GROUP BY clause
 Separate GROUPING SETS clauses by
commas
 A comma represents a Cartesian product
between two GROUPING SETS clauses

GROUPING SETS Algebra 2-2
Example
SELECT Id, Name, AVG(Marks) AverageMarks FROM Students
GROUP BY
GROUPING SETS
(
(Id, Marks, Name),
(Name)
);
The query in the example is equivalent to the Cartesian product of
two GROUPING SETS clauses.
Name is combined with each of the other groups:
Output

Sql server ___________session 3(sql 2008)

Recommended

More Related Content

What's hot (20)

Viewers also liked (10)

Similar to Sql server ___________session 3(sql 2008) (20)

More from Ehtisham Ali (17)

Recently uploaded (20)

Sql server ___________session 3(sql 2008)