February 18, 2015 at 11:37 am
Resender (2/2/2015)
ok this is something of a best practice of mine in my queriesI 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 can't think of why it would be a "best practice" since there is no obvious benefit. At best it's redundant, while potentially causing confusion for others reading the code. There may be some query tools or code syntax checkers that enforce or encourage usage of a WHERE clause, so perhaps that's how it got started for you.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 18, 2015 at 12:18 pm
Eric M Russell (2/18/2015)
Resender (2/2/2015)
ok this is something of a best practice of mine in my queriesI 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 can't think of why it would be a "best practice" since there is no obvious benefit. At best it's redundant, while potentially causing confusion for others reading the code. There may be some query tools or code syntax checkers that enforce or encourage usage of a WHERE clause, so perhaps that's how it got started for you.
You know I was actually thinking of while 1 = 1 or when 1 = 1 this whole time.
Now that I look closer this makes no sense in anyway.
You are adding an always true where condition that adds two or more CPU cycles to every execution as a Best Practice?!?! O_o
Guess I need to add another question to what I ask people during an Interview.
Q: When would you use WHERE 1=1 in any SQL query.
A: Only when trying to confuse others, look stupid, and be wasteful.
February 18, 2015 at 12:23 pm
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:
In which case it was a perhaps a useful means of discouraging people from writing hardwired jumps - in decent assembly languages there were a variety of jumps and the assembler provided a means to generate the relative (or absolute, as may be) address when running the link-editor (or at assemble time, in systems without linkers), so any programmer who wrote a hardwired jump (other that to an address set in stone by the hardware) should have been taken out and shot. I guess there were some assembly systems that weren't that sophisticated, but I used Assemblers on a fair number of different machines dating from the 50s and 60s (including Deuce, and there were very few older than that one) and none of the assemblers I used had that fault.
However it could be very amusing in old-fashioned microcomputers - not the modern chip things, machines that used microcode in the original sense. Often one had to wait for a number of processor cycles before something would be effective, and if one couldn't find anything useful to do with those cycles (generally, but not always, that meant if one was an idiot or even worse an automatic translator) one had to fill them with some sort of NOOPs and sometimes those could take forms like "if true do nothing" (sometimes something like "IF 0 != 0 SKIP NEXT INSTRUCTION") and eliminating one of those could have startling consequences - eg picking up whatever random junk was on the main store interface instead of the line you'd scheduled to be there 1 instruction cycle later.
Tom
February 18, 2015 at 12:28 pm
Eric M Russell (2/18/2015)
Resender (2/2/2015)
ok this is something of a best practice of mine in my queriesI 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 can't think of why it would be a "best practice" since there is no obvious benefit. At best it's redundant, while potentially causing confusion for others reading the code. There may be some query tools or code syntax checkers that enforce or encourage usage of a WHERE clause, so perhaps that's how it got started for you.
From my test, it appears the SQL optimizer ignores "WHERE 1 = 1", and the execution plan for both of the following are identical. So, even if adding this type of thing to an ad-hoc query were an attempt to force a new execution plan, it probably wouldn't work.
select * from MyTable;
select * from MyTable where 1 = 1;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 18, 2015 at 12:30 pm
TomThomson (2/18/2015)
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:
In which case it was a perhaps a useful means of discouraging people from writing hardwired jumps - in decent assembly languages there were a variety of jumps and the assembler provided a means to generate the relative (or absolute, as may be) address when running the link-editor (or at assemble time, in systems without linkers), so any programmer who wrote a hardwired jump (other that to an address set in stone by the hardware) should have been taken out and shot. I guess there were some assembly systems that weren't that sophisticated, but I used Assemblers on a fair number of different machines dating from the 50s and 60s (including Deuce, and there were very few older than that one) and none of the assemblers I used had that fault.
However it could be very amusing in old-fashioned microcomputers - not the modern chip things, machines that used microcode in the original sense. Often one had to wait for a number of processor cycles before something would be effective, and if one couldn't find anything useful to do with those cycles (generally, but not always, that meant if one was an idiot or even worse an automatic translator) one had to fill them with some sort of NOOPs and sometimes those could take forms like "if true do nothing" (sometimes something like "IF 0 != 0 SKIP NEXT INSTRUCTION") and eliminating one of those could have startling consequences - eg picking up whatever random junk was on the main store interface instead of the line you'd scheduled to be there 1 instruction cycle later.
Tom - I also remember that. I never had to work with Deuce or Fortran Tape Machines.
I did get to watch my Uncles curse at them for hours on end.
They also mentioned several times taking <this engineer or that programmer> out back and shooting them.
I grew up thinking this is what happened if you made programming errors at the Machine or Microcode.
Can not tell you how let down I was to learn that was not true.
February 18, 2015 at 3:11 pm
PHYData DBA (2/18/2015)
Eric M Russell (2/18/2015)
Resender (2/2/2015)
ok this is something of a best practice of mine in my queriesI 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 can't think of why it would be a "best practice" since there is no obvious benefit. At best it's redundant, while potentially causing confusion for others reading the code. There may be some query tools or code syntax checkers that enforce or encourage usage of a WHERE clause, so perhaps that's how it got started for you.
You know I was actually thinking of while 1 = 1 or when 1 = 1 this whole time.
Now that I look closer this makes no sense in anyway.
You are adding an always true where condition that adds two or more CPU cycles to every execution as a Best Practice?!?! O_o
Guess I need to add another question to what I ask people during an Interview.
Q: When would you use WHERE 1=1 in any SQL query.
A: Only when trying to confuse others, look stupid, and be wasteful.
Like others have said, I don't like seeing it, but I understand why some developers use it.
While you're developing a complex query with many conditions in a where clause (or even a join, yuck!), if you write it as:
where 1=1
and table.x = table.y
and table.z = @parm
and table.w >= getdate()
etc...
you can easily comment out any and all predicates without having to "worry" about the first one. It (the 1=1) should not survive to see a production server.
I think it's just lazy though.
Don Simpson
February 19, 2015 at 1:28 am
I guess I get to be in the minority here. I don't use it everywhere, but it does have its purpose. One notable example is in building dynamic sql for reports off of the TouchWorks (healthcare EMR) backend. Users (nurses, doctors, etc.) can create their task lists on the front end by selecting from a wide variety of criteria, like billing location, task owner, created date, etc. Each of these pieces of criteria are stored in a table that looks a lot like:
UserPreferenceID TaskListName TextString (not joking on that field name either)
123 My neuro tasks BillingDivision = 'Neurology'
123 My neuro tasks SchedulingProvider = 'Dr. Bob'
123 My neuro tasks TaskStatus <> 'Completed'
456 All current tasks TaskStatus = 'Active'
456 All current tasks BillingProviderID IN (243,5326,235)
So when reports are needed on the task lists, and the users want to see their familiar task lists that they see on the EMR interface...:
DECLARE @sqlStmt varchar(MAX), @whereList varchar(MAX)
SELECT @whereList = COALESCE(@whereList + ',', '') + TextString
FROM dbo.TaskPreferences
WHERE UserPreferenceID = @tlID
EXEC(
'SELECT TaskStatus
,COUNT(*) TaskCount
FROM dbo.Tasks
WHERE 1 = 1 ' + ISNULL(@whereList,''))
This is very simplified, but I think it shows one decent use of it. Other than that, similar to placing the commas at the beginning of each line in the select statement, having an 'AND...' at the beginning of each test makes some tedious development slightly less painful, since commenting/uncommenting out various pieces becomes a bit quicker.
Somedays....it's the small victories that keep you going!
February 19, 2015 at 1:29 am
Eric M Russell (2/18/2015)
Resender (2/2/2015)
ok this is something of a best practice of mine in my queriesI 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 can't think of why it would be a "best practice" since there is no obvious benefit. At best it's redundant, while potentially causing confusion for others reading the code. There may be some query tools or code syntax checkers that enforce or encourage usage of a WHERE clause, so perhaps that's how it got started for you.
It's a development trick, of which there are many. In production code I'd argue that it's an indication that development is incomplete. The house isn't finished if it's still clad in scaffolding.
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 19, 2015 at 1:32 am
Eric M Russell (2/18/2015)
Eric M Russell (2/18/2015)
Resender (2/2/2015)
ok this is something of a best practice of mine in my queriesI 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 can't think of why it would be a "best practice" since there is no obvious benefit. At best it's redundant, while potentially causing confusion for others reading the code. There may be some query tools or code syntax checkers that enforce or encourage usage of a WHERE clause, so perhaps that's how it got started for you.
From my test, it appears the SQL optimizer ignores "WHERE 1 = 1", and the execution plan for both of the following are identical. So, even if adding this type of thing to an ad-hoc query were an attempt to force a new execution plan, it probably wouldn't work.
select * from MyTable;
select * from MyTable where 1 = 1;
Any change to the query - even whitespace - will trigger a recompile which could result in a different plan.
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 19, 2015 at 1:35 am
DonlSimpson (2/18/2015)
...I think it's just lazy though.
When "lazy" translates to "quick, easy and accurate" it's not so bad.
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 19, 2015 at 7:33 am
If you choose to compare a column to itself, then understand that it's not functionally the same as "1 = 1", the result will exclude rows with NULL values.
create table #t (id int not null, yn char(1) null);
insert into #t ( id, yn )
values (1, 'Y'), (2,'N'), (3,null), (4,'Y');
select * from #t;
id yn
----------- ----
1 Y
2 N
3 NULL
4 Y
(4 row(s) affected)
select * from #t where yn = yn;
id yn
----------- ----
1 Y
2 N
4 Y
(3 row(s) affected)
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 19, 2015 at 8:03 am
ChrisM@Work (2/19/2015)
Eric M Russell (2/18/2015)
Resender (2/2/2015)
ok this is something of a best practice of mine in my queriesI 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 can't think of why it would be a "best practice" since there is no obvious benefit. At best it's redundant, while potentially causing confusion for others reading the code. There may be some query tools or code syntax checkers that enforce or encourage usage of a WHERE clause, so perhaps that's how it got started for you.
It's a development trick, of which there are many. In production code I'd argue that it's an indication that development is incomplete. The house isn't finished if it's still clad in scaffolding.
I've found that it usually occurs when some form of dynamic SQL (done correctly or not) is being used so that one doesn't have to calculate if the first "working" criteria in the WHERE clause needs to be prefaced with an "AND" or not. It does make coding a lot simpler for some. Except for burning an extra couple of clock cycles, I don't see where it hurts anything performance wise and I won't climb on someone during a peer review for it. I just don't like seeing it. It's kind of like not having one pair of socks matched up in a sock drawer. It doesn't really matter but I hate seeing it.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 19, 2015 at 8:10 am
bantrim (2/19/2015)
I guess I get to be in the minority here. I don't use it everywhere, but it does have its purpose. One notable example is in building dynamic sql for reports off of the TouchWorks (healthcare EMR) backend. Users (nurses, doctors, etc.) can create their task lists on the front end by selecting from a wide variety of criteria, like billing location, task owner, created date, etc. Each of these pieces of criteria are stored in a table that looks a lot like:UserPreferenceID TaskListName TextString (not joking on that field name either)
123 My neuro tasks BillingDivision = 'Neurology'
123 My neuro tasks SchedulingProvider = 'Dr. Bob'
123 My neuro tasks TaskStatus <> 'Completed'
456 All current tasks TaskStatus = 'Active'
456 All current tasks BillingProviderID IN (243,5326,235)
So when reports are needed on the task lists, and the users want to see their familiar task lists that they see on the EMR interface...:
DECLARE @sqlStmt varchar(MAX), @whereList varchar(MAX)
SELECT @whereList = COALESCE(@whereList + ',', '') + TextString
FROM dbo.TaskPreferences
WHERE UserPreferenceID = @tlID
EXEC(
'SELECT TaskStatus
,COUNT(*) TaskCount
FROM dbo.Tasks
WHERE 1 = 1 ' + ISNULL(@whereList,''))
Somedays....it's the small victories that keep you going!
What are you winning?
Using Dynamic SQL instead means you loose performance and data layer separation.
Adding a schema scan to every execution by using Count(*) you loose too many things to list here.
Using Count(*) means you are counting rows not actual values so you loose reliability.
This is just the first three things I see in what you posted.
Writing simple code is not something for the simple mind.
It takes a good knowledge of what you are doing, why you are doing it, and how it will execute to create code that is easy to read and maintain.
Of course as one of my Teachers used to say: You don't get an A for turning in something simple, you get an A for doing something simply wonderful.
February 19, 2015 at 8:32 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.
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.
February 19, 2015 at 8:46 am
PHYData DBA (2/19/2015)
bantrim (2/19/2015)
I guess I get to be in the minority here. I don't use it everywhere, but it does have its purpose. One notable example is in building dynamic sql for reports off of the TouchWorks (healthcare EMR) backend. Users (nurses, doctors, etc.) can create their task lists on the front end by selecting from a wide variety of criteria, like billing location, task owner, created date, etc. Each of these pieces of criteria are stored in a table that looks a lot like:UserPreferenceID TaskListName TextString (not joking on that field name either)
123 My neuro tasks BillingDivision = 'Neurology'
123 My neuro tasks SchedulingProvider = 'Dr. Bob'
123 My neuro tasks TaskStatus <> 'Completed'
456 All current tasks TaskStatus = 'Active'
456 All current tasks BillingProviderID IN (243,5326,235)
So when reports are needed on the task lists, and the users want to see their familiar task lists that they see on the EMR interface...:
DECLARE @sqlStmt varchar(MAX), @whereList varchar(MAX)
SELECT @whereList = COALESCE(@whereList + ',', '') + TextString
FROM dbo.TaskPreferences
WHERE UserPreferenceID = @tlID
EXEC(
'SELECT TaskStatus
,COUNT(*) TaskCount
FROM dbo.Tasks
WHERE 1 = 1 ' + ISNULL(@whereList,''))
Somedays....it's the small victories that keep you going!
What are you winning?
Using Dynamic SQL instead means you loose performance and data layer separation.
Adding a schema scan to every execution by using Count(*) you loose too many things to list here.
Using Count(*) means you are counting rows not actual values so you loose reliability.
This is just the first three things I see in what you posted.
Writing simple code is not something for the simple mind.
It takes a good knowledge of what you are doing, why you are doing it, and how it will execute to create code that is easy to read and maintain.
Of course as one of my Teachers used to say: You don't get an A for turning in something simple, you get an A for doing something simply wonderful.
The COUNT (*) was just an example, and an over simplified one at that. The point I was trying to make revolves around being able to dynamically pull task lists that users create on the end for various reports on the backend. Reread the code again, but focus on the part that concatenated the criteria pieces for the WHERE clause. How else would you handle the doctors' request to be able to get details about their task list that aren't available in the front end, when the criteria behind the task lists can change at any time?
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply