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