April 3, 2008 at 7:31 am
Thanks for confirming that. That's another good reason for fully-qualifying column names.
I had also noticed the foobar thing myself and agree it seems like a sope issue in the column names. But the statement would be valid if foobar was really a column in the users table.
So if the column name you use is the same name it seems like it doesn't bother checking whether it actually exists (or it doesn't process the error).
April 3, 2008 at 7:37 am
SQLZ (4/3/2008)
Here's another good reason why not to use IN:Run this on the AdventureWorks database in SQL 2005:
begin tran
delete Sales.SalesOrderDetail where SalesOrderId in (select SalesOrderId from Doctor)
rollback tran
Whoops! You've just deleted 120,000 SalesOrders. Hopefully you included the "rollback tran" in that statement you just ran. Now consider the fact that the table Doctor has no column named SalesOrderId. If you run the inner query by itself it generates an error, but if you run the statement as a whole it deletes every row in SalesOrderDetail.
All it takes is for a developer to accidentally type in the wrong table name in the inner query and you could end up in serious trouble.
Well - this is when you need that new "Omega13" thingies from SQLSkills (allowing you to travel back in time and correct a single mistake).
In all seriousness though, how is this the fault of "IN" versus any other syntax? You could do that user-based error with any of the syntaxes. That also works with EXISTS....
Not that I don't think that SQL Server shouldn't stop me from doing stupid things, too, but I'd be a little afraid it wouldn't let me do ANYTHING...:D
----------------------------------------------------------------------------------
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?
April 3, 2008 at 7:40 am
SQLZ (4/3/2008)
JohnG (4/3/2008)
I got caught with this in a trigger referencing the deleted table. It was a syntax error that SQL Server should have caught and thrown an error. This is just a BAD BUG in SQL Server. Shame on you Microsoft!That is why you should always qualify every column with the table name (or an alias).
Actually this isn't a SQL Server issue rather than a SQL issue. I can't remember where I saw this but I remember reading about it and how it was a "feature" of SQL and how you'd get this same behaviour on Oracle or other RDMBS's.
I never went out to verify this so I might be wrong.
Can anyone confirm whether this is the case?
It's not a bug - by giving us the ability to create correlated sub-queries, it does allow us some room to hang ourselves, yes. But then again, you can do a LOT of damage with a nailgun, too...if you use it incorrectly.
It would have no way to know where that particular parameter is supposed to originate since it wasn't qualified.
----------------------------------------------------------------------------------
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?
April 3, 2008 at 8:02 am
Is it worthwhile to consider the value of 'ambiguity' error or warning messages? How terrible would it be if SQL required FULLY qualified names? Would that affect portability of code? SHOULD it affect portability of code? I realize this is a thread hijacking - expanding what was a 'modest' question asking the difference between two conditional clauses (styles?), so if there's interest, perhaps we should start a new thread for this question?
April 3, 2008 at 8:18 am
Portability? Have you ever tried to make 100% portable code and still have it actually do something more than return data to a GUI with simple SELECTs? Even true ANSI code isn't 100% portable because not everyone follows the "standard". Even if they did, using only ANSI code is like using only the 4 basic math functions of a scientific calculator 😀
Perhaps it's a fault on my part, but I never worry about portability when so few can get scalability and performance down pat.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 3, 2008 at 8:19 am
More on the above... try solving the running total problem in 3 or 4 different RDBMS engines... and still have some scalability and performance left over.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 3, 2008 at 8:26 am
Jeff Moden (4/3/2008)
Portability? Have you ever tried to make 100% portable code and still have it actually do something more than return data to a GUI with simple SELECTs? Even true ANSI code isn't 100% portable because not everyone follows the "standard". Even if they did, using only ANSI code is like using only the 4 basic math functions of a scientific calculator 😀Perhaps it's a fault on my part, but I never worry about portability when so few can get scalability and performance down pat.
At the risk of starting up a religious war again...I will worry about portability when I see the CIO whip out that mega-AmericanExpress card to drop 12M in licensing fees to Oracle...
----------------------------------------------------------------------------------
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?
April 3, 2008 at 11:47 am
Jeff Moden (4/3/2008)
Portability? Have you ever tried to make 100% portable code and still have it actually do something more than return data to a GUI with simple SELECTs? Even true ANSI code isn't 100% portable because not everyone follows the "standard". Even if they did, using only ANSI code is like using only the 4 basic math functions of a scientific calculator 😀Perhaps it's a fault on my part, but I never worry about portability when so few can get scalability and performance down pat.
I've seen actual portability on real production databases with very complex code.
To achieve it, you unhook the server from its rack, load it up on a dolly, trundle it across the room, lift it onto the new rack, and ... oh wait ... wrong kind of portability. Never mind. (The other one, so far as I can tell, is an urban legend.)
- 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
April 3, 2008 at 11:54 am
GSquared (4/3/2008)
I've seen actual portability on real production databases with very complex code.
To achieve it, you unhook the server from its rack, load it up on a dolly, trundle it across the room, lift it onto the new rack, and ... oh wait ... wrong kind of portability. Never mind. (The other one, so far as I can tell, is an urban legend.)
Hehe. Last year we "ported" our last SQL 7.0 server in much the same way. Right out the window and into the dumpster......:P
----------------------------------------------------------------------------------
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?
April 3, 2008 at 12:00 pm
Of course, there is always "Portability Light", but it only works on smaller databases.
You copy the database onto a flash drive, walk over to the other server ...
(I have a flash drive on my keychain that has the installer files for SQL 2005 Express and Management Studio Express on it, and a little over 3 Gig of free space. That's plenty of room for most small business databases.)
- 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
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply