* to 1

Question

* to 1

sizal0234

SSCrazy

Points: 2272
More actions
October 1, 2019 at 7:57 pm

#3685110

Can I replace the * with 1 here in the below code. Will it impact the result.
IF EXISTS (SELECT * FROM dbo.tbTest where Test_ID = @Test_ID) -- Like change to SELECT 1

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

rishabhdg Grasshopper Points: 15 More actions · Answer 1

Hi,

both will work perfect. There is no difference wrt performance also. I have seen in some community posts that select 1 is faster than select * but if you see the execution plan in both the cases same. So it’s your choice if you use * or 1 with the EXISTS clause

heb1014 Hall of Fame Points: 3790 More actions · Answer 2

The engine is smart enough that either is fine. You could take a look at the execution plans and statistics on both versions of your query.

Thom A SSC Guru Points: 99130 More actions · Answer 3

You can replace the * with whatever you want (often people will use 1 or NULL yes). It shouldn't, however, have any effect on the performance however.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

rishabhdg wrote:

Hi,
both will work perfect. There is no difference wrt performance also. I have seen in some community posts that select 1 is faster than select * but if you see the execution plan in both the cases same. So it’s your choice if you use * or 1 with the EXISTS clause

Not referring to the current case here but (just to say it out loud) the comparison of execution plans (even actual plans) shouldn't actually be used to determine which code is actually better because even the actual execution plans are riddled with sometime seriously wild estimates.

Don't mistake that for me making a recommendation to not use execution plans to help write the best code possible. Just don't use execution plans as a measurement of which code will actually perform better.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.allen SSC Guru Points: 76985 More actions · Answer 5

The SELECT statement is ignored. This can be seen by running the following query, where the query runs without producing a "divide by zero" error.

IF EXISTS ( SELECT 1/0 FROM (VALUES(1)) v(n))
SELECT GETDATE()

I prefer using the *.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA