WHERE 1 = 1,who uses this besides me

Question

WHERE 1 = 1,who uses this besides me

Viewing 8 posts - 46 through 52 (of 52 total)

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

bantrim SSCommitted Points: 1544 More actions · Answer 1

PHYData DBA (2/23/2015)
bantrim (2/23/2015)
PHYData DBA (2/23/2015)
bantrim (2/23/2015)
Eric M Russell (2/23/2015)
PHYData DBA (2/23/2015)
bantrim (2/22/2015)
Eric M Russell (2/22/2015)
On occasion I've used: SELECT.. WHERE 1 = 0. For example, I may want to select into a temp table that is derived from another table but initially empty. Also, just for the sake of the application, there may be a need to have a stored procedure return an empty resultset with the usual column layout, even when invalid input parameters have been specified or an error has occured.
Same here. It's especially useful in using BULK INSERT, where you need the target table created beforehand. For certain ETLs, I bulk insert into a temp table, then merge the deltas into the target table.
There are programing structures that handle all of this.
None of them add a useless check to a where statement that at best is not even executed.
I'm not aware of any BCP option that will create the target table; is there?
I was assuming he was referring to creating a target table "the right way". I'll let him answer though.
How the sentence "There are programing structures that handle all of this." was confused with the idea "This is in BCP" makes my point well.
There is plenty of confusion without adding code that does nothing as a Best Practice.
It adds confusion and reduces readability.
These are two things that should never be done in any Best Practice in any language.
I think we're beating a dead horse here. Based on your user name, I assume you're a DBA, and for this reason, I'm going to assume you spend much of your time chasing down developers running amok with inefficient code. You probably know, down to the CPU cycle, the most efficient way to do almost anything, and don't see the sense in doing something in any other way, and I totally respect that.
In my environment, I've spent the past 6 months working 70-80 hours per week, and though I appreciate the spirit of writing things to be airtight and optimized down to the millisecond, my CIO's deadlines don't allow for that. I have to take shortcuts to get the job done, and believe me, I have to chase down inefficient code as well. The consultants we have here have a seemingly obsessive desire to use subqueries in the select statements for their lookups in lieu of joins, and I took an 18 hour query down to 1-2 minutes just before the weekend. Because of this kind of workload, I don't get to worry about things like WHERE 1 = 0 not being the most efficient way of creating a target table, as long as it gets the job done in decent time.
Being a DBA is what I am now.
What I have always been is an IT professional.
You're comment full of assumptions and unfounded conclusions about my posts only add to the insurmountable mountain of angst that is the pile of excuses for writing bad code.
There are no limit to reasons why we write bad code or deploy bad code.
There are no good reasons for a best practice to add bad code to all SQL.
That is our topic. Please stick to it.
PS: we are discussing using WHERE 1 = 1 not WHERE 1 = 0. I understand it is easy to get confused about these things.

You know, I tried to be diplomatic.

You are acting like there is never an excuse for anyone to ever write code that doesn't fit your narrowly defined version of "good". You're also assuming you somehow know how each of our environments is designed, how our positions are defined, and the limits they entail.

I hate to say it, but you are sounding like the equivalent of the "old man shouting at clouds" in the coding world. I'm expecting your next post to tell me all my music is "just noise anyway", and to possibly pull my pants up.

This whole conversation could easily be settled with a simple "it depends". But, that wouldn't let you feel all self righteous, now would it?

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

PHYData DBA (2/23/2015)

ChrisM@Work (2/23/2015)
PHYData DBA (2/23/2015)
Care to divulge?
Since you did not specify I am going to just restate one that this discussion is about -
Never add a where statement to your SQL if that where statement is not filtering data.
example: SELECT Column1Name From dbo.TableName WHERE (6 + 3) = 9
The RDBMS Query Analyzer will remove it prior to executing your SQL.
Not adding code that does not or can not be executed (other than comments) is considered a programing structure.

WHERE 1 = 1 is a commonly used development trick which saves a few moments of time when you’re experimenting with search predicates in ad-hoc queries. Leaving it in production code is failing to complete the job – except as Jeff pointed out, to facilitate building multi-predicate strings in dynamic SQL. However, when left in production code it only costs during recompile because it’s optimised out. That means it’s likely to cost when used in dynamic SQL, but the alternatives – calculating whether or not an AND is required or removing a redundant AND from the beginning or end of the string – are likely to cost at least as much.

WHERE 0 = 1 is commonly used to build a table on the fly by exactly copying the structure of an existing table or output. Remember - tightly defined data can be the exception in some business models. Think marketing, for instance. The alternatives to this construction could be very expensive indeed.

We don’t have the luxury of a dba here but if we did and he proselytised in this manner with a) no evidence to back up the folly of our evil ways and b) no superior alternative to them case by case with evidence to prove it, we’d cast him out – airborne for most of the way and accelerated by gales of laughter.

Dogma spawns bad code as efficiently as laziness and ignorance.

^{“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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

ChrisM@Work (2/24/2015)
Dogma spawns bad code as efficiently as laziness and ignorance.

Nice. Great quote. I'm gonna use it now.:-D

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

SQLRNNR (2/24/2015)
ChrisM@Work (2/24/2015)
Dogma spawns bad code as efficiently as laziness and ignorance.
Nice. Great quote. I'm gonna use it now.:-D

That’s Plan B. Plan A was one of those 30-plus-letter German words meaning “a tenuous conclusion derived from a sweeping generalisation” and sounding like the curse of the century.

Sadly our translators here didn’t have time to play.

^{“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

Alan Burstein SSC Guru Points: 61136 More actions · Answer 5

ChrisM@Work (2/24/2015)
SQLRNNR (2/24/2015)
ChrisM@Work (2/24/2015)
Dogma spawns bad code as efficiently as laziness and ignorance.
Nice. Great quote. I'm gonna use it now.:-D
That’s Plan B. Plan A was one of those 30-plus-letter German words meaning “a tenuous conclusion derived from a sweeping generalisation”...

I always suspected that twitter is not very popular in Germany because they too often reach the 140 character limit before they can complete their thought. :hehe:

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

klapouchy21 Grasshopper Points: 12 More actions · Answer 6

I do the same, but only into ad-hoc queries... never ever into production.

matt 70019 Newbie Points: 9 More actions · Answer 7

When teaching beginners to always be mindful of the where clause.

Sporidium SSCommitted Points: 1550 More actions · Answer 8

PHYData DBA (2/23/2015)
bantrim (2/22/2015)
Eric M Russell (2/22/2015)
On occasion I've used: SELECT.. WHERE 1 = 0. For example, I may want to select into a temp table that is derived from another table but initially empty. Also, just for the sake of the application, there may be a need to have a stored procedure return an empty resultset with the usual column layout, even when invalid input parameters have been specified or an error has occured.
Same here. It's especially useful in using BULK INSERT, where you need the target table created beforehand. For certain ETLs, I bulk insert into a temp table, then merge the deltas into the target table.
There are programing structures that handle all of this.
None of them add a useless check to a where statement that at best is not even executed.

The statement is a SELECT..INTO that definitely will be executed, to create the empty temporary table.

I find this is the quickest way to create a temporary clone of a permanent table, especially if it contains columns defined as UDDTs.

If you explicitly CREATE the #temp table, you are forced to declare such columns using the underlying native data type for the UDDT, defeating the purpose of the abstraction.