Optimization is not easy and really depends on your application an needs. You can change the isolation level (see books online and refer to the SET TRANSACTION ISOLATION LEVEL command...

You did replace ##temptablename with the name of the table that you see in your database, right? For Example ##MyTable?

Sridhar,

The steps should NOT HAVE deleted anything. They were supposed to return results. Please post these results so I can better see what's going on. That was the reason for...

Can you paste you SQL stamtment here?

I have read the SQL architecture books several times and cannot recall any lock coverage in granular details, which is what you are looking for. Very interesting question. I would...

I don't believe that the above is correct. The shared locks will not be released in the example below (which is not issued from a query but from an INSERT...

You got the parameters in the CHARINDEX backwards. THe space should be the first param.

If you'd like to dig into the speed problems with this particular column- can you please post the DDL (schema) of the table?

What is the data type for the ProdDate column?

How about using the following statement instead? There is no need to convert datetime back to itself and the 102 doesn't help...

Do you understand how b-trees work?

Look at the b-trees and SQL architecture in the following best-practices presentation:

http://education.sqlfarms.com/ShowPost.aspx?PostID=797

You can get it in other places on the web. This presentation...

How are you filtering the data? Can you please post the T-SQL statement(s)?

"It DID sound like a shameless plug... "

All is fair in love and war... (Francais Edward Smedley)

The problem posted above says that there are 5 cols that uniquely identify each row EXCEPT for one row that has dups (i.e., values...

Yes to all, but there may be simpler solutions.

Can you please run the following SQL statements and post the results?

1.

USE MASTER

SELECT OBJECT_ID('tempdb..##TempTableName')

2. Run the below and see if anything...

Hmm... What about the insertion of 1 row among all duplicate rows? That's where the challenge is