>>If the potential for dirty reads is not an issue, you could add 'WITH (NOLOCK)' table hint to eliminate locks during the select
>>
>>

>>SELECT Client.Numero FROM Client WITH (NOLOCK)
>>  WHERE Client.NoEntity=@NoEntity AND Client.Fic_Date<@Fic_Date 
>> 	AND Client.Loss_Code=@Loss_Code AND Client.CType=@CType
>> 	AND EXISTS ( SELECT 1 FROM Invoice WITH (NOLOCK) WHERE Client.Numero=Invoice.NoClient 
>>                AND Invoice.Amount=@Amount)
>>

>
>Could you define "dirty reads"? Also, is the NOLOCK ANSI SQL compliant?

NOLOCK and dirty reads

There is a discussion here

http://www.sql-server-performance.com/articles/per/lock_contention_nolock_rowlock_p2.aspx

Basically, using the NOLOCK hint, you might read data in an uncomitted transaction that could be rolled back after you read it.

I don't know if it is ANSI SQL compliant. I suspect not.