BUT FRANCE HAS A PONY
I really like SQL Server. Most of the time. Okay, so most of the time I like SQL Server most of the time. Don’t get me wrong, if I had to go back through the career-time continuum and pick a RDBMS to work with, I’d probably still choose it over Oracle. Probably. And, because I don’t exclusively grow facial hair from my neck, I wouldn’t be allowed to choose PostgreSQL. They’d kick me off the mailing list.
Just kidding. You’re all handsome rogues. We could have had a nice life together, staring longingly into each other’s shoes and trying to implement parallelism.
I’d have DB2 here, but the cost of entry to the Developer Edition is rather steep. So, you know, I’m sure it’s great! But no. Though I would be really happy if Microsoft implemented ANSI Standard constructs into T-SQL half as fast as IBM does.
I have poked at Oracle and PostgreSQL a bit, and found they have some really cool stuff. Heresy, right?
Check out some of these Oracle gadgets and tell me they wouldn’t make your life a whole lot easier.
In no particular order:
Table restores! Built in! I’m very surprised we never got a feature like this. You can do it with a 3rd party tool like Dell LiteSpeed.
Adaptive Plans! Go to the link and read the second paragraph. Read it twice. Wipe the drool off your face.
UPDATE: Geoff Patterson has created a Connect item to get Adaptive Plans for SQL Server.
In-Database Row Archiving! You know all that stuff you do with partitions that Oracle already does better? Where you’re basically praying for partition elimination to not undo the two weeks of work you put in to setting up this partitioned table that developers are writing horrible MERGE upserts to? Yeah. You can just tell the engine to not pay attention to rows you don’t care about anymore when it accesses the index. Fancy that.
Bitmap Indexes! It’s kind of like a filtered index, except for all values of a highly non-selective column.
Materializing CTEs! Even though it’s undocumented, we use plenty of undocumented stuff in SQL Server to get the job done. This is really cool to me, since I’ve discussed this limitation in CTEs before. I’d love to see a way to do this in SQL with the same behavior; not having to create temp tables. It would be a nice way to get around issues with caching statistics for temp tables, and especially since MS is still fixing bugs around temp tables.
Are there more? Yeah, but this is a blog. Go grab a VirtualBox and read the documentation if you’re interested in learning more.
55 Comments. Leave new
I have no problem with your list, but maybe we should start with the basics first, before asking for the sexy stuff:
— Let’s get error handling working properly. Oracle error handling just works, every time, no exceptions. SQL Server…not so much so. Example…”Could not create constraint, see previous errors.” Ugh.
— transaction handling. Why do simple errors cause xact_state() to go -1, obviating any possibility of savepoints actually working. Why no autonomous transactions? Implicit transactions are horrible. Too many issues to list. The other vendors don’t really have these issues around transaction management.
— How about logical database dumps like mysql, postgres, and oracle have. One command to dump the whole database, with the rows, to rerunnable sql. Yeah, I know you can do this with powershell…it shouldn’t be that difficult though. Simple commands that are portable and easy to use. mysqldump is so easy to use and people wrote extensions that take those files and can convert them to mssqlserver syntax and really cool stuff like that.
— A real performance_schema. Yes, DMVs are great and we all use tooling like sp_whoisactive. But then we all have to place wrappers around those things to log historical data and dial them up and down to suit needs. All of this is custom and lacks portability. It would be nice to say, “I’m having a perf problem and I see the following wait event in performance_schema.CallsByDuration”.
— how about “CREATE OR REPLACE object AS ” syntax? Why are we still coding IF EXISTS clauses everywhere?
All good suggestions.
One thing in DB2 that was exceptionally helpful was CREATE TABLE x LIKE y which would create an empty table with the same structure without having to extract DDL.
The biggest issue I had when coming across from DB2 to SQL Server was the complete and utter lack of semi-decent error messages and codes to go with them. Of course this was SQL 2000 before they did all the work of SS2005 but it could still be better.
You can come close with SELECT … INTO … WHERE 1 = 0, but you’re not going to get constraints, defaults, etc. Anything more than a basic copy and you’ll have to script it out via SSMS or a utility.
Ughugh. I think I just choked on my tongue when I was reading the link on partitioning in Oracle. Options for how you partition a table?! Teh deuce? That’s pretty sweet – the composite partitioning is particularly cute. Nice list =)
Partitioning in Oracle? Dust-off the Corporate Credit Card, it’s $11.5K per processor!
http://www.oracle.com/us/corporate/pricing/technology-price-list-070617.pdf
If I had to pick one Oracle feature, it’d be Flashback, but as that’d require a complete rearchitecting of MSSQL, I’m not holding my breath.
LoL @ those prices. I don’t need composite partitioning that badly. Makes you wonder how anyone can balk at SQL enterprise licensing… 0.o
Oh contraire mon ami
I wrote a very similar article to this one a while back saying that the only feature I still wanted from Oracle was Flashback:
https://rule30.wordpress.com/2014/04/23/the-only-oracle-feature-i-actually-still-want/
Turns out SQL Server Temporal Tables is a start in that direction:
https://rule30.wordpress.com/2015/06/04/temporal-tables-coming-in-sql-server-2016/
It’s certainly not as powerful as Flashback, but it’s certainly enough to get you out of the brownstuff in certain types of scenario. CTP docs are here:
https://msdn.microsoft.com/en-gb/library/dn935015.aspx
Best regards
S
Adaptive plans. Hoowee!
That’s #1 on my list.
I mean, query store is cool and all, but…
+1
Having been an Oracle DBA, I would have to say: recycle bin, flashback, and I like being able to simply say commit or rollback.
My vote is for hash partitioning.
I much prefer Oracle’s approach to include HINTS in a comment:
SELECT /*+ FULL(tbl) */ COL1, COL2, …
FROM large_table tbl;
This makes it easier to inject the hints across multiple platforms. I can take an Oracle query with hint and run it in SQL Server. I cannot take a SQL Server query that includes the OPTION and run it in any other database platform.
It would be nice if SQL Server also support putting hints in the comment.
@Dave – don’t even get me started on SQL Server error handling. A personal favorite are the links they include to help that don’t work.
As for the rest….I don’t suppose that Microsoft would consider a CU handling at least some of these wishlist items as a Christmas present to its customer base? We’ve all been really good this year…
How about parameterizing CTE’s.
WITH SA_PARTMAJ
AS (SELECT PO.PART AS PART,
PO.PPCTYPEB AS PPCTYPEX
FROM SA_POPART PO)
SELECT PART,
PPCTYPEX
FROM SA_PARTMAJ
WHERE (( PART = ‘5P4516’ ))
Yes, this is a extra stupid use of CTE but a developer read about CTE and thought it would be cool to implement. Unfortunately, SQL Server doesn’t recognize simple CTE and apply parameterization to it so my plan cache is polluted with millions of these SQLs.
I, too, would not trade SQL Server for Oracle, but sometimes it’s the little things that let you know your database loves you. How about these sweet little nothings:
* describe – quickly lists table columns and data types
* truncate(,) – many uses, but ever get tired of removing the time portion of a datetime in SQL Server?
* anchored declarations with %TYPE and %ROWTYPE – makes it easy to create a variable or a row structure of the right type, and you might not have to update your code if the type changes; awesome for fetching a cursor row with lots of columns
* packages – a nice step in the direction of object orientation, since you can do things like encapsulation and methods
I forgot about %TYPE and %ROWTYPE. That’s useful for lots of things other than cursors too…creating tables as copies of other structures without using “WHERE 1=0”.
I too like packages but one area where Oracle absolutely SUCKS is the ability to return result sets from procedures without having the whole Package scaffolding. That is soooooo cumbersome.
One Oracle feature in relatively recent versions that I wish for SQL Server is their extension to foreign keys, so I can say if Column1 = 1, then enforce an FK between Column2 and Table2.Column1, but if Column1 = 2, then enforce an FK between Column2 and Table3.Column1.
But in terms of something I’d use every day, yeah, “CREATE OR REPLACE object AS”.
I’ve never used that oracle feature.
But you made me remember my all-time favorite Oracle feature, that, unfortunately, they botched…NATURAL JOIN.
The ANSI standard says NATURAL JOIN is an INNER JOIN where the ON clause is inferred when columns are identically named in both tables. That is extremely dangerous.
I think the way it should be implemented is that NATURAL JOIN infers the ON clause by using the FK DRI. This is much safer, faster, easier to read, and avoids fat fingering ON clauses that become a nightmare to debug later.
But it seems NO ONE agrees with me on any of that. Sooooooo much nicer when you start using it though.
I agree that using FKs is much better than matching names, but natural join is already defined liked that and implementing something different with the same name is confusing. In the mean time I’m using ApexSQL Complete to autogenerate the on clauses.
You forgot Oracle Packages. I miss Oracle packages…. SQL Treeo isn’t the same. And columnstore indexes aren’t the same as bitmap indexes…
Flashback is a great feature too. And what about Ask Tom? Tom Kite’s snappy answers to stupid questions over the last 22 years or so are hilarious and informative.
To me Ask Tom is the #1 feature MS should include in the next release.
yup all PostgreSQL dbas are males 🙂
i’m just tripping… who knows, maybe they are or maybe men just think in terms of other men… kendra are you there today?
You don’t have to be a guy to grow a beard. Trust me, I’ve been to Coney Island.
U got me
I’d like to see flashback, table restores (have used bcp scripts for “backup” tables), create or replace object should be great
TRIM()
… and the annoying paper clip that I can remotely fire saying hey it looks like you are writing a SQL statement or SSRS rdl that your resident guru has already done for you over here!!
… and CREATE or REPLACE
… bring back a lightweight Query Analyser so the business stop requesting SEM
Oracle’s “table restores” are actually system + data tablespace restores to a new, temporary DB that then pull the requested table out of the temporarily-restored DB…so basically the same thing Oracle DBAs have been doing since time immemorial but with a bit more automation. The Oracle documentation makes it seem a lot more graceful than it is…
Once upon a time you could buy a developer’s edition of DB2 for $50ish. They need to strip out that year of support and get the price back down to a reasonable level.
There are the Express editions for free, but I don’t know what those are like.
I have never understood why any of these companies charge even a nickel for a developer’s edition. They should be free. In the “services” model a company makes its money on selling services. But when you are selling database software with core licensing that is only useful if a propeller-head codes something against it…then you want lots of loyal developers. I’m convinced this is why Java took off and took a big foothold away from MS. In the past I didn’t mind spending a grand on an msdn subscription because I knew there were costs in postage and cd/dvd burning and I got a nice little kit and sometimes a tshirt. Lately, I wonder why I keep renewing my subscription when the available SKUs keeps decreasing and simple things like Visio and Office aren’t available in the lesser subscriptions.
For me (and this is going to stick in the craw of many), when needing a *powerful* database engine, the only answer is DB2 on mainframe. Yes, it’s a dinosaur view, but when you need power, you don’t look to a Prius.
Things have indeed changed now and the BIG thing for the future, in my opinion, is HANA. The difference between it and an in-memory SQL Server version is that developers of code to run on HANA will be restricted to using code that works – as opposed to many (definitely not all) who create code for SQL Server that is often inefficient at best; with the hassles being loaded onto the suffering DBA to monitor and administer a system that leaves them hog-tied and being looked upon by management as not quite up to the job of maintaining a responsive system.
My first programming education experience was mainframes: Cobol, JCL, etc. People who poo poo mainframes don’t understand the tremendous power and uptime that they’re capable of. I worked with an old AS/400 running DB2 and it was amazing, and knowing that it could be sucked up in to a mainframe if more power was needed was wonderful.
I’m also a huge fan of iSeries, talk about a Timex of computing! I love receiving an email from the system saying that a battery on a controller card should be replaced at some point in the not too distant future. A friend of mine worked for IBM torturing iSeries, it was amazing what it took to break ’em or make them crash.
Yes, there’s a lot of cruft in mainframe and old Cobol code, but that’s equally true in SQL Server. It’s all a question of proper design and implementation.
Agreed 🙂
Could you provide some specific numbers to support your statements?
+1 for Packages
+1 for CREATE OR REPLACE
+1 for improved error handling
I’d also like commit/rollback (maybe just as an option) for SSMS query window like in oracle developer.
And finally, full support for custom data types, including the option to modify it (across the whole database).
Adaptive plans wouldn’t have almost the same features as the Query Store in the (future) SQL Server 2016? It’s a bummer that we have to wait until the first SP is released though!
Regards!
No, QS still requires manual intervention to get SQL to use the plan you want it to at the time. It will not, mid-execution, think to itself “maybe this wasn’t such a great idea” and pick a different plan.
SQL Server still doesn’t have a great command line to generate all of the DDL for a CREATE TABLE.
MySQL has
SHOW CREATE TABLE myTable;
and Oracle apparently has DESC, which supposedly functions similarly.
We have
sp_help myTable;
which returns tabular result sets, not an executable T-SQL script. Aaron Bertrand tried to push this 8 years ago on Connect https://connect.microsoft.com/SQLServer/feedback/details/273934, and I don’t think anything has changed, has it?
Since my devs are in constant competition for who can call a CTE the most. Materializing them would make my world a better place.
I really want flashback and RAC. Flashing back a half TB database in five minutes is awesome. Flashback queries are a life saver. Mr Developer where exactly were you at 9:45:17am yesterday morning? But true active/active clustering… I drool just thinking about it. Then I remember the cost and shudder.
I would say Automatic Storage Management (ASM) is a real wanted feature, at least for me. It really makes datafile and log files placement easier (at least in Oracle) 😉
Regards and happy weekend!
+1 for Flashback queries but when all’s said and done, I wouldn’t swap SQL Server for Oracle for all the tea in China.
SQL Server is plenty powerful enough for essentially all likely day to day scenarios and spanks Oracle when it comes to easy of deployment, development and cost
Back in the SQL Server 2000 timeframe Oracle had clear and important advantages. That lead has been eroded consistently to the point where most of the differences are largely academic
I see Oracle as having one significant advantage over SQL Server: multi-platform. Though if I needed a serious database on *nix, I’d go with DB2 over Oracle.
As one who works on multiple database managers on multiple platforms I’ve never let the platform choose my database manager. I’ve worked on Oracle on rhel and it was crap…let me rephrase, the performance and stability was crap…but that was due more to my team’s crap code. And I’ve worked on rock-solid SQL Server implementations.
It all distills down to your people. All of these TPC benchmarks and comparisons really make no difference if your people can’t code and your DBAs know nothing.
As for DB2 over Oracle on nix…I’d agree, if it was a greenfield project, if I got to pick my team, and if I never had to hear the word “budget”. Otherwise I’d let my people’s strengths dictate what we used.
I agree with what you say here.
“Budget” is the curse word that gets important aspects like “DR” or “redundancy” nixed from a project. It doesn’t get the team lunches removed though – especially in government departments.
“If your people cannot code and your DBAs know nothing…” – how true a statement is that? I’ve recently left a team of a over a dozen in government and around a third of the people in it were competent and able to actually call themselves a ‘DBA’.
If Microsoft were to add Adaptive Plans and what Dave mentioned above about the poor error handling, it would be a strong contender (in my opinion).
I have to agree with “CREATE OR REPLACE object AS”… but I really wished SQL Server had regex functions built-in as found in Oracle.
I view Oracle as having one significant advantage over SQL Server: multi-platform. Though if I needed an earnest database on *nix, I’d go with DB2 over Oracle.
Oraclebased – dumb question: do you run Oracle on Windows a lot?
hardly, i always us in Linux and Unix.
http://oracle-based.com/oracle-database-11g-release-2-installation-on-oracle-linux-5/
Okay, so then how is being multi-platform an advantage?
That’s like saying, “I only use Acme Laptops because they have a built-in pencil sharpener, but I would never actually sharpen a pencil with it.”
Is there a Connect issue floating around anywhere for adaptive query plans?
Even a very limited version of such a feature would be incredibly valuable. I’m happy to file one if there isn’t, but figured there would likely already be a request given the amount of interest. However, I haven’t been able to find one on Connect.
Not sure, and as I learned this week, searching on Connect is horrible.
I was talking to someone about how a feature like that seems like a natural progression from Live Query Plans. If SQL estimates a number of rows, and it gets way more or way less from an operator, it should be able to change downstream (or upstream, depending on which way you’re reading the execution plan) operators to better suit the number of rows it’ll be getting.
Yes, I can imagine a Connect issue for better Connect search would be highly voted upon!
I created an issue for adaptive plans, including a test script that highlights the potential benefit as well as some general commentary about usefulness and use cases. Feel free to vote or comment with additional use cases 🙂
https://connect.microsoft.com/SQLServer/feedback/details/2486931
Upvoted, and I’ll edit the post to add your link.
Oracle has SQL Server beaten, and probably always will have, on one feature: its transaction handling (redo & undo). It’s been ingenious since Oracle RDBMS’s inception. And from that has emerged Flashback technology. Fried the database with a dodgy code release? Flashback the database to before the release began – it takes almost no time to do. Wanna see what was in a table last week? Flashback query to the rescue. Then there’s Data Guard: as beautiful as a da Vinci.
On the downside: it’s terrifyingly expensive, has crappy GUIs and is considerably harder to learn (from a DBA perspective).
Richard – keep an eye on the new Accelerated Database Recovery feature in SQL Server 2019 & Azure SQL DB – might find that interesting:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-accelerated-database-recovery