Archive

Posts Tagged ‘DELETE’

DML | Is SELECT a DML?

July 26, 2011 1 comment

DML or Data Manipulation Langauge as the term suggest represents those SQL statements that manipulates the data in a database. Thus these langauges allows users to INSERT, UPDATE & DELETE the data in a particular database. Other than this the much debatable SELECT statement may or may not be considered as DML upon its usage.

A simple SELECT statement which fetches data from a table is a read-only language and cannot be called as DML.

But a modified version of SELECT i.e. ‘SELECT INTO’ can fall into the DML segment. The ‘SELECT INTO’ can be used to create a Table and insert records fetched from the SELECT statement.

Also, we can manipulate the data for reporting purpose while retriving by using the SELECT statement. The data in underlying tables is unchanged but on the frontend you get a view of modified data, like:

USE [AdventureWorks]
GO

SELECT ContactID, Title,
	FirstName, MiddleName, LastName,
	FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName as [FullName] -- Name manipulated by joining 3 part names.
FROM Person.Contact

SELECT SalesOrderID, SalesOrderDetailID,
	OrderQty, UnitPrice,
	OrderQty * UnitPrice as [TotalPrice] -- Manipulated cost by calculating it for total items purchased.
FROM Sales.SalesOrderDetail

Thus SELECT also comes under DML and following is the list of all DMLs:
1. SELECT {COLUMN LIST} [INTO {TABLE_NAME}] [WHERE {WHERE condition}]
2. INSERT INTO {TABLE_NAME} VALUES (SET of Values)
3. UPDATE {TABLE_NAME} SET [WHERE {WHERE condition}]
4. DELETE FROM {TABLE_NAME} [WHERE {WHERE condition}]

OUTPUT clause and MERGE statement

November 25, 2010 1 comment

Just responded to a post in MSDN forum, link: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c06e1db4-7fd6-43c4-8569-5335d555dac8

Accroding to MS-BOL, OUTPUT clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

Lets go with a self descriptive example:

–> OUTPUT with INSERT

create table manoj (sn int, ename varchar(50))
insert into manoj
OUTPUT INSERTED.*
values (1,'manoj'), (2,'hema'), (3,'kanchan'), (4,'pankaj')
This gives me following output instead of normal message (N row(s) affected):
sn      ename
1	manoj
2	hema
3	kanchan
4	pankaj
select * from manoj
This gives me the same output as above:
sn      ename
1	manoj
2	hema
3	kanchan
4	pankaj

–> OUTPUT with DELETE

delete from manoj
OUTPUT DELETED.*
where sn = 4
This gives me following output:
sn      ename
4	pankaj
select * from manoj
Now the result set is changed to:
sn      ename
1	manoj
2	hema
3	kanchan

–> OUTPUT with UPDATE

update manoj
set ename = 'pankaj'
OUTPUT DELETED.*, INSERTED.*
from manoj
where sn = 2
This gives me following output:
sn      ename   sn     ename
2	hema	2	pankaj
select * from manoj
Now the result set is changed to:
sn      ename
1	manoj
2	pankaj
3	kanchan

–> OUTPUT with MERGE

According to MS-BOL, MERGE performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

create table manoj2 (sn int, ename varchar(50))

insert into manoj2
values (1,'manoj'), (2,'hema'), (3,'kanchan'), (4,'pankaj'), (5,'saurabh')

select * from manoj2
This gives me following output instead of normal message (N row(s) affected):
sn      ename
1	manoj
2	hema
3	kanchan
4	pankaj
MERGE manoj AS TARGET
USING (SELECT sn, ename FROM manoj2) AS SOURCE
ON (TARGET.sn = SOURCE.sn)
WHEN MATCHED THEN
	UPDATE SET TARGET.ename = SOURCE.ename
WHEN NOT MATCHED BY TARGET THEN
	INSERT (sn, ename) VALUES (sn, ename)
OUTPUT $action, DELETED.*, INSERTED.*;
This gives me following output:
$action sn      ename   sn      ename
INSERT	NULL	NULL	4	pankaj
INSERT	NULL	NULL	5	saurabh
UPDATE	1	manoj	1	manoj
UPDATE	2	pankaj	2	hema
UPDATE	3	kanchan	3	kanchan
select * from manoj
Now the result set is changed to:
sn      ename
1	manoj
2	hema
3	kanchan
4	pankaj
5	saurabh

–> Final cleanup

drop table manoj
drop table manoj2

Plz note: An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement.

MS BOL:-

– On MERGE: http://technet.microsoft.com/en-us/library/bb510625.aspx

– On OUTPUT: http://technet.microsoft.com/en-us/library/ms177564.aspx

Difference between TRUNCATE, DELETE and DROP?

February 22, 2009 13 comments

DELETE and TRUNCATE are two SQL commands used to remove records from a particular table. But they differ in how they execute and operate.

–> DELETE: (http://msdn.microsoft.com/en-us/library/ms189835.aspx)
– Removes Some or All rows from a table.
– A WHERE clause can be used to remove some rows. If no WHERE condition is specified, all rows will be removed.
– Causes all DELETE triggers on the table to fire.
– It de-allocates records row-by-row in transaction logs and thus is slower than TRUNCATE.
– According to MS BOL, if a table is a Heap or no Clustered index is defined than the row-pages emptied are not de-allocated instantly and remain allocated in the heap. Thus, no other object can reuse this associated space. Thus to de-allocate the space a Clustered index is required or TABLOCK hint should be applied in the DELETE statement.
– Thus it requires more locks and database resources.
– This is a DML command as it is just used to manipulate/modify the table data. It does not change any property of a table.

–> TRUNCATE: (http://msdn.microsoft.com/en-us/library/ms177570.aspx)
– Removes All rows from a table.
– Does not require a WHERE clause, not allowed here.
IDENTITY columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.
No Triggers are fired on this operation because it does not log individual rows.
– It de-allocates Data Pages instead of Rows in Transaction logs, thus is faster than DELETE.
– Thus it also requires less number of locks.
– TRUNCATE is not possible when a table is reference by a Foreign Key or tables used in replication or with Indexed views.
– This is a DDL command as it resets IDENTITY columns, de-allocates Data Pages and empty them for use of other objects in the database.

Note: It is a misconception among some people that TRUNCATE cannot be roll-backed. But in reality both DELETE and TRUNCATE operations can be COMMITTED AND ROLL-BACKED if provided inside a Transaction. The only method to Rollback a committed transaction after DELETE/TRUNCATE is to restore the last backup and run transactions logs till the time when DELETE/TRUNCATE is about to happen.

–> DROP: (http://msdn.microsoft.com/en-us/library/ms173790.aspx)
The DROP TABLE command removes one or more table(s) from the database.
– All related Data, Indexes, Triggers, Constraints, and Permission specifications for the Table are dropped by this operation.
– Some objects like Views, Stored Procedures that references the dropped table are not dropped and must be explicitly dropped.
– Cannot drop a table that is referenced by any Foreign Key constraint.
– According to MS BOL, Large tables and indexes that use more than 128 extents are dropped in two separate phases: Logical and Physical. In the Logical phase, the existing allocation units used by the table are marked for de-allocation and locked until the transaction commits. In the physical phase, the IAM pages marked for de-allocation are physically dropped in batches.

Follow

Get every new post delivered to your Inbox.

Join 438 other followers