January 12, 2009 at 1:21 pm
Because chocolate IS better than vanilla! 🙂
Yeah, choices in cases where it doesn't much matter should be fine. I'd say that kind of thing, like which format to use, is more of a "develop internal standards for your shop" kind of thing, than a "you must do it the way a bunch of academics who set standards say". Unfortunately, I don't make Microsoft policy, and I don't think they're taking suggestions on this particular point. (And there are plenty of things I'd rather see them spending their time on than this one.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2009 at 2:46 pm
Jeff Moden (1/12/2009)
GSquared (1/12/2009)
Agree with you on most points.I like the Inner Join syntax. Makes it easier for me to read (not for you, but it does for me).
That would also be an excellent point to Microsoft... why not give folks a choice? 🙂 I will admit, I hate the old *= and =* method for doing outer joins because I think they're much more difficult to read, but even that's a personal preference. Let people do it either way. Why do ANSI standards deprecate things that aren't harmful?
I was with you all of the way up to here. the old syntax was one of the few things I happen to agree on, due to the ambiguity it introduces. That's one that did "seem harmful" to me. But otherwise - keep on preaching, Brother Man!
----------------------------------------------------------------------------------
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?
January 12, 2009 at 10:02 pm
ramadesai108 (1/9/2009)
Grant mentioned about generated code, but this code is certainly not generated through any code generator...
How can you be so sure? I have written a lot of specialized code generators for various projects and the generated code often looks just like that.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 13, 2009 at 9:03 am
I am guilty of this WHERE 1=1 trick, to make commenting out MUCH easier
Compare
SELECT *
FROM Table
WHERE 1=1
AND Name = 'Jerry'
AND Gender = 'Male'
vs
SELECT *
FROM Table
WHERE Name = 'Jerry'
AND Gender = 'Male'
Now if I want to comment out the Name = 'Jerry' part..... it takes 1 line change in first code, but 2 line changes in 2nd code
SELECT *
FROM Table
WHERE 1=1
--AND Name = 'Jerry'
AND Gender = 'Male'
vs
SELECT *
FROM Table
WHERE --Name = 'Jerry'
--AND
Gender = 'Male'
Maybe I should go crazy, and use 9=9, or 99=99........ 1=1 sounds so boring :hehe:
January 13, 2009 at 9:03 am
I use Where 1=1 in some of my SP that dynamically search the DB as follows. When the search form opens it will display all items initially because no params have been passed.
Alter PROCEDURE [dbo].[DynamicSQLSearchAdmin]
@ContractNumber nvarchar(30) = NULL,
@VenderName nvarchar(50) = NULL,
@Location nvarchar(50) = NULL,
@debug bit = 0 AS
DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)
Begin Try
--Concatinate Mineral Table to Make it Searchable
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
set QUOTED_IDENTIFIER ON
SET NOCOUNT ON
SELECT @sql = 'SELECT ContractNumber, Convert(Varchar(50), StartDate, 101) as StartDate, Convert(Varchar(50), EndDate, 101) as EndDate, Location, Project, Task, ContractOwner, Amount,
LinkPDF, VenderID
FROM ContractNumber
WHERE 1 = 1'
--ContractNumber
IF @ContractNumber IS NULL OR @ContractNumber = N''
Select @sql = @sql
Else
SELECT @sql = @sql + ' AND ContractNumber = @xContractNumber'
--VenderName
IF @VenderName IS NULL OR @VenderName = N''
Select @sql = @sql
Else
SELECT @sql = @sql + ' AND VenderID = @xVenderName'
--Location
IF @Location IS NULL OR @Location = N''
Select @sql = @sql
Else
SELECT @sql = @sql + ' AND Location = @xLocation'
IF @debug = 1
PRINT @sql
SELECT @paramlist = '@xContractNumber nvarchar(30), @xVenderName nvarchar(50), @xLocation nvarchar(50)'
EXEC sp_executesql @sql, @paramlist, @ContractNumber,
@VenderName, @Location
End Try
Begin Catch
Select Error_Number() ErrorNumber, Error_Message() Message
End Catch
January 13, 2009 at 10:05 am
I have also seen this as a "feature" on many code generators similar to what was previously described. Having the "where 1=1" stub provides a post from which you can begin to hang additional AND clauses or whatever else might be appended.
January 13, 2009 at 11:24 am
GSquared (1/9/2009)
I've seen requests to Microsoft to make having a Where clause on every query be required, with the solution to unrestricted queries that they could have something like "Where 1 = 1". The purpose is to make it so you don't accidentally run an update/delete without a Where clause (which can be quite a bad thing).
Yes, an UPDATE without a WHERE clause could be nasty. However if your WHERE is 1 = 1 then this does not protect you at tall. Since 1 = 1 always evaluates as true, there is no difference as if no WHERE clause was added...
As suggested by others, if you sometimes need to disable (temporarily) the first statement in a WHERE clause, leaving only active a new statement such as ' AND (condition 2) ... which would cause it to fail, then MAYBE always throwing in a 1 = 1 as the first statement would MAYBE makes things a little easier. Not for me. Unless this was a stated coding standard within an organization I would always wonder what the hell is this for. And I would happily disregard such convention.
And NO, I do not put the AND keyword at the end of a statement. What happens if the last statement is the one I want to disable for debugging purposes ? A dangling unterminated AND is no better than an opening AND without a previous statement.
So far, I do not see any point in adding what I still consider clutter in the code. Is not life complicate enough as it is now ?
Regarding the JOIN style, I too prefer it over the old
SELECT
FROM A, B
WHERE B.xxx = A.yyy
January 13, 2009 at 11:32 am
As far as the join style - Jeff, you are crazy. The JOIN keyword is much more readable. Ok, you are not crazy, it is all opinion. I like the newer syntax.
As far as a server option to make a WHERE clause required, I can see some benefit, but it really is to protect idiots from themselves.
If you type:
[font="Courier New"]DELETE MyTable
WHERE RecNum = 1[/font]
and then you are dumb enough to accidentally highlight just the first row and press F5, and you have auto-commit on, and you did not have a transaction open, and you are too slow to cancel before the delete completes - well, forcing a delete to have a where clause would be useful.
Of course, if all of that happened you shouldn't have a tool that allows you to run an update or delete against a production database and taking the tools away from these people is probably a more effective solution.
January 13, 2009 at 11:45 am
Like some have already pointed out, WHERE 1=1 is helpful while trying out additional conditions only since it seldom adds value if it's part of the final code. Code generators use them perhaps for simply adding AND in the WHERE clauses.
Other than these 2, I don't seem to find any other useful reason for it and I definitely don't understand why any organization would make this a standard! Maybe it's just me.
January 13, 2009 at 11:46 am
J (1/13/2009)
GSquared (1/9/2009)
I've seen requests to Microsoft to make having a Where clause on every query be required, with the solution to unrestricted queries that they could have something like "Where 1 = 1". The purpose is to make it so you don't accidentally run an update/delete without a Where clause (which can be quite a bad thing).Yes, an UPDATE without a WHERE clause could be nasty. However if your WHERE is 1 = 1 then this does not protect you at tall. Since 1 = 1 always evaluates as true, there is no difference as if no WHERE clause was added...
As suggested by others, if you sometimes need to disable (temporarily) the first statement in a WHERE clause, leaving only active a new statement such as ' AND (condition 2) ... which would cause it to fail, then MAYBE always throwing in a 1 = 1 as the first statement would MAYBE makes things a little easier. Not for me. Unless this was a stated coding standard within an organization I would always wonder what the hell is this for. And I would happily disregard such convention.
And NO, I do not put the AND keyword at the end of a statement. What happens if the last statement is the one I want to disable for debugging purposes ? A dangling unterminated AND is no better than an opening AND without a previous statement.
So far, I do not see any point in adding what I still consider clutter in the code. Is not life complicate enough as it is now ?
Regarding the JOIN style, I too prefer it over the old
SELECT
FROM A, B
WHERE B.xxx = A.yyy
To clarify, the point is the difference between accidentally running:
update dbo.MyTable
set Col1 = 5
and deliberately running:
update dbo.MyTable
set Col1 = 5
where 1 = 1
The first one will update the whole table, but is that really what you meant to do, or did you just not select all the lines you meant to before you hit F5? The second one, you really did mean to update all the rows.
It helps prevent a potentially high-cost "typo", if the RDBMS requires a "Where" over allowing you to run without one.
Most front ends, before you do something like accidentally remove all items from your order, ask you, "do you really mean to remove all items from your order". Management Studio, et al, doesn't ask before you run "delete from dbo.MyTable", without a Where on it, it just does it.
That's all that's being asked for.
I'm not saying I'm in favor of it. I don't think it will actually solve the most important problem, which is people who don't care what they're doing, as opposed to people who might occassionally not pay as much attention as they really should.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2009 at 2:06 pm
As far as a server option to make a WHERE clause required, I can see some benefit, but it really is to protect idiots from themselves.
If you type:
[font="Courier New"]DELETE MyTable
WHERE RecNum = 1[/font]
and then you are dumb enough to accidentally highlight just the first row and press F5 ...
Of course, if all of that happened you shouldn't have a tool that allows you to run an update or delete against a production database and taking the tools away from these people is probably a more effective solution.
You are being harsh today...
I agree that a low-caffeine-level developer MIGHT ** BY ACCIDENT ** fail to highlight the entire command. This is more of an OOPS! type of thing than idiocy or stupidity. Now, of course, the half-awake perpetrator did ask for it - and the consequences.
Seriously, anyone using Query Analyzer and invoking the DELETE command without EVER making this mistake, fell free to cast the first stone.
... (arrrgh ! that hurt!). What's next ? FROZEN flying pork chops ?
Maybe it would be safer to write
[font="Courier New"]DELETE FROM myTable WHERE ... ON THE SAME LINE.[/font]
January 13, 2009 at 3:24 pm
If I were to get a penny for every problem I have been called to "solve" regarding "I forgot to highlight the WHERE clause (or part of it)" I would be RICH!
* Noel
January 13, 2009 at 3:31 pm
Forgetting to highlight the Where clause is just one example.
Carelessness can take a large number of forms. After all, there are an infinite number of ways to do something wrong, and usually just one way to do it right.
Had an incident a couple of months ago where a dev was trying to test something on one record, and accidentally ran an update without a Where on it at all. He was distracted, and usually doesn't make that kind of mistake, but that doesn't help the fact that a huge table in a production database was now a complete mess. And it was WAY too many rows to recover out of the transaction log, and there wasn't any sort of audit trail on the table that could be used for a rebuild either.
So, it was back to the old "point in time restore", and be glad the database wouldn't lose more than a few minutes of changes, which wasn't that big a deal in that case.
As a note, he had the production server and the dev server open in Management Studio at the same time, and thought he was testing in dev, but was actually testing in production. Again, there's a flaw in the presentation in Management Studio's default layout that makes that easy to do. So, two mistakes, neither of which would have mattered all that much by itself, but together made a problem that mattered, which could have been prevented with a mandatory Where clause.
Of course, as stated before, that handles just one problem, and doesn't handle the source of the problem. There are still too many ways to mess stuff up. Always will be. And if SQL somehow became proof against human error in the code, there's always smoking in the server room!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2009 at 7:45 pm
Michael Earl (1/13/2009)
As far as the join style - Jeff, you are crazy. The JOIN keyword is much more readable. Ok, you are not crazy, it is all opinion.
Heh... nope... you're right... I'm probably crazy! 😛
--Jeff Moden
January 13, 2009 at 7:47 pm
GSquared (1/13/2009)
So, two mistakes, neither of which would have mattered all that much by itself, but together made a problem that mattered, which could have been prevented with a mandatory Where clause.
BWAA-HAA!!! You forgot the 3rd mistake... you let stupid people near your database. 😛
--Jeff Moden
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply