Why use Select 1

Question

Why use Select 1

Mike Aguilar

SSC Enthusiast

Points: 157
More actions
July 14, 2015 at 2:04 pm

#151426

IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'Index name here' AND object_id = OBJECT_ID('Table Name Here')
DROP INDEX ....
I was just wondering why some people use a 1 there instead of name or *?
Personal preference, best practices, ?
Thanks

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Mike Aguilar (7/14/2015)
IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'Index name here' AND object_id = OBJECT_ID('Table Name Here')
DROP INDEX ....
I was just wondering why some people use a 1 there instead of name or *?
Personal preference, best practices, ?
Thanks

I'll say that it's personal preference as it won't matter what is in the column list, as EXISTS only checks for rows. You could even use SELECT 1/0 and you won't get an error.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Mike Aguilar SSC Enthusiast Points: 157 More actions · Answer 2

Thanks Luis.

You know I had to try that. You were right: no error.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Personally, to make it clear(er) that EXISTS doesn't care what the columns are.

And 1/0 works, but something like LOG(-1) doesn't. I suspect the latter is a parse-time error.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 4

GilaMonster (7/14/2015)
Personally, to make it clear(er) that EXISTS doesn't care what the columns are.
And 1/0 works, but something like LOG(-1) doesn't. I suspect the latter is a parse-time error.

Ah, now that's a very interesting new fact!

Something like this:

IF EXISTS (

select LOG(-1) from sys.objects)

SELECT 1

Interestingly, that seems version specific. It fails on my 2008 and 2008 R2 instances, but succeeds on all my 2012 and 2014 instances. I'll have to do some digging to see if I can find any sources for what may have changed.

Cheers!