>>>>>>Sergey,
>>>>>>That conflicts with the rule-of-thumb: "Never use * when selecting, always specify the fields".
>>>>>>I know you are a very smart man, so I am wondering why you would do this.
>>>>>>Does it change performance or just reduce typing?
>>>>>>
>>>>>>Einar
>>>>>>
>>>>>>>I use '*' in subquery because the field list is irrelevant in this case.
>>>>>>>

IF NOT EXISTS(SELECT * FROM [MyTable] WHERE [PK] = @PK) ...

>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>>
>>>>>>>>>

IF NOT EXISTS(SELECT [PK] FROM [MyTable] WHERE [PK] = @PK) ...

>>>>>>>>>
>>>>>>>>>If this query is used a lot, there should be a nonclustered index on PK.
>>>>>>>>
>>>>>>>>Thanks. Your way looks cleaner than my code.
>>>>>>>>
>>>>>>>>
>>>>>
>>>>>because:
>>>>>

>>>>>IF EXISTS(SELECT *)
>>>>>   SELECT 1
>>>>>

>>>>>not fail,
>>>>>then
>>>>>

>>>>>if exists(select * from mytable where pk=@pk)
>>>>>

>>>>>it is faster respect to
>>>>>

>>>>>if exists(select pk from mytable where pk=@pk)
>>>>>

>>>>>bacause the analysis is simpler.
>>>>
>>>>Fabio and Sergey,
>>>>
>>>>I must disagree here.
>>>>
>>>>Using the same column in the select list and the where clause is a lot better than *.
>>>>
>>>>First, the optimizer does not have to evaulate whether something other than an single index seek is required to satisfy the query.
>>>>
>>>>Second, if the column list is from a nonclustered index, only the index leaf is locked during the query. When you use *, a shared lock is placed on the table as well as the index.
>>>
>>>Keith,
>>>Without knowing the application I prefer the code that mostly guarantees
>>>the data integrity,
>>>and therefore I prefer * that it lock the table row.
>>
>>Fabio,
>>
>>Index locks and row locks are interchangeable when the data being operated on is part of the index. Using * gains nothing in data integrity.
>
>Then you say that [*] is slower respect to [pk]. Can you measure this ?

Sure. Set up 100 clients and have them ins/upd/del from the table while another process is looping the IF EXISTS.. statement. Measure that performance against the 100 clients without the IF EXISTS.. looping.

Or, we can agree that more locking than neccessary is a waste of server resources and in general is a bad thing.