Math is math is math
I haven’t found any tremendous differences working with numbers between Oracle and SQL Server. Both offer pretty standard functions to calculate your calculations. Oracle has MOD(), SQL Server uses %. This is likely something you’ll want to be aware of if you’re working cross-platform, but nothing earth shattering.
One really interesting thing I’ve found with Oracle is that it only has one data type for numbers when you create a table. SQL Server has tinyint, int, bigint, decimal, numeric, float, and money (does REAL count?).
Oracle, well, it just uses NUMBER. The difference here is that you have the option to specify precision and scale. You can only do that in SQL Server for types more precise than integers.
One area where Oracle has a leg up on SQL Server is when it comes to concatenating numbers and strings together. It will implicitly convert numbers to strings, where SQL Server will just throw awful red text at you. Wah, error converting data types. At minimum, it makes your code way more clean. How many times have you written some dynamic-ish SQL only to have to CAST/CONVERT a bunch of values to strings? Probably a million. Maybe more. This also goes for dates.
I mean, you can try to use CONCAT or FORMAT in SQL Server.
If you hate your server.
And performance.
And baby animals with big eyes.
Don’t you lose my number
Working with numbers is usually one of the easier things to do in databases. Just match your data types and you’re good to go. As long as you’re not covering up years of “accounting irregularities” it should go pretty smoothly.
One major area of difference is, as I mentioned, that Oracle will do you a solid and convert numbers to strings when you’re concatenating. I think this in large part because Oracle differentiates the concatenation operator (||) from the addition operator (+). We’ll look more closely at that next time, because there’s a bit more to say about working with strings in general.
Thanks for reading!
Brent says: wow, only one datatype for numbers sounds awesome, actually. The simpler, the better there.
11 Comments. Leave new
Thank you Erik! I am SQL Server DBA working in an Oracle shop so these blog entries are very helpful.
Always glad to help! Let me know if there’s anything specific you’d like a post on, and I’ll try to address it.
In a similar boat as EJ. Our shop is migrating to Oracle. As a SQL Server DBA, would love to see “Getting Started With Backup and Recovery”. Food for thought, if there are similarities, maybe someone could port Ola’s solution to the Oracle world. Hhmmmm…..
Mark – sure, check this one out: https://www.brentozar.com/archive/2014/07/backing-oracle-database/
Just read the post and it helps a lot. Many thanks!
I’ve really enjoyed these posts on Oracle. Worked with it for 10 years before a job change brought me to SQL Server. Sure makes me homesick. There are lots of things to appreciate about SQL Server but I think Oracle got some things right a long time ago that SQL Server is just beginning to catch up with.
For sure. What was the hardest thing getting used to jumping over the fence to SQL Server?
Just what you’ve been covering. Date math and string manipulation! And, at the time, no windowing functions in SQL Server.
I agree wholeheartedly, Jeff!
Windowing functions were a revelation, and having to use SQL Server back before they were introduced was like trying to dig a ditch with your bare hands…
Good analogy!
I’m a huge fan of both the specific concatenation operator. I’m in the middle of a huge Oracle -> SQL Server data migration and it’s SO much easier to do the text conversions on the Oracle side. Thousands and thousands of characters less in the scripts just because–as you said–you don’t have to CAST every. single. number.
Oracle has it’s weirdness, but I certainly think that the SQL Server team should consider using the double pipes for concatenation with automatic string conversion…