Oracle Vs SQL Server

thatjeffsmith Expired 40 Comments

Tell Others About This Story:

Disclaimer and Update: I now work FOR Oracle. So keep that in mind when you read these posts. Of course I wrote them BEFORE I started at Oracle, but I still figure you ought to know that…does that mean I think that Oracle wins the debate now? I bet my career and family’s financial well being on Oracle 🙂

I’ve worked with Oracle since 1999. Oracle’s database technology debuted in 1979, years before Microsoft OEM’d Sybase’ database. You’re just waiting to here me say ‘Yes, Oracle is better!’ Well, I’m not going to say that. Both database platforms are legitimate solutions, and it would be hard to find a clear ‘winner’ without some form of bias entering the equation. I hope that you spend a lot of time developing your core requirements before you decide which way to go. In the meantime, I’d like to share some of the things I find ‘cool’ about both platforms.

Update: Read Part II where I talk about equivalent terms, tech, and community players
Update: Read PT III where I talk about monitoring indexes

Why Oracle Wins

Oracle goes first because I’ve been using it longer and it happens to come first in the alphabet.

  • It runs on just about any OS you you can think of, including Windows and Mac OSX
  • It was the first successful commercial RDBMS, they have helped set the standard.
  • Yeah, it does that. Pretty much anything you can think of is supported by the engine.
  • You can create, populate, manage, query, backup, & destroy a database all on the command-line. No tools necessary.
  • You can run it for free. If you consider it’s supported on Linux, then it gets even cheaper. Just find some decent hardware, and you’re good to go.
  • You can control it over the web, if that’s your kind of thing.
  • Flexibility. You want to load data? Ok, we’ll you’ve got at 6 ways to do that based on your needs.
  • Tools. You know I was going there. But what I mean here is choice of tools. At one point I identified over 100 different IDEs available for working with the Oracle database. You should be able to find one you like!
  • It’s paid my rent/mortgage for more than 12 years. There’s no shortage of jobs that require Oracle expertise. If you know Oracle, you should be able to find a job. I don’t see this changing anytime soon.
  • Oracle jobs generally come with a very good salary.
  • Why SQL Server is EVERYWHERE

    Note that everywhere doesn’t include Macs and Unix/Linux. Listed second, but doesn’t come in 2nd place in very many aspects when compared to Oracle.

  • Easy to install. REALLY easy to install. Maybe TOO easy if you know what I mean.
  • You can ROLLBACK a TRUNCATE. Yes! If you do so inside an explicit BEGIN TRANSACTION
  • Sample databases. The AdventureWorks wizard gives you 5 very cool sample databases out of the box. Great for learning.
  • No DUAL table. This means I can just write SELECT function, no need for a FROM clause. Simple, but I like it.
  • #SQLHELP on Twitter. The number of people chipping in to help here is just amazing.
  • Keeps Oracle on its toes. Competition is always good. And let’s face it, DB2 and Sybase aren’t really pushing Oracle to do much. SQL Server is. BIG TIME. The customers win out on both sides when there’s good competition.
  • They make good tools. Not perfect ones, but really good ones.
  • Most SQL runs great out of the box. Tuning happens, but it’s not the headache it is other places.
  • The development platform plugs right into database (Visual Studio). Change management is pretty straightforward.
  • The experts have a great sense of humor, are approachable, and are fun. You know who you are.
  • Who Wins?
    We all do. You can’t really go wrong choosing either platform to build a career or application on. I have seen both databases perform extremely well in the industry standard benchmarks, but more importantly, I’ve seen both running in the real world by real customers for real applications.

    Shameless Plug for T-SQL to Oracle SQL Translator

    If you’re here I’m guessing you did a search on Google for Oracle vs SQL or similar. That tells me you’re trying to make a platform decision, win a bar bet, or trying to figure out how to do something in Oracle like you already know how to do in SQL Server. If you’re in the latter camp, check out our SQL translator. It’s built right into our default IDE, aka SQL Developer.

    So what did I leave out?
    I’d love to hear why you love Oracle or SQL Server or both! And that goes double for people who have actually worked with both platforms.

    Related Posts Plugin for WordPress, Blogger...
    Tell Others About This Story:

    Comments 40

      1. thatjeffsmith Post
        Author
    1. Nice. I have worked with Sybase,SQL server as well as Oracle. SQL server 2008 R2 has come along way and is quite comparable to the Oracle. However, Microsoft’s main TRP is its tool and SSMS makes life easy especially the graphical plan.

      1. thatjeffsmith Post
        Author
    2. Hi Jeff,
      I am in the middle of a consultancy startup with as goal overall host system load reduction by tuning relevant bad behavior SQL. It will be no surprise to you that I use sys views like V$SQLAREA, V$SQL etc. for this, once you have been with an experienced DBA and basic statistical insights about orders of magnitude in numbers, anyone can do this.
      My question: how querywise do I do anything of the same dimension (overall historical buffer information with system load characteristics per SQL historically run on the system) in SQL Server? I would like to help those folks as well cleaning up the most horrible statements where usage gets exponential by sloppy code writing.
      My experience is that in a high class Oracle system house even each release some bloopers slip through and can be analysed and dismantled just like that using the information I indicated above. The resulting host system load reduction is substantial, freeing up high speed memory for real queries in stead of thrashing through bad written ones. Hope there is an equivalent for SQL Server (all versions?)

      1. thatjeffsmith Post
        Author

        It can be done and yes SQL has its own set of performance metrics and cached SQL statements. I would check out Paul Randall and Brent Ozar’s blogs for some great posts on the subject.

    3. Has anyone compared table partitioning in SQL2008R2 and Oracle?
      Possible for SQL2008 standalone server with RAID5 implement table partitioning in different file groups (and if hardware cost allowed, sitting in different disk array) helps to handle table with 300million rows to meet the performance for OLTP and Reporting?

    4. Also, don’t count out PostgreSQL either. You’ll get a lot of enterprise level features while being open source. If you don’t need support, you can have all of those features at no cost.

      1. JeffS Post
        Author

        I haven’t forgotten PostgreSQL or any of the other platforms, it is just that the topic of what is better between these 2 platforms is more popular, or at least in my circles it is.

    5. nice site, JS. Impressed with the general vibe of the place — not very catty like the messageboards I spend my days prowling trying to make Access do what they want me to make it do. I came here looking for ideas on whether to push them to buy me SQL SMS (which I took a class in) or something roughly equivalent for the Oracle tables that the current Access “solution” is attached to.

      Plus Erin’s picture was a nice touch.

      Anyway…oh! that’s why I looked for the comment window! I can’t *believe* those salaries! WTF? How can you guys make a living on that?! Don’t let ’em use you like that. Maybe you should organize. Imagine what their lives would be like without your skills!

      1. JeffS Post
        Author

        >>not very catty like the messageboards
        I think that’s about the highest form of praise I’ve ever received. I can’t stand ‘those’ message boards. What a turn-off!

        Check out our Application Express (APEX) solution. It’s free with the database and I think you could quickly build out a web version of your existing Access solution. Plus our SQL Developer tool will step you through migrating your data over as well.

        As long as you get out of Access, I’ll pretend to be happy 😉

        As far as salaries go, remember those are averages!

    6. Hi Jeffs. Am SQL Server database designer. I have been working with SQL server for 6 years now. But I dont know if in Oracle you can have Linked Server. Cause this help me a lot for Data Synchronization. Please try to tell me because I want to start using oracle ASAP.

      1. JeffS Post
        Author

        Pretty much, you want a db_link. Homogeneous (Oracle – Oracle) is easy. Heterogeneous (Oracle – SQL|DB2|etc) has an additional cost. Amyways, do a Google on db_link against the Oracle Docs.

        You can look up Oracle Transparent Gateway – that will let you query SQL Server directly with your Oracle connection, via DB_LINK. Here’s some technical info for how it is setup for SQL Server
        http://docs.oracle.com/html/B10544_01/toc.htm

      2. Oracle on Windows can use ODBC connections to link to access about any database. It’s no additional cost (no cost if you’re using Oracle Express). Oracle calls it “Heterogeneous Services” (HS) and I’ve been using it for years to pull/push data with SQL Server, DB2, Informix, Access, Excel, etc. Tim Hall’s got a great little example here:
        http://www.oracle-base.com/articles/9i/HSGenericConnectivity9i.php

        The Gateways are extra powerful, extra cost options. HS is already included and does almost everything I need!

    7. Hi Jeffs, we are a professional IT shop i.e. we will be taking care of what we run regardless of the solution we choose and didn’t mean to imply anything less. Our two options for many reasons are Oracle and MS SQL so whilst there are other cheaper alternatives we won’t be going there for some time.

      Apologies for the light touch on information but this was my first posting and wasn’t sure how much information would be required. Cloud is not an option for us due to the culture and security concerns of the organisation. Whilst this is not my opinion it is what it is and fighting that at present won’t help anything.

      From a hardware perspective we have a Sun Sparc M9000 (but this is massively over utilised) and lots and lots of x86 blade servers, mainly running Windows server 2008 R2. So to narrow this down further the comparison needs to be between Oracle and MS SQL on Windows using comparable editions of the DB suite.

      1. JeffS Post
        Author

        In that case, I think the only real way to make the decision is to test-drive both and test the technology to your requirements checklist. Does it come down to price only? If so, be sure to compare apples to apples as closely as possible.

    8. OK folks to those who want to distill this back down to a few big ticket key items i.e. me! Here is where we are coming from
      We aren’t going to need monster performance or the best technical solution in the world as we are not that big (2,500 staff tops).
      We are looking to have something that does the job to at least 80% and where that doesn’t we will use the other DB product to do the work.

      We are a typical IT shop i.e. even spread of SQL and Oracle, lots of MS stuff (office, sharepoint, project server etc etc).

      So rather than getting very detailed about where you can run your logic (not to say its not important) for an organisation that wants to just run something that is cost effective, easy to maintain and doesn’t need lots of bespoke work or care and attention which would you good folk recommend?

      1. JeffS Post
        Author

        No matter what you pick, care and maintenance will still be there. Databases will always need attention for backups, performance problems, and security. If you have lots of ‘MS stuff’, then why not just spin up another instance of SQL Server? If you are really cost conscious, then you could also just go with MySQL and get the 80% you ask for. I’d really hate to make a recommendation based on the limited info you provided though. You could as easily make a case for running Oracle Standard Edition.

        Please just don’t go with something under the assumption it won’t need care or attention. Your data is important regardless of where it’s stored.

      2. JeffS Post
        Author
    9. To JeffS in regards to SSMS not holding a candle to Toad; I say, first, you have to go get and buy Toad, where SMSS comes with SQL-Server. Let alone, one has to go “get-it” cause it does not come with the product.

      As for T-SQL being “pretty limited”? Huh What? T-SQL allows not only application logic to “execute” inside the databse, but it allows one to execute T-SQL and application logic to other development platforms, including compiled Extended Stored Procedures, .NET/CLR applications, and even run T-SQL/App-Logic to other databases, including Oracle. The .NET/CLR works with any .NET app, including Mono .NET apps. But the real power of being able to run Extended Stored Procedures is amazing. T-SQL can also interface with web services.

      The biggest problem with Oracle is the installation and maintenance. For over the 10 years where I have worked with Oracle and SQL-Server, it never amazes me how much overhead Oracle has in cost. I did have the wonderful pleasure of seeing Oracle when it first came out with the web interface piece, and that was a great step in the right directions. But Oracle to this day, does not have an equivalent to SQL-Server’s Management Studio with such tools as the Profiler and T-SQL editor. And sorry, Toad does not hold a candle light to SQL Server’s T-SQL/Query-Analyzer tools, by a long shot, in my well seasoned experience of using Toad and SSMS.

      1. JeffS Post
        Author

        Yes, you have to buy Toad, no way around that! One has to go get anything in this world if one wants it, even SSMS. I’m assuming every machine doesn’t have SQL Server installed on it 🙂

        I was comparing T-SQL to PL/SQL. I have seen some very cool stored procs and functions and I would guess one could accomplish just about thing one wanted to do with it.

        Oracle’s SQL Developer is on par if not better than the T-SQL editor that comes in SSMS based on what I have seen, esp if you consider that it provides equal functionality for multiple versions of Oracle, whereas SSMS only gives you certain features based on your version of SQL Server. I’ll agree with you that Oracle doesn’t have a good answer for the Profiler, but the 3rd party vendors have responded with a vengeance and no Oracle DBA has a good excuse for not being able to do deep diagnosis of performance issues.

        I would be happy to give you a tutorial on Toad so can see what I’m talking about. 13+ years of development and 3,000,000 or so users are hard to deny, but I welcome the challenge!

        J – thanks so much for the passionate response and taking the time to share your experience with us. One thing I’ve found is that folks are passionate about their choice or RDBMS. It’s rare to find someone to likes Oracle & SQL Server – maybe I am the only one?

    10. After 10 years as an Oracle developer, I recently got a job in the SQL Server Universe. I also see pros and cons for each, but frankly, SQL Server seems so much more advanced.

      One tool–SQL Server Management Studio–replaces multiple pieces of software required by Oracle. You can do development, monitor processes, and access administrative tools all in SQL Server Management Studio with a few clicks. The code editor alone beats Oracle front ends Toad, SQL Developer, PL/SQL Developer, or SQL Navigator (in my humble opinion).

      SQL Server makes a lot of thing easier. For example, you can write SQL in a stored procedure. You don’t have to put SQL into variables and execute with EXECUTE IMMEDIATE like in Oracle (though you can do that with sp_executesql if you actually want to.) I would think being able to use SQL in code would be a basic right for database developers. Other features that make life easier include very simple ways to create temorary tables in memory, and Common Table Expressions (CTE’s) for simplifying complex queries.

      The sad part is that SQL Server can put a lot of DBA’s out of business. Years of gaining skill making Oracle work become useless. In other words, database administration in SQL Server is much simpler.

      And, of course, all the additional integration, reporting and analytical tools that you get with SQL Server are not only free but higher in quality than many of the ETL and BI tools for Oracle (again, in my humble opinion). Much better. Try them.

      In favor of Oracle, they have more sophisticated partitioning including multiple partition types (range, list, hash) and subpartitioning. SQL Server is limited to range partitioning and no subpartitioning. However, SQL Server is less dependent on partitioning for performance. It seems to scale to really big tables without a lot of fuss.

      1. JeffS Post
        Author

        I see why you enjoy Management Studio (SSMS), but it’s code editor doesn’t hold a candle to Toad’s – although my opinion is anything but humble 🙂

        T-SQL seems to be pretty limited to what PL/SQL offers you. Oracle allows for all of the application logic to execute inside the database, I’m not sure if that’s as popular in the SQL Server world. PL/SQL is based on SQL – hence the /SQL part of it, so I’m not sure what you are limited by in terms of running queries in your PL/SQL.

        I think the ease of deployment and management of an out-of-the-box install of SQL Server would give most mangers pause before dedicating resources to having a full-time DBA. However if you are running a business critical application on SQL Server and you don’t have one to manage performance, backups, and the day-to-day fires, it would be very short-sighted to not invest in that DBA.

        I have no experience with SSIS or OBIEE, so I don’t have a dog in that fight. But I would love to have @sqlchicken and @oraclenerd duke it out!

        From a developer’s perspective, I get what you’re saying. But, Oracle has a decade head-start, and MSFT is still playing catchup in a lot of places – especially scalability, e.g. Oracle RAC.

        Randy, thanks so much for taking the time to reply, I’m excited to see you so excited to get into SQL Server. A lot of the Oracle folks I work with refuse to even consider working with SQL based on misconceptions and FUD from the Oracle domain. Good luck! And I’ll recommend a great resource for taking it to the next level – SQLServerPedia.com

    11. Pingback: Oracle vs SQL Server, Take II | 140,000 Characters or Less

    12. Ok, good points about tracing in Oracle…but…are the third party tools (that make tracing and reading traces easier) free? Profiler is free, and there can be overhead, but you can do a lot natively to capture the information you need.

      I actually went to a day-long seminar where Cary Millsap spoke, but I was new to Oracle and most of it was over my head. And I also know of Tom Kyte, but just from reading his posts on askTom.com, I don’t get the impression he’s that approachable. The welcoming attitude of the SQL Community is something I do not see in the Oracle Community…yet. You, my friend, are an exception and I love that. But I haven’t found many like you. I hope we get to meet in person some day, there is much to discuss! 🙂

      1. JeffS Post
        Author

        Remind me again, you work for a software vendor, right? Just making sure you understand that we’re in this to make $ 🙂 See my point about Oracle paying my mortgage, and my point about the SQL tools being much better.

        I have seen free tools out there to read trace files, but they were one-off’s by developers. You can also get Analyze Trace from Oracle, here’s a blog about it from Eddie Awad, http://awads.net/wp/2006/07/18/oracle-trace-analyzer-is-tkprof-on-steroids/

        The SQL Dev plug-in is $50. Toad is a little more, but of course it does more than just analyze trace files.

        The approachability of the SQL Server folks is beyond reproach! There are several good Oracle folks out there, I find more and more every day on Twitter. Eddie is a great resource too! @eddieawad

    13. One thing I didn’t see mention of is performance tuning and tracing user activity. While I haven’t worked with SQL Server as long as most, I feel I have a good understanding of how its optimizer works, and what I can do to affect the decisions it makes. I have less than 50% of the same level of understanding for Oracle, and I’m not sure why (not smart enough, haven’t spent enough time, not really interested???).
      I also struggle with capturing user activity in Oracle. I could be wrong, but I believe you can only trace one user, or all users, and the load introduced by the trace is pretty substantial. With SQL Server, Profiler is a cinch to use and is very powerful in terms of filtering. And then you have free tools like Clear Trace to easily analyze the data. You don’t have to run it through tkprof, wade through text files, etc. I know Statspack/AWR provide great snapshots, but I always wonder what I’m missing…

      Erin

      1. JeffS Post
        Author

        I see capturing user data as a toss-up I guess. I’ve used both Oracle Trace and SQL Server Profiler to capture activity. Working with the data does seem easier in SQL Server, esp if you limit the discussion to the out-of-the-box toolset. tkProf is a mess – and was never intended to be make available to non-Oracle employees.

        3rd party tools like Toad/Spotlight make reading trace files a breeze. You’d also have to consider Cary Milsap’s Method-R plug-in for SQL Developer. The overhead is what it is. You can trace at the session, user, or database level. The more you trace, the more overhead you’ll incur. But if you need the info, you NEED the info and you’ll pay the price.

        AWR takes snapshots, and only runs every X time intervals and only captures the top X% of activity. If you want to grab EVERYTHING and pay no performance price, Quest has a wicked neat tool called Performance Analysis (available for both Oracle and SQL Server), but that’s a different story 🙂

        I think if you were to follow the right folks in the community and put some time into it, you’d come up to speed very quickly on the Oracle side too. It’s not magic, mostly 🙂 Cary Milsap, Tom Kyte, and the folks behind http://blogs.oracle.com/optimizer/ are my go-to resources. Consider Cary and Tom the Buck Woody and Paul Randal/Kim Tripp in the Oracle world.

        Thanks so much for taking the time to read through my laundry list and sharing your comment – I think you get the award for longest response on my blog so far!

    14. Great stuff Jeff, really enjoyed reading this. I too am intrigued by Steve Jones’ comment about SQL Server not scaling. I suspect I know what he means but hoping he comes here to clarify.

      Is it fair to say that Powershell and/or SQLCMD offer command-line abilities similar to that of Oracle’s? Having not used Oracle’s command-line tools I wouldn’t know.

      1. JeffS Post
        Author

        PowerShell is an interesting animal. It seems to be a mix of your classic UNIX scripting language (KSH, CSH, SH), and an interface to a lot of MSFT program APIs. I know some MSFT products don’t surface features anywhere BUT PowerShell – and that’s kinda freaky.

        But old school Oracle folks will do everything via scripts and cmd-line tools like SQL*Plus and VI/Emacs and Unix/Vax/Windows prompts.

    15. Good list. I think you left out a few key items. Oracle scales out, SQL doesn’t (yet). SQL has lots of tools in the box that Oracle doesn’t, which means there’s a big cost different. Or convenience difference since many companies don’t seem to want to spend too much on other tools.

      Not sure about Oracle support. I know it’s there and $$$, but haven’t used it. MS has great support.

      Is there no Oracle community support like #sqlhelp or SQLServerCentral?

      1. JeffS Post
        Author

        Is this the real Steve Jones, and did you just say that SQL doesn’t scale out? If so, please elaborate. I’ve seen SQL scale to fit some pretty demanding loads. Unless you mean there’s no answer to Oracle’s RAC technology…

        As for support, I’ve used Oracle’s support before. Back in the day, when you could call and they had these things called TARs. I would wait till about 2 in the morning, b/c my ‘friend’ in the UK would be there to help me.

        These days I usually don’t hear many good things about their support, but I haven’t had to deal with them since about 2002.

      2. JeffS Post
        Author

        On the community, i’m trying to push #ORACLEHELP, but have only got 2 or 3 folks helping me get the word out.

        There are dozens of web communities for Oracle. I’m working on OraDBPedia, which is a sister site to SQLServerPedia – you may have heard about that one 🙂 There’s also OraFAQ, AskTom – Tom Kyte’s doman, and several others. I don’t see the fanaticism on Twitter for Oracle like I see for SQL – YET.

    16. JeffS Post
      Author

      You can run it for free, but there’s no free version of Windows, so technically, you can’t run it for free.

      I know, I’m being difficult 🙂 I didn’t say this explicitly in the blog, b/c I didn’t want to get sued, but I like SQL Server because it’s so much CHEAPER than Oracle. The software, hardware, tools, people, etc.

      1. Jeff,

        You can run SQL Server for free. SQL 2008 express can be installed[1] on windows 2008 hyper-v core. This is a free[2], albet unsupported option. Time also marches on…when sql 2012 is released it running on Hyper-V core is a supported configuration[3].

        [1] http://www.nullsession.com/2009/06/02/sql-server-2008-on-server-core-2008-r2/
        [2] http://arstechnica.com/microsoft/news/2009/08/microsoft-hyper-v-server-2008-r2-arrives-for-free.ars
        [3] http://msdn.microsoft.com/en-us/library/hh231669%28v=sql.110%29.aspx#bk_configurationfiles

        1. JeffS Post
          Author
    17. Pingback: Tweets that mention Oracle Vs SQL Server | 140,000 Characters or Less -- Topsy.com

    Leave a Reply

    Your email address will not be published. Required fields are marked *