WHERE 1 = 1,who uses this besides me

Question

WHERE 1 = 1,who uses this besides me

Resender

SSCertifiable

Points: 7443
More actions
February 2, 2015 at 9:06 am

#99074

ok this is something of a best practice of mine in my queries
I always do
WHERE 1 = 1
I can't remember where I picked this habit up and I'm one of the few in my current workplace who use this,so I've been wondering anyone else besides me who does this.

Viewing 15 posts - 1 through 15 (of 52 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Resender (2/2/2015)
ok this is something of a best practice of mine in my queries
I always do
WHERE 1 = 1
I can't remember where I picked this habit up and I'm one of the few in my current workplace who use this,so I've been wondering anyone else besides me who does this.

All the time during development. It depends whether you put commas before or after your output columns.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Resender (2/2/2015)
ok this is something of a best practice of mine in my queries
I always do
WHERE 1 = 1
I can't remember where I picked this habit up and I'm one of the few in my current workplace who use this,so I've been wondering anyone else besides me who does this.

I have done this when programmatically building dynamic SQL to make adding ANDs or ORs easier, but that's about it.

Bhushan Kulkarni SSCrazy Points: 2829 More actions · Answer 3

I used this when creating dynamic sql query as default search condition

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

I hate seeing that in queries.

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

kellergary SSC Enthusiast Points: 119 More actions · Answer 5

sql = "Select * from table where 1 = 1";

if (XXX)

{

sql = sql + " and a > 10";

}

Programming[/url]

Neil Burton SSC-Insane Points: 22905 More actions · Answer 6

I do when I'm troubleshooting queries, it saves constantly having to delete and re-enter AND's if I'm trying to establish what's causing a report to return unexpected data.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 7

GilaMonster (2/16/2015)
I hate seeing that in queries.

Ditto that.

--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)

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 8

I use it regularly, but only when I'm developing queries or in ad-hoc queries.

Interesting that I saw this post just now. I just finished removing one of these from a production stored procedure.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Ed Wagner SSC Guru Points: 287024 More actions · Answer 9

The only purpose I know of for it is the one Lynn stated. In production code, concatenating strings into SQL statements tends to be bit on the dangerous side, so don't do it.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

TomThomson SSC Guru Points: 104773 More actions · Answer 10

Jeff Moden (2/16/2015)
GilaMonster (2/16/2015)
I hate seeing that in queries.
Ditto that.

Agree.

The ancient "WHILE 1=1" had an excuse because the procedural languages never had any decent multi-exit loop control flow concepts (even T-SQL with CONTINUE and BREAK still needs that), but surely we don't want anything like "WHERE 1=1" in any SQL dialect's DML.

Tom

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 11

CELKO (2/17/2015)
Ditto that.
Agree.
Department of Redundancy Department 🙂

🙂

--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)

PHYData DBA SSCertifiable Points: 7541 More actions · Answer 12

Jeff Moden (2/16/2015)
GilaMonster (2/16/2015)
I hate seeing that in queries.
Ditto that.

+1 to all of the above.

#1 Reason my production SQL does not work as expected.

It is a code structure older than SQL and is not a best practice in any language.

It is like saying While Constant = the same Constant do something.

EDIT: I see I am not the only one that remembers how horrible this is in any language.

PHYData DBA SSCertifiable Points: 7541 More actions · Answer 13

CELKO (2/18/2015)
I see I am not the only one that remembers how horrible this is in any language.
Remember it from Assembly languages? If you dropped it, the line count would change and would mess up hardwired jumps. :crazy:

Wasn't the syntax different for the 1=1 we are talking about? like C₁ = 1

In assembly every check is for bit value at some register or Port, so there are to many things you could be remembering.

I do remember in Assembly for 80386 and later FPUs that you checked C₁ (bit 9 or condition one) if bit 6 (stack fault) equaled one to see if it was stack underflow or overflow.

If these Condition checks got confused with constant checks in the executing code then it would miss the jump to peek at the correct port and would just keep trying cause constant 1=1 but the next register was not there because condition 1 = 1 fault had occurred so constant 1 still equal 1.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 14

CELKO (2/18/2015)
Oh Ghod! We are old! :crying:
And we had some weird undocumented instructions. The BLISS language out of Carnegie-Mellon had a final pass in their compiler to look for these things. I favorite was a single VAX instruction that compared x++ to x++ and if it was TRUE (always) then increment the results. How to add 3 in one command! :sick:

I vaguely recall some runaway process on ENIAC (the VAX that was in play when I went to college, not the original "vaccuum tube" iteration) that sounded a lot like that type of incrementer. It skipped right past the stop criterion (x==5) and kept on looping happily thereafter.

God those were the good days :w00t:

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?