SQL SERVER 2005 vs ORACLE 10G

  • There has been this rat race for quite a while for which of the two is better. Oracle servers as most of them believe has a better performance and is more secure than the Microsoft counterpart. But of late, Microsoft in it's site claims that SQL server 2005 is more secure and performs better than oracle 10g and vice versa. Can any one throw more light on it?

  • All of the 'big 4' RDBMS products are fit for purpose, and all will do what most businesses need to be done. The best one to use is the one your organisation has the most experience with. If you are a Oracle shop, stick to Oracle - it will be costly to build up the same depth of expertise if you bring in SQL Server. Likewise, if you are already a SQL Server, DB2, or Sybase shop, stick with what you know.

    Very few organisations will have a problem big enough to justify bringing in the RDBMS that best matches their leading-edge requirements. If you are leading the edge on TPS, DB Size, connections or whatever, then you need to pick the product that does what you need - the software costs will not be significant in a project of this size.

    So, which is the best product? It depends. It depends on whatever you already have, or whatever you need to do.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • Go to tpc.org to see that all the major players have TREMENDOUS scalability on current hardware. We have lots of options for maximum performance or cost optimization. Not many organizations need 1M+ transactions per minute, btw! 🙂

    Since scale isn't the differentiator it used to be 10+ years ago, things like hardware cost, ease of development/maintenance, security, etc come to the fore in the decision-making process. Some of those are explicit and others (like security) are pretty nebulous.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Thanks EdVassie and TheSqlGuru for ur replies.

  • You are right. This is a rat race. But in a rat race it is not guarented that the older and fatter rat will necessarily win.

    SQL Server is surely a winner for its business intelligence and reporting solutions SSIS and SSRS

    After all a user is interested in getting his requirements fulfilled.

    -----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

  • I totally agree but the fact that oracle has it's own kinda reputation and still some dba's blindly feel that when the size of their database increases oracle is the best buy. Also to keep in mind that this reputation was not hyped by marketing strategies like some of the microsoft products. EdVassie had given a very diplomatic answer to this debate and theSQLguru has given a very useful link to take the tpc performance numbers into consideration.

    Eventually the bottom line, well said by Mazharuddin Ehsan

    "After all a user is interested in getting his requirements fulfilled."

  • Keep in mind that the Windows platform is not Oracle's main focus. UNIX platforms almost always get patches or bug fixes before the Windows platform. I'm always told the fix is available, but not for Windows.

    I agree with the others; cost, skill, O/S, needed features, etc play a role in commiting to a given database system. SQL Server is definitely less expesive and easier to use. I've been porting an Oracle DSS to SQL Server and I really like developing and debuging in SSIS. At first I did not think SQL Server would scale to 1 TB on my hardware, but it is actually faster then the dozens of approaches I tried on Oracle. Under our agreements, SQL Server is ~$147,000 less than Oracle. Based on performance, cost, and being able to develop in SSIS we are making the swith to SQL Server and dumping Oracle for this product.

  • Also, in my experiences I've only seen DB2 used in production where old IBM mainframes are found somewhere in the building or attached to the network. We dumped support for Sybase because none of our customers use it any more, I'm not sure who is. However they are marketing very hard these days, but I wonder why... Oracle is where UNIX is, and SQL Server is everywhere else. From offices with no DBA (usually supported via some ISV or implementation partner), to departments with basic SQL Server experience, to enterprise OLTP and DSS implementations spanning the globe.

  • Jeez... where do I start...

    1. The output of SQL Server stored procedures is easily used as a result set... for Oracle, you must build a reference cursor in a package. And, you can't just select from the package... you must open the reference cursor and step through it to see the return... absolutely inconvenient from troubleshooting, modifications, and testing.

    2. SQL Server preserves the capitalization used on table and column names as they were in the Create statement... works real nice for mixed casing. Even the script generators in SQL Server preserve the casing. In Oracle, it all changes to upper case. Sure, you can write code in mixed case, but if you need to regen a table or the like, you get all upper case. Same holds true in any of the tools you may examine a table/column names with. That's why everyone who uses Oracle also uses underscores.

    3. Want a naming convention? Well, even if you don't, you'd better come up with one for Oracle... object names can only be 30 characters in length and you'll chew a bunch of those up with all the underscores you'll want for readability. In SQL Server, they can be 128.

    4. Sometimes, when creating sophisticated auditing, ya just gotta build a trigger a little larger than 32k bytes... can't be done in Oracle, though.

    5. Just TRY to write a set based trigger in Oracle... just go ahead and try!

    6. Need to do a little DDL in your Oracle proc? Be careful!!! Execution of DDL does an auto-commit even if you don't want it to. And to actually do DDL in a proc (including truncating a table), you have to use dynamic SQL because DDL isn't actually allowed in procs.

    7. Don't even think about using a temp table in Oracle.

    8. Forget about overloading variables in a single select in Oracle.

    9. Oracle's error messages will actually lead you away from where the error in the code is. And don't you dare leave out a ";"... well, unless it's required to leave it out and then don't you dare put it in.

    10. You can't do a simple select like SELECT 1+2 just for testing purposes or for any other reason in Oracle... at the very least, you'll need to write SELECT 1+2 FROM DUAL.

    11. Oracle is case sensitive for strings. Some folks like that, but most do not.

    There's a couple of nice-to-have features in Oracle like CONNECT BY for hierarchical data and the fact that they have true "BEFORE" triggers instead of those bloody INSTEAD OF triggers... and, if you really need it and you took the time to install it, it does have RegEx built in... but those little conveniences don't make up for the awful programming interfaces nor the awful requirements and limitations within code.

    My opinion... If you're a good set-based programmer, Oracle will frustrate the heck out of you... you get a bigger bang for the buck with SQL Server.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Oh yeah... almost forgot... if you hate the fact that SQL Server 2000's VARCHAR only goes up to 8,000 characters and did hand-springs over SQL Server 2005's VARCHAR(MAX) so you didn't have to use the awful TEXT data type, then you'll just love the fact that Oracle's VARCHAR2 only goes up to 4,000 bytes... unless you want to use the "wonderful" CLOB data type.

    You'll also love the fact that "INT" and all the Numeric data types take up the same number of storage bytes no matter how big or small you define them in Oracle.

    And, just wait until you use something like TOAD to make up for the fact that Oracle's GUI's are so terrible... yeeach! Gimme QA/EM or SSMS any day!

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff, you have made some good points, but pardon me for thinking it comes across as just a teensy bit anti Oracle!

    It is worth noting that the default case sensitivity behaviour can be altered in both SQL Server and Oracle (Oracle can be made non case sensitive and vise versa with SQL Server) simply by changing the collation. Also object name capitalisation can be turned off in Oracle.

    Many Oracle developers complain about SQL Server's default locking behaviour (Oracle defaults to row level locks more often) - again this behaviour can be changed in both RDMSs. The standard tools for older versions of Oracle (SQL*Plus & SQL worksheet) are rather clunky, but the free SQL Developer is much more user friendly and has been out for a while now.

    PL/SQL is in general a more powerful and flexible language than T-SQL - sure there are little features in 1 that are better that in the other, that's always the way when comparing 2 languages.

    Something that is not insignificant for the DBA - Oracle skills are probably worth a little more $.

    In general, though, I have to say I prefer working with SQL Server, perhaps because that's where I started.

  • Does Oracle really not have temporary tables, or is it just non-performant to use them??

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Andrew Whettam (1/6/2008)


    Jeff, you have made some good points, but pardon me for thinking it comes across as just a teensy bit anti Oracle!

    Absolutely incorrect, Andrew... I'm a LOT anti-Oracle! 😀

    It is worth noting that the default case sensitivity behaviour can be altered in both SQL Server and Oracle (Oracle can be made non case sensitive and vise versa with SQL Server) simply by changing the collation. Also object name capitalisation can be turned off in Oracle.

    I knew the case sensitivity for strings could be overridden by a setting, but how is the object name capitalisation overridden? The reason I'm asking is because, apparently, I've got some DBA's that think it can't be done and I'd like to stick this one in their left nostril 😉

    Something that is not insignificant for the DBA - Oracle skills are probably worth a little more $.

    I'll agree there... it's probably because they have to come up with a standard naming convention that fit's in 30 characters 😛

    In general, though, I have to say I prefer working with SQL Server, perhaps because that's where I started.

    Heh... or maybe it's just easier to get things done quickly without paying the big bucks for an Oracle DBA :hehe:

    Anyway, I appreciate your points and thanks for the feedback... The question did give me a chance to vent on the RDBMS that I hate to work with...

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • TheSQLGuru (1/6/2008)


    Does Oracle really not have temporary tables, or is it just non-performant to use them??

    Oracle does have "temp tables" but even though different sessions cannot see the data from other sessions, the schema of said temp tables is global in nature. Again, there's that little problem with mixing DDL in Oracle, as well.

    The recommended method is to create a table in the schema of the login running the job... but that also means that the same job cannot run twice at the same time unless you number the table somehow... basically, you have to provide the naming that SQL Server does automatically.

    I haven't had cause to use it, yet, but supposedly, there's an "array" data type that works kinda like a table variable... we'll see..

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Nice rant, Jeff.

    Personally I think SQL Server is better because it's cheaper, easier to use, and more friendly to the user. But I'm a SQL Server guy and always have been.

    They're both good products and can fit almost anywhere. Oracle and SQL Server have fundamentally different ways to write code against them (see some of Jeff's points), so you almost do need to pick one and go with it. Simpler for staff if they stick with one approach.

Viewing 15 posts - 1 through 15 (of 55 total)

You must be logged in to reply to this topic. Login to reply