1

I am new in T-SQL. As far as I know, SELECT * is considered to be a bad practice so I always avoid using SELECT * in my code.

However, my colleague told me that use SELECT * is fine for doing an existence check. For example,

IF EXISTS (SELECT * FROM tb_test WHERE ResourceType = 2)
BEGIN
    --do something
END

"Because the MSSQL Server knows that the statement is doing an existence check, the optimizer will do the right thing." he said.

Is there no performance overhead when I use SELECT * for doing an existence check??

Thanks in advance.

Hemingway Lee
  • 759
  • 1
  • 9
  • 21
  • 1
    You can always check the query execution plan and compare. I dont think its going to make much differences between `Select *` and `Select 1` unless its in `If` condition – Nilesh Sep 16 '13 at 07:17
  • I always used to write these as `SELECT 1 FROM...` as well, but when in doubt, check the execution plan. – Tom Chantler Sep 16 '13 at 07:23
  • @Nilesh No it doesn't. See [Subquery using Exists 1 or Exists \*](http://stackoverflow.com/questions/1597442/subquery-using-exists-1-or-exists). What do you mean by "unless its in `If` condition"? – Martin Smith Sep 16 '13 at 08:42

1 Answers1

8

Your colleague is correct1. The optimizer knows that no column data actually needs to be retrieved.

But you're also correct that, in general, SELECT * should be avoided. EXISTS checks are the exception that proves the rule.

1A bit of a rarity on SO in my experience.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448