4

I have to insert data into table but only if it does not already exist.

I check for existing rows using:

IF EXISTS (SELECT 'X' FROM Table1 where id = @id)

Will the use of 'X' improve performance instead of using a column name?

Tony
  • 9,280
  • 3
  • 46
  • 72
karthik
  • 291
  • 2
  • 5
  • 16

3 Answers3

8

No. You can use *,column name, NULL or even 1/0.

As per the ANSI standard, it should not be evaluated. Page 191 ANSI SQL 1992 Standard.

* is mentioned in MSDN

However, a better way is to use MERGE (SQL Server 2008) or simply catch the error. Previous SO answers from me: One, Two

Community
  • 1
  • 1
gbn
  • 408,740
  • 77
  • 567
  • 659
  • @anishmarokey: see my links to my other answers. A unique key in itself does not help, but it does allow try/catch. And it implies an index too which will help performance – gbn Oct 22 '10 at 06:59
0

It's probably negligible difference. I think the most common "pattern" I've seen is to just select 1 when you're just checking for existence, but I wouldn't worry about it too much.

select 1 from Table1...
Andy White
  • 84,168
  • 47
  • 173
  • 207
0

One technique would be to add a unique constraint on the column. Always insert a record and handle the failure case where that id already existed in the table.

Ozten
  • 178
  • 9