SQL Server 2008, WAITFOR DELAY/TIME

The WAITFOR Statement

There are often things that you either don’t want to or simply can’t have happen right this moment, but

you also don’t want to have to hang around waiting for the right time to execute something.

No problem — use the WAITFOR statement and have SQL Server wait for you. The syntax is incredibly

simple:

WAITFOR

DELAY <’time’> | TIME <’time’>

The WAITFOR statement does exactly what it says it does. It waits for whatever you specify as the argument to occur. You can specify either an explicit time of day for something to happen, or you can specify an amount of time to wait before doing something.

The DELAY Parameter

The DELAY parameter choice specifies an amount of time to wait. You cannot specify a number of

days — just time in hours, minutes, and seconds. The maximum allowed delay is 24 hours. So, for

example:

WAITFOR DELAY ‘01:00’

would run any code prior to the WAITFOR, then reach the WAITFOR statement, and stop for one hour, after which execution of the code would continue with whatever the next statement was.

The TIME Parameter

The TIME parameter choice specifies to wait until a specific time of day. Again, we cannot specify any

kind of date — just the time of day using a 24-hour clock. Once more, this gives us a one-day time limit

for the maximum amount of delay. For example:

WAITFOR TIME ‘01:00’

would run any code prior to the WAITFOR, then reach the WAITFOR statement, and stop until 1 AM, after which execution of the code would continue with whatever the next statement was after the WAITFOR.

SQL Server 2008 – Introduction to Merge Statement, One Statement for INSERT, UPDATE, DELETE.

In previous versions of SQL Server, when you heard the word ‘‘merge’’ you generally thought of merge replication. With SQL Server 2008, however, we have a whole new way of thinking about the word merge and, more importantly, of thinking about DML statements.

One of the most important advantages of MERGE statement is all the data is read and processed only once. This is quite an improvement in performance of database query.

With MERGE, we have the prospect of combining multiple DML action statements (INSERT, UPDATE, and DELETE) into one overall action, improving performance (they can share many of the same physical operations) and simplifying transactions. MERGE makes use of a special USING clause that winds up working somewhat like a CTE. The result set in the USING clause can then be used to conditionally apply your INSERT, UPDATE, and DELETE statements. The basic syntax looks something like this:

Syntax of MERGE statement is as following:
MERGE <target table> [AS <alias>]

USING

(

<source query>

)

[ON <relation-condition-mergANDqueryTable>]

WHEN {[NOT] MATCHED | <expression> THEN

<action statement>

[<additional WHEN clauses>, [...n]]

N.B.: no Need to give table name for insert, update and delete, because its take automatically MERGE table Name.

Example:

Create Marge Table……………………….

CREATE TABLE Sales.MonthlyRollup(

Year smallint NOT NULL,

Month tinyint NOT NULL,

ProductID int NOT NULL

FOREIGN KEY REFERENCES Production.Product(ProductID),

QtySold int NOT NULL,

CONSTRAINT PKYearMonthProductID

PRIMARY KEY (Year, Month, ProductID)

);

Let’s you keep sales data into MERGE table, every day after sales complete, you need to update sales of every product, if your product is first time sales today then you need to insert this product sales, if sales quantity 0 then need delete, then the quey is following:

MERGE Sales.MonthlyRollup AS smr

USING

(

SELECT soh.OrderDate, sod.ProductID, SUM(sod.OrderQty) AS QtySold

FROM Sales.SalesOrderHeader soh JOIN Sales.SalesOrderDetail sod

ON soh.SalesOrderID = sod.SalesOrderID

WHERE soh.OrderDate =’23 May 2012’

GROUP BY soh.OrderDate, sod.ProductID

)AS s

ON (s.ProductID = smr.ProductID)

WHEN MATCHED AND smr.QtySold =0 AND  s.QtySold =0 THEN DELETE

WHEN MATCHED THEN UPDATE SET smr.QtySold = smr.QtySold + s.QtySold

WHEN NOT MATCHED THEN INSERT (Year, Month, ProductID, QtySold)

VALUES (DATEPART(yy, s.OrderDate),DATEPART(m, s.OrderDate),s.ProductID,s.QtySold);

Common Table Expressions (CTE)

Common Table Expressions (CTE)

The basic syntax for a CTE utilizes the WITH keyword followed by a name and definition:

WITH <CTE name> [( <column name> [,...n])]

AS

( <query returning tabular data> )

<statement that will make use of the CTE>

After the CTE is defined, you can refer to it by name just as if it were a table.

WITH Report (ManagerID,EmployeeID,EmployeeName,Label)

AS

(SELECT e.ManagerID,e.EmployeeID,e.EmployeeName,1 as Label

from Employee e

WHERE e. ManagerID is null

UNION ALL

SELECT e.ManagerID,e.EmployeeID,e.EmployeeName,r.Label+1 as  Label

from Employee e JOIN Report r ON e.ManagerID=r.EmployeeID)

SELECT ManagerID,EmployeeID,EmployeeName,Label FROM Report

Output Come like………

ManagerID	EmployeeID	EmployeeName	Label
NULL	1	MD Sir	1
1	2	Head Finance	2
1	3	Head IT	2
3	4	Programmer	3