Implicit Transactions In SQL Seerver 2008

Primarily for compatibility with other major RDBMS systems, such as Oracle or DB2, SQL Server supports (it is off by default but can be turned on if you choose) the notion of what is called an implicit transaction. Implicit transactions do not require a BEGIN TRAN statement — instead, they are automatically started with your first statement. They then continue until you issue a COMMIT TRAN or ROLLBACK TRAN statement. The next transaction then begins with your next statement.

Theoretically, the purpose behind this is to make sure that every statement is part of a transaction.

SQL Server also wants every statement to be part of a transaction, but, by default, takes a different

Approach if there is no BEGIN TRAN, then SQL Server assumes you have a transaction of just one statement, and automatically begins and ends that transaction for you. With some other systems though, you’ll find the implied transaction approach. Those systems will assume that any one statement is only the beginning of the transaction and therefore require that you explicitly end the every transaction with a COMMIT or ROLLBACK.

By default, the IMPLICIT_TRANSACTIONS option is turned off (and the connection is in auto commit transaction mode). You can turn it on by issuing the command:

SET IMPLICIT_TRANSACTIONS ON;

After that, any of the following statements will initiate a transaction:

CREATE

ALTER TABLE

GRANT

REVOKE

SELECT

UPDATE

DELETE

INSERT

TRUNCATE TABLE

DROP

OPEN

FETCH

The transaction will continue until you COMMIT or ROLLBACK. Note that the implicit transactions option

will affect only the current connection — any other users will still have the option turned off unless they

have also executed the SET statement.

The implicit transactions option is dangerous territory, and I highly recommend that you leave this option off unless you have a very specific reason to turn it on(such as compatibility with code written in another system).

Here’s a common scenario: A user calls up and says, ‘‘I’ve been inserting data for the last half hour, and none of my changes are showing.’’ So, you go run a DBCC OPENTRAN, and discover that there’s a transaction that’s been there for a while — you can take a guess at what’s happened. The user has a transaction open, and his or her changes won’t appear until that transaction is committed. The user may have done it using an explicit BEGIN TRANS statement, but he or she may also have executed some

code that turned implicit transactions on and then didn’t turn it off. A mess follows.

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