SQL Kit

Some time back I took a small session for a group of SQL Server users about some cool stuff in SQL Server 2008 from the T SQL point of view. Some of the students wanted to have access to the scripts , so here they are

MERGE STATEMENT and OUTPUT CLAUSE

drop table Source
drop table Destination

go

CREATE TABLE Source (id int,val varchar(56));

CREATE TABLE Destination (id int,val varchar(56));

GO

INSERT Source VALUES (1,’A');
INSERT Source VALUES (2,’B');– This will be inserted

INSERT Destination VALUES (1,’q');–this will be updated to A
INSERT Destination VALUES (6,’w');
INSERT Destination VALUES (8,’r');

GO

MERGE Destination D — target table

        USING Source S — source table

        ON S.ID = D.ID

        WHEN MATCHED  THEN

                UPDATE set D.val = S.val

          WHEN NOT MATCHED THEN

                — insert a row if the stock is newly acquired

                INSERT VALUES (S.ID, S.VAL)

        — output details of INSERT/UPDATE/DELETE operations

        — made on the target table

        OUTPUT $action, inserted.*, deleted.*; —this is not required in Merge statement I have kept it here just to demostrate that from SQL 2005 onwards
                                                       — OUTOUT clause can be used along with INSERTED , DELELED to get the what has been done as a result to DML action
                                                       — Might help in auditing the DML actions
/*output of the OUTPUT CLAUSE IS

$action    id    val        id        val— I can see what is Inserted and what is Deleted
INSERT    2    B        NULL    NULL
UPDATE    1    A        1        q

*/                                                      

SELECT * FROM Destination;–See the result

GO

 

GROUPING SETS

GROUPING SETS are a new feature of SQL Server 2008. Using them will allow multiple groupings to be returned in one record set.

We will grouping on City and StateProvice in the same query

use AdventureWorksLT
go
—old way —————-
SELECT NULL, City, COUNT(StateProvince) AS Nbr
FROM SalesLT.Address
GROUP BY  City
UNION ALL
SELECT StateProvince, NULL, COUNT(StateProvince) AS Nbr
FROM SalesLT.Address
GROUP BY StateProvince

–useing GROUPING SETS in SQL 2008
SELECT StateProvince, City, COUNT(StateProvince) AS Nbr
FROM SalesLT.Address
GROUP BY
GROUPING SETS
(
  (City),
(StateProvince)
–()
  );

TABLE VALUED PARAMTERS

In this example I will pass a table valued parameter in the stored procedure

–USING Table valued input Parameters
CREATE TYPE myTableType AS TABLE (id INT, name NVARCHAR(100),qty INT);

ALTER PROCEDURE myProc (@tvp myTableType READONLY) AS

declare @table TABLE (id int)

    UPDATE Inventory SET
    qty += s.qty
    FROM Inventory AS i INNER JOIN     @tvp AS tvp
    ON i.id = tvp.id
GO

 

NEW DATE TIME DATA TYPES

– IN SQL 2008 INSTANCE
declare @datetime datetime,@DATETIME2 DATETIME2
select @datetime = ‘1500-03-04 15:43:26.857′–this will give out of range exception asyou cannot have anything before 1900
select @datetime2 = ‘1500-03-04 15:43:26.857′– there you can do it
SELECT @DATETIME2

DECLARE @DATE DATE , @TIME TIME
SELECT @DATE =’1900-01-01′,@TIME = ‘15:43:26.857′
SELECT @DATE ,@TIME

DECLARE @DATETIMEOFFSET datetimeoffset
SELECT @DATETIMEOFFSET = ‘2005-09-08 12:20:19.345 -08:00′    — stored datetime with OFFSET Lets say you want datetime datetime captured in LONDON server transfrom to US and lets assume the offset is 8 hrs
SELECT @DATETIMEOFFSET

————————————————————————————————————————–

Thats all for now!! I though it will be easier to get the scripts from here rather than download from my sky drive. It is not a very extensive list , it just covers the 1 hours session which I took for beginner sql users.