Top 3 SQL Server 2008 Feature

/*

Tips 1
*/
DECLARE @Counter INT = 10
SELECT @Counter 
DECLARE @Sales TABLE (EmployeeId INT, Yr INT, Sales MONEY, MyCounter INT)

INSERT @Sales VALUES
(1, 2006, 12000, @Counter),
(1, 2007, 18000, @Counter+1),
(1, 2008, 25000, @Counter+2),
(3, 2006, 20000, @Counter+3),
(3, 2007, 24000, @Counter+4);
-- Assignment operators
UPDATE @Sales set Mycounter += 1

/*
Tips 2
*/
CREATE TABLE EmployeeSRCTbl(EmployeeId INT, EName VARCHAR(20), Age INT)
CREATE TABLE EmployeeTRGTbl(EmployeeId INT, EName VARCHAR(20), Age INT)
INSERT INTO EmployeeSRCTbl VALUES

     (1, 'Ganguly', 35),
     (2, 'Dravid', 34),
     (3, 'Dhoni', 30);

INSERT INTO EmployeeTRGTbl VALUES
       (1,'Ganguly', 35),
     (2,'Rahul Dravid', 34),
     (4,'Dhoni', 30);

MERGE [EmployeeTRGTbl] t
    USING [EmployeeSRCTbl] s ON t.EmployeeId = s.EmployeeId
WHEN MATCHED THEN
     UPDATE SET t.EName = s.EName, t.Age = s.Age
WHEN NOT MATCHED THEN
     INSERT VALUES(EmployeeId, EName, Age)
WHEN NOT MATCHED BY SOURCE THEN

     DELETE;  

DELETE FROM EmployeeTRGTbl
DELETE FROM EmployeeSRCTbl
SELECT * FROM EmployeeSRCTbl
SELECT * FROM EmployeeTRGTbl
    
/*
Tips 3
*/
    
CREATE TABLE ProductTree (ProductTreePK INT IDENTITY,

[Description] VARCHAR(1000), ProductLevelPK INT, HierID HIERARCHYID)

CREATE TABLE ProductLevels (ProductLevelPK INT IDENTITY,
[Description] VARCHAR(50))
    -- PK of 1 (All)
INSERT INTO ProductLevels VALUES ('All Products')
-- PK of 2 (Family)
INSERT INTO ProductLevels VALUES ('Family')
-- PK of 3 (Brand)
INSERT INTO ProductLevels VALUES ('Brand')
     -- PK of 4 (Category)
INSERT INTO ProductLevels VALUES ('Category')

-- PK of 5 (SubCategory)

INSERT INTO ProductLevels VALUES ('SubCategory')
     -- PK of 6 (SKU)
 INSERT INTO ProductLevels VALUES ('SKU')


hierProductParentID.GetDescendant(@LastChild,NULL)

hierarchyid::GetRoot()
GetAncestor(1)
exec InsertProductTree null,'All Products', 1
exec InsertProductTree 1 ,'Family A', 2
exec InsertProductTree 1 ,'Family B', 2
exec InsertProductTree 1 ,'Family C', 2

declare @TempParent int
=


(SELECT ProductTreePK FROM ProductTree WHERE

SELECt * FROM ProductTree
SELECT * FROM ProductLevels


DECLARE @tDates TABLE (WorkDate DATE)


INSERT INTO @tdates
     VALUES ('1-1-2008'),
     ('1-1-2009'),
     ('12-31-2007'),
     ('3-1-2008'),
     ('11-1-2009'),
     ('12-1-1620');


SELECT DATEPART(yy,workDate),WorkDate FROM @tDates ORDER BY WorkDate

 
DECLARE @tTimes TABLE (WorkDate TIME)    

INSERT INTO @tTimes
     VALUES ('10:20'),      
    ('20:10:10'),
     ('12:25:30')

SELECT WorkDate FROM @tTimes ORDER BY WorkDate