Oracle
-
T-SQL superior than PL/SQL? Please. I don't think so. I'm trying to be fair but is no way that we can hide the sun with one finger.
vs
I hope you like the cars. Both are good but are not the same.
-
I do have a gripe regarding MSSQL & VS 2005. It is also applicable to the post that asked if a PC Tech could install Oracle. I'm neither and I did manage to get Oracle and Sybase running on Linux. But that's not my gripe. One of the strengths of the MS tools is that they should work together: browse DB's from within VS. I've gone through 4 complete uninstall/re-install cycles and VS and MSSQL and VS
still dies after hanging for 5 minutes whenever I click the selection to browse a DB. Now, it is probably my mistake. They did have this list of "31 things that you might have to delete and they must be deleted in this order..." in the install documentation. Its hard to believe a professional development shop would put such a list in the install document. A script that did it for me would have been acceptable. No doubt, I missed one or got two in the wrong order. This is a knock on MS not specifically MSSQL. It's a great tool and I use it daily; nothing sophisticated, just looking at application data.
-
Two area's I might agree with you. Exception Handling and Cursor handling.
Cursors better be superior because they didn't support temp tables. (I know they do now to some degree)
I would take Table Variables over PL/SQL Tables.
Temp tables over Cursors any day. "ORA-01555"
CASE statements (as part of a select) weren't introduced until 9i, and they were poor at best.
T-SQL's UDF's are superior, but had to be because of PL/SQL's Cursor handling.
T-SQL's try/catch is poor at best, but improved.
flashback queries
Really, Really Cool! Oracle has that (but again that is Data Concurrency)Might be a matter of opinion, but using BOTH extensively, I don't know why you have that opinion. I can't recall a time in either environment where I wasn't able to accomplish what I needed. But I seem to have a lot less times I have issues now than before.
Deadlocks and Data Concurrency were only big issues and with Snapshot Isolation that nearly eliminates that as an issue. Granted it is at a cost. Transactions take on average about 3% longer if you are using isolation than without.
-
OK, I didn't want to our anyone with the editorial, and it was somewhat in humor, but seriously. Aren't there more complaints about SQL Server? I was hoping for some good ones?
Maybe starting SSMS in the morning gives me time to go get (and make) coffee?
SSIS provides a great programming environment, provided I don't want to be on the latest 64-bit platforms?
SQL Server is a great platform, so easy to use my Mom can install it and build an app in Access. And then give it to me to support! (kidding, Mom
)Any more?
-
Just last weekend, I watched an hour long show on worshiping Oracle.
But in the end, Mr. Spock determined it was just another program run by a computer.
Pretty much summed it up for me.
-
"Any product that has over 500+ configuration parameters has a definite arcitectural flaw" - a 'true' statement from a Sybase DBA that I know when talking about Oracle ...
Granted Oracle only wants you to mess with a little over a hundred of them but the fact remains that they do exist and they are modified more often than not with and without Oracle Support direction !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
SQL Server had dynamic sql in SQL Server 7.0 which was so easy to do. I tried to write the dynamic SQL when I was using Oracle 8i which was a pain. Oracle 10g it was better but still it was not as easy as SQL Server.
-
Well, my shop is still on 2000, so I can't comment on the startup speed of SSMS but it seems reasonably snappy on my Mac in Parallels.
My solution to slow startup times is to not turn off my PC. Make sure any queries that I'm working on are saved to the network, lock the console, and walk away. I used to hibernate my PC's, but now we're running Cisco Clean Access agent and it takes a good 10 minutes before I'm actually on the network after I start my PC in the AM.
Normally I only shut down my computer for long holiday weekends or for vacations, but they're building a new Federal court house next door and are going to be shutting off power to City Hall, so I guess I have to shut down my computer tonight.
Sorry, no real complaints about SS. Though I do have to say that doing a point in time recovery to a different database this AM did leave me a little paranoid.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
I've even been recently working for a company that had 2 DBA for 300+ Oracle DB, and... no "real" DBAs for MS DBs. Too many people still think that SQL Server is the new UI for Access ;-).
-
Hmmm... let's see...
Oracle protects me by doing an auto-commit if I happen to mix some DDL in a proc. Really helpful there. With SQL Server explicit transactions, I have to tell it what to do when I want it to do it. And, get this... Oracle is so smart that it won't even allow me to put DDL in a PL/SQL block unless I call a proc that has the DDL in it... I don't ever have to worry about accidently truncating a temp table... especially since all temp tables are global!
And, ya gotta love it... instead of auto committing when no DDL is present, Oracle makes me commit everything I want to do... if I forget to do a commit on something obvious, it protects me by throwing the data away.
The Oracle UPDATE statement is fantastic! No need to worry about the pesky FROM clause that SQL Server has... and it gives me practice typing all of those wonderful correlated subqueries... not just once, but twice in each an every UPDATE. Its making me a much better programmer.
No need to worry about writting set based code in Oracle triggers... just write some RBAR on steroids and then say FOR EACH ROW and your done. How cool is that? And, you never have to worry about making a trigger too long... they can only be 32k long no matter what you need to do.
Oracle recognized long ago, that people spend to much time on the keyboard... so they made the maximum length on all object names only 30 characters long and they make it so everyone types the same way... they force all object names to upper case and they made all string literals case sensitive so that you can easily filter between names like Mc Bride and MC Bride and MC BRIDE. Don'cha just love the simplicity in that? And doing simple things like WHERE UPPER(somecolname) = 'MC BRIDE' finds them all! A side benefit is that it makes it so you don't even need to worry about making indexes because they just won't be used except for scans.
Ya gotta love Oracle for requiring the ";" at the end of every query and every block... and, if you leave one out or put one in the wrong spot, the error messages are general enough so that you have to peer review your own code to find it. Takes a little extra time but makes for much fewer logic errors.
The output is to die for especially when trouble shooting... all you have to do is type a handful of commands at the beginning of your script and voila, readable output. If you don't want to type those commands, you can always use the DBMS_OUTPUT commands and, again, Oracle protects you by requiring that they only be done in PL/SQL blocks.
Speaking of trouble shooting, don't you love the way Oracle keeps you from accidently leaving trouble shooting code in with the real code by not allowing SELECTs to return anything to the screen in a PL/SQL block? And that DUAL table really turns you into a sharp programmer by requiring a FROM clause on every SELECT, even ones that are for trouble shooting.
Oracle also protects you from accidently overwriting the contents of a variable in the same SELECT... no worries about concatenating data into a single variable using a single SELECT.
Features like the COMPUTE clause are great... I'm sure that everyone uses those every single day.
Oracle truly separates the men from the boys on stored procs... no need to worry about some rookie writing code to show you up on getting a return to the GUI just by writing a simple proc... those wonderful global cursors are simply to die for. And the all too obvious "=>" symbol for named parameters is so intuitive that if you do a search in the documentaion for it, you won't find it. And, Oracle thought of everything... If you do a search on "named parameters", you'll get 10 thousand returns and Oracle is smart enough to not give you the correct information in the first thousand so you take the time to read about things you never even dreamed of.
I could go on and on about why I love Oracle so much better than SQL Server... but that's probably enough to get my point across... ya just gotta love Oracle to use it...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Shoot me now - I like it !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
My personal rant about SQL Server is when querying from syslogins. If the password column is selected and the results returned to Grid, any subsequent columns are shifted one place to the right of where they ought to be (run "select name, password, language, denylogin from syslogins" and you will see what I mean).
However, this query works fine when results are returned to Text. (I raised this with Microsoft and was effectively told that they weren't going to fix it, which I also found rather annoying).
However, having said that, I would just say that I have worked on both Oracle (7, 8i & 9i) and SQL Server (6.5, 7 & 2000) and give me SQL Server any day of the week.
-
Aren't we supposed to talk about SQL Server through Oracle users' eye ?
I agree with most of what Jeff says. Oracle also has weeknesses! Let me just moderate a few things :
Oracle DBA's usually don't like batches / client apps that perform DDL (ie schema modifications, like add or remove tables... drop databases...). That's just a way of working.
If a sp alters a schema, this will invalidate all dependants objects, which will be recompiled on the fly when called (T-SQL sp are not compiled). That's time consuming.
That's why temp table definition is global (not its data). It has to be thought at design-time, and delivered like other tables.
But you can always get through it with dynamic SQL.
Auto-commit : if your client app breaks down before committing, its transaction may be half-way, and generally shouldn't be committed. This is the only correct way to behave in my opinion.
UPDATE statement : you're right, Oracle misses smoothness concerning UPDATE statements. In my rememberance, you can update the child table of a join statement only if there is a FK to ensure childhood. Due to its isolation mode, I guess.
Note : why should we update FROM a table rather than IN, ON, ABOUT it? why not update a table? (don't know about which is ISO standard).
Triggers : true, Oracle does not provide an "updated" virtual table, but on the other hand, they do not require a join operation to such a table on Oracle. And triggers are not necessarily FOR EACH ROW.
Maybe when a db has 32Kb+ triggers and performs large updates, you should consider using sp to modify data, before it's too late.
Identifiers : They can be any case. They're uppercase by default if you omit double-quotes on identifiers.
Case sensitivity on char fields is one of the key to Oracle's Performance. For "human" text search, we're far behind SQL ease of use. Two solutions are function indexes on these columns (ix = upper(field)) or, better, "Text" facilities.
About semicolon separator : True. But maybe this is what makes Oracle parser more explicit. On Oracle, there's no messages like "Incorrect syntax near 'SELECT'.
Output : True again. Oracle instance receives SQL, and outputs data (rows or output variables), and/or error code.
I agree it's often a pain for debugging. It seems to be Oracle's philosophy : All the flourish (formatting, colors, sounds, and other non-database related information) is managed by the client data access layer. Even dbms_output, I think, goes through a buffer table. That's why the basic scripting tool, SQL*Plus, has formatting keywords (unknown to server) in it.
SELECTs in PL/SQL : You're right. Still part of Oracle "input/output" law.
Note : When you send a batch to SQL Server, you get intermediary results at the end of the batch anyway... ?
COMPUTE : Seldom used, but that's a formatting keyword part of SQL*Plus reporting facilities. No SQL here. There are so many things that editors make available, which we seldom use, don't you think?
Named parameters: If you look for "@" on the internet, I'm not sure your first hit will be about the way to declare named parameters in SQL Server... If you're talking about BOL... same problem (2K/2K5). You'll have to read the chapter related to coding sp in BOL or PL/SQL Guide.
-
Wow. I'd never seen that behavior before. However, on my system it is correct when results are in grid, incorrect when they're in text.
Have you noticed this in any other system tables? I work with them quite a bit and this is the first glitch like that that I've seen.
I guess I do have one complaint about SQL Server, and I believe it has already been voiced: date/time. I like having both components in one field, but I would also like discrete date and time data types.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
I was hoping for some good SQL Server comments, but maybe we just like picking on Oracle
How about these:
I love that SQL Server makes the sysadmins GOD. Useful to ensuring you hire great, trustworthy, experienced DBAs.
SQL Server is so easy, anyone can administer it. You don't need a DBA!!! Or do you 1 minute after the install.
Slipstreaming patches is so easy in SQL Server for new installs and disaster recovery, given that we now see a rollup every two months.
SQL Server now has 64-bit servers for everyone! As long as you don't really expect everything that works in 32-bit to still work in 64.
Get all transactions for Sept 10th? It's easy - select trans from Trantable where TranDate = '9/10/07'. Unless you perform transactions after midnight.
OK, maybe I'm not the funniest guy, but I'm sure you can come up with others.
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply