February 19, 2015 at 9:09 am
I wouldn't consider myself "lazy", but I do try to maintain only a small manageable number of priorities that I perceive "matter". Most IT developers do, which is why we are occasionally at odds with project managment, marketing, and corporate culture in general.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 19, 2015 at 10:01 am
PHYData DBA (2/19/2015)
ChrisM@Work (2/19/2015)
DonlSimpson (2/18/2015)
...I think it's just lazy though.
When "lazy" translates to "quick, easy and accurate" it's not so bad.
That statement says it all.
It is a perfect example of the kind of Lazy thinking that creates/teaches horrible programing skills that are making more unusable and unreadable code structures commonplace in many of todays applications.
There is nothing quick, easy, or accurate (or even Lazy) about adding a statement to code that does nothing.
At the very least It creates more work to understand why it is there and what it is doing.
If it was something that was included in every SQL where clause, like was mentioned at the beginning of this post, in my environment this would be added to 45,000 different statements that execute at least 10 times an hour.
There is nothing quick, easy, or accurate about that. definitely not if you are asking if it should be a "Best Practice" idea.
This kind of thing does have a place to help as a place holder SQL code that is already outside of the Best Practice idea.
Putting it everywhere as a replacement for "TODO:" is just silly.
+1
Don Simpson
February 20, 2015 at 10:51 pm
I use this when I am developing and want to set up the structure to easily comment out conditions in the WHERE clause if needed. It was weird to me when I first saw a co-worker using it but as the complexity of the query increases I find it to be a time saver. Remember we all want to be lazy we just may not know it. Who doesnt take the shortest path to work, school? The meaning of efficiency is doing things with less energy. If there is a new function that can replace ten lines of code ... is it always better to not use it? Why do we keep furthering technology? It is not a matter of laziness, but one of practicality. This syntax is out of your comfort zone if you are not used to doing it. I am not sure how much it impacts actual performance, as SQL execute plans and not syntax necessarily. (Note , I do agree the code looks unfinished QA wise if left in production this way).
----------------------------------------------------
February 22, 2015 at 10:03 am
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 22, 2015 at 10:55 am
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.
February 23, 2015 at 7:33 am
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.
February 23, 2015 at 8:15 am
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.
At the end of the day, when I need to do an ad hoc bulk insert of a flat file that is structurally the same as a table that already exists, I am going to prefer a simple:
SELECT * INTO #tmp FROM table WHERE 1 = 0
as opposed to
CREATE TABLE #tmp
(
field1 datatype
,field2 datatype
...
,fieldn datatype
)
Both run in a fraction of a second, but one of these takes less time to setup.
February 23, 2015 at 8:18 am
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?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 23, 2015 at 8:20 am
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.
February 23, 2015 at 8:24 am
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.
Care to divulge?
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
February 23, 2015 at 8:54 am
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.
February 23, 2015 at 9:17 am
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.
February 23, 2015 at 10:46 am
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.
I have been an IT professional since high school.
You're comment full of assumptions and unfounded conclusions about the desires behind 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: The topic is using WHERE 1 = 1 as a best Practice. Not wasting our lives preaching about people that deploy code with WHERE 1 = 0 in it. I understand it is easy to get confused about these things.
I also spend a lot of time finding and removing nested selects, sub querries, non parameterized SQL, etc... from the code executing against our servers.
The first thing I did when I started at this DBA lifecycle was a code analysis of restored schema only backups or all our production databases.
One of the things this does is look for SQL that the RDBMS QA processor re-writes prior to execution.
It is an existing best practices on any RDBMS SQL server to update this code so this does not occur.
So what I would like to point out is if you have a Best Practice that adds "where 1=1" to all SQL then all of your SQL is failing another Best Practice for RDBMS.
Know your code and you will know happiness 😎
February 23, 2015 at 10:49 am
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.
February 23, 2015 at 10:57 am
ChrisM@Work (2/19/2015)
DonlSimpson (2/18/2015)
...I think it's just lazy though.
When "lazy" translates to "quick, easy and accurate" it's not so bad.
I agree with Chris here.
DBAs should be striving to be "lazy" / "efficient, accurate, quick" imho. :-D:w00t:
I like to use the 1=1 trick when I am fleshing out the predicate. Then I remove it if the code is going to be used in a repeatable fashion.
I remember advice from my first job "Worker smarter not harder." As a DBA, using 1=1 is just working smarter ( a lot of times).
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
Viewing 15 posts - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply