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}]

TSQL Interview Questions – Part 1

December 9, 2010 15 comments

These are the following common question that I faced in various TSQL interviews.

… I’ll be updating this post with more questions.

1. Difference between SQL Server 2000 & SQL Server 2005 features, or new features of 2005 vs 2000.
- Ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE)
- Exception handling (TRY-CATCH block)
- CTE (Common Table Expressions)
- PIVOT, UNPOVIT
- CUBE, ROLUP & GROUPING SET
- SYNOMYMS

2. What tools do you use for performance tuning?
Query Analyzer, Profiler, Index Wizard, Performance Monitor
Link: http://www.sqlteam.com/article/sql-server-2000-performance-tuning-tools

3. What is SQL Profiler? What it does? What template do you use?
More Info: http://www.extremeexperts.com/SQL/Articles/TraceTemplate.aspx
http://msdn.microsoft.com/en-us/library/ms190176.aspx

4. How can you execute an SQL query from command prompt?
OSQL & SQLCMD
More Info: http://msdn.microsoft.com/en-us/library/ms162773.aspx
http://blog.sqlauthority.com/2009/01/05/sql-server-sqlcmd-vs-osql-basic-comparison/
http://www.databasejournal.com/features/mssql/article.php/3654176/SQL-Server-2005-Command-Line-Tool-SQLCMD–Part-I.htm

5. Difference between DELETE & TRUNCATE statement? Which statement can be Rollbacked?
– With DELETE we can provide conditional WHERE clause to remove/delete specific rows, which is not possible with TRUNCATE.
– TRUNCATE is faster than DELETE as Delete keeps log of each row it deletes in transaction logs, but truncate keeps log of only de-allocated pages in transaction logs.
– Both statements can be rolled backed if provided in a transaction (BEGIN TRANS). If not then none of them can be rollbacked.
– DELETE is DML just like INSERT, UPDATE, but TRANCATE is DDL, just like CREATE, ALTER, DROP
Link: Complete differences on in Delete & Truncate.

6. What are extended stored procedures? Can you create your own extended stored-proc?
More Info: http://msdn.microsoft.com/en-us/library/ms175200.aspx
http://msdn.microsoft.com/en-us/library/ms164627.aspx

7. How can you execute a DOS command from SQL or through SQL query by using xp_cmdshell?

exec xp_cmdshell 'dir c:\*.exe'

More Info: http://msdn.microsoft.com/en-us/library/aa260689%28SQL.80%29.aspx
http://msdn.microsoft.com/en-us/library/ms175046.aspx

8. How will you insert result set of the above proc in a table?

insert into
exec xp_cmdshell 'dir c:\*.exe'

9. What are Cursors and their types? What type do you use most and which one is fast?
FORWARD-ONLY, FAST-FORWARD or READ-ONLY cursors.
Fastest to slowest: Dynamic, Static, and Keyset.
More Info: http://msdn.microsoft.com/en-us/library/ms180169.aspx
http://www.sql-server-performance.com/tips/cursors_p1.aspx

10. Why you should not use a cursor? What are its alternatives?
Alternatives: while loops with temp tables, derived tables, correlated sub-queries, CASE stmt
More Info: http://www.sql-server-performance.com/tips/cursors_p1.aspx
http://sqlserverpedia.com/wiki/Cursor_Performance_Issues
http://searchsqlserver.techtarget.com/feature/Part-3-Cursor-disadvantages

11. Difference between LEFT JOIN with WHERE clause & LEFT JOIN with no WHERE clause.
OUTER LEFT/RIGHT JOIN with WHERE clause can act like an INNER JOIN if not used wisely or logically.

12. How will you migrate an SSIS package from Development to Production environment?
Do not include db connections and file paths in your workflow, instead create configuration files. This will help in deploying the pkg created in DEV server to Testing and finally to the PROD environment.
More Info: http://msdn.microsoft.com/en-us/library/cc966389.aspx
http://www.wpconfig.com/2010/03/26/ssis-package-configurations/

13. Multiple ways to execute a dynamic query.
EXEC sp_executesql, EXECUTE()
More Info: http://sqlwithmanoj.wordpress.com/2011/03/22/execute-or-exec-vs-sp_executesql/

14. Difference between COALESCE() & ISNULL()
More Info: http://sqlwithmanoj.wordpress.com/2010/12/23/isnull-vs-coalesce/

15. Which of the following has higher performance:
a. OR, AND
b. =, <>, >
c. IN, NOT IN, EXISTS
d. UNION, UNION ALL

16. What should be the ideal combination with IN & UNION (ALL) in terms of performance?
a. SELECT *FROM
WHERE

IN (SELECT… UNION SELECT…)
OR
b. SELECT * FROM

WHERE

IN (SELECT… UNION ALL SELECT…)

17. What is an IDENTITY column and its usage in INSERT statements?
IDENTITY column can be used with a tables column to make it auto incremental, or a surrogate key.
MS BOL link: http://msdn.microsoft.com/en-us/library/ms174639(v=SQL.90).aspx

18. Can you create a Primary key without clustered index?
Creation of PK automatically creates a clustered index upon the column(s).
More Info: http://vadivel.blogspot.com/2006/03/primary-keys-without-clustered-index.html

19. There are two tables one Master and another Feed table, both with 2 columns: ID & Price. Feed table gets truncated and re-populated every day.

Master Table		Feed Table
ID, Price		ID, Price
1    100		1    200
3    200		2    250
5    300		4    500
6    400		6    750
7    500 		7    800

Create a job with an optimal script that will update the Master table by the Feed table.

20. What are CUBE & ROLLUP sets?
CUBE & ROLLUP are the grouping sets used with GROUP BY clause and are very helpful in creating reports.
More Info: http://sqlwithmanoj.wordpress.com/2010/11/12/cube-rollup-compute-compute-by-grouping-sets/
http://msdn.microsoft.com/en-us/library/ms177673.aspx

21. What new indexes are introduced in SQL Server 2005 in comparison to 2000?
– Spatial
– XML
More Info: http://msdn.microsoft.com/en-us/library/ms175049.aspx

22. What are XML indexes, what is their use?
More Info: http://msdn.microsoft.com/en-us/library/ms345121%28SQL.90%29.aspx

23. How many types of functions (UDF) are there in SQL Server? What are inline functions?
– Scalar functions
– Inline Table-valued functions
– Multi-statement Table-valued functions
More Info: http://sqlwithmanoj.wordpress.com/2010/12/11/udf-user-defined-functions/
http://msdn.microsoft.com/en-us/library/ms189593.aspx

24. How will you handle exceptions in SQL Server programming?
By using TRY-CATCH constructs, putting our SQL statements/scripts inside the TRY block and error handling in the CATCH block, link.
More Info: http://msdn.microsoft.com/en-us/library/ms179296%28v=SQL.90%29.aspx

25. How would you send an e-mail form SQL Server?
Configure Database mail here.
More Info: http://msdn.microsoft.com/en-us/library/ms175887%28v=SQL.90%29.aspx

 

 

… more questions on next post [Part-2].

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 14 comments

DELETE & TRUNCATE are two TSQL 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 only remove some rows. If no WHERE condition is specified, all rows will be removed.
– Causes all DELETE triggers on the table to fire.
– It deallocated 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 deallocated and remain allocated in the heap. Thus no other object can reuse this associated space. Thus to deallocate 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 its is just used to manipulate/modify the table data. It does not change the 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 deallocates 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 its resets identity columns, deallocates 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 rolled back. But in reality TRUNCATE operation can be ROLLED BACKED. Thus DELETE & TRUNCATE both can be rollbacked 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 those tables are dropped by them 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 deallocation and locked until the transaction commits. In the physical phase, the IAM pages marked for deallocation are physically dropped in batches.

Follow

Get every new post delivered to your Inbox.

Join 396 other followers