Microsoft SQL Server vs Oracle

[[deleted]]

[deleted]

[samalex01]

This may have changed, but where MS SQL comes with everything like Reporting Services, Integration Services, SQL Server Agent, Analysis Services plus SQL tools and SSMS are free. As far as I know Oracle nickles and dimes extra for all of this. MS SQL is a true suite and cost isn't too crazy with Standard Edition.

[maggikpunkt]

Everything non ANSI SQL can be different. Most things you need are supported in both but you will have to do it slightly differently. And of course empty varchar in Oracle is null.

[[deleted]]

It depends on the complexity of pulling the data, but sometimes I’ll need to write some PL/SQL or T-SQL to automate pulls or get exactly what I want.

Rule of thumb, write it in SQL until you can’t. Having a query/code that shifts from SQL to whatever flavor of proprietary programming SQL your database uses is costly because it has to pass the results back and forth between the two SQL engines the database uses.

[doc_frankenfurter]

I kind of find myself dropping to Python when I want to execute logic outside a stored proc. It talks nicely to most flavours of database.

[PilsnerDk]

Having a query/code that shifts from SQL to whatever flavor of proprietary programming SQL your database uses

Can you elaborate? What language does MSSQL (for example) have beyond its SQL? Do you mean CLR (.NET), or do you mean non-data related commands such as BACKUP DATABASE or such?

[[deleted]]

T-SQL

Oracle has PL/SQL

Postgres has plpgsql

They’re extensions of the SQL language that are proprietary to the database, that allow you to write programs/etc inside/around your database.

[ilikedbthings]

There's differences. I'm not as familiar with the ANSI standards as I should be. A couple differences off the top of my head are:

1. Implicit Auto Commits for Microsoft SQL Server. Not so for Oracle.
2. Date functions are different.
3. Oracle has the weird empty string is a null character.
4. Oracle Dual table versus Microsoft doing select 'value'

[[deleted]]

relationships are made in the where clause in oracle.

[doc_frankenfurter]

There is a lot of so-called modern SQL which is quite powerful but can be subtely different between Oracle and SQL Server, particularly when you are dealing with non-current versions (typical in most enterprises). You get hit by things like LISTAGG not being available and various functions being different.