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 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.