>>>>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.