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,672
  • 3
  • 47
  • 75
karthik
  • 311
  • 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
  • 422,506
  • 82
  • 585
  • 676
  • @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
  • 86,444
  • 48
  • 176
  • 211
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
  • 188
  • 9