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.

What is CDC :-

Change data capture (CDC) is a set of software design pattern used to determine (and track) the data that has changed so that action can be taken using the changed data. Also, Change data capture (CDC) is an approach to data integration that is based on the identification, capture and delivery of the changes made to enterprise data sources.

It is a very common requirement for a data warehouse load package is to determine what has changed in the source systems and load this data into the warehouse.

All I wanted to give was a script that you can use along with adventure works Database to see what are basic stuff in CDC and how it works. off course you can use it in your Data warehouse SSIS packages.

---------------------------------------------------------------

USE AdventureWorks
GO
--Enable Change Data Capture on the Database--
EXEC sp_cdc_enable_db
GO

--Enable Change Tracking on a table--
EXEC sp_cdc_enable_table
    @source_schema = 'HumanResources' ,@source_name = 'Employee',@supports_net_changes = 1,@role_name = null
go
--Simulate a Data Change --
UPDATE HumanResources.Employee
SET   ManagerID = 10 WHERE Title = 'Production Technician - WC60'
go

select * from HumanResources.Employee where Title = 'Production Technician - WC60'

--Get the Data --
select sys.fn_cdc_get_min_lsn('HumanResources_Employee'),sys.fn_cdc_get_max_lsn() 
go

select *  from cdc.fn_cdc_get_all_changes_HumanResources_Employee
(0x00000036000017400078,0x0000003600001EC1006C,'all')

declare @minLSN varbinary(max),@maxLsn varbinary(max)

select @minLSN = sys.fn_cdc_get_min_lsn('HumanResources_Employee')
,@maxlsn=sys.fn_cdc_get_max_lsn() 

select *  from cdc.fn_cdc_get_all_changes_HumanResources_Employee
(@minLSN ,@maxLsn ,'all')

--Disable CDC as I dont want this running on my Laptop--
EXEC sp_cdc_disable_db
GO
/*
    WHEN 1 THEN 'delete'
    WHEN 2 THEN 'insert'
    WHEN 4 THEN 'update'

*/

--exec sp_configure

-----------------------------------------------------------------------------------------------------

It might not the be most trivial Stuff but I saw some posts on microsoft forums for this . So I thought I should blog it

select Getdate() -- this will give you todays date

SELECT DATEADD(wk, DATEDIFF(wk, 6,Getdate() ), 6) --First day of the week
SELECT DATEADD(wk, DATEDIFF(wk, 5, Getdate() ), 5)--last day of the week

On forums http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=4119871&SiteID=1