Help on SQL query

Hi,
Can you let me know, which of the following query execution gives better performance.

Also, the table T1 is not indexed and the table can have 100,000 records.

Case 1:
DELETE FROM T1 where NAME = 'Ayyappan'
DELETE FROM T1 where NAME = 'Manivel'

Case2:
DELETE FROM T1 where NAME in ('Ayyappan', 'Manivel')

In my opinion both are same. You can either apply a join or use IN statement.

Refer this thread to clear your doubts

http://stackoverflow.com/questions/7486856/disadvantages-of-in-operator-in-sql-server-and-alternate-way-not-to-use-in-opera

Just refer this too

http://www.bennadel.com/blog/940-SQL-Optimization-Case-Study-JOIN-Clause-vs-IN-Clause.htm

Hope it makes clear to you :)

The best way to answer questions like this is to try it on a test database and see :)

On the face of it, you might expect that 'case 2' would be faster as it should only be necessary to traverse the records in the table once, looking for those to delete.

However, I suspect that there will actually be little or no difference because of the way the query optimizer treats the 'IN' clause.

In addition to the links which Anuja posted, I'd check out this one:

http://stackoverflow.com/questions/714590/most-efficient-fast-t-sql-delete-for-many-rows

 

You could use a higher-level interface and let it take care of the details. Such as TableAdapters and the Entity Framework.