3 ms·

Are you using Postgres? It seems like you haven’t discovered CTEs or lateral joins.

crazygringo 3y ago
Oh, I've worked with all the major engines. To which we can add: trying to remember which seemingly basic features are supported by which engines, and which aren't (e.g. percentiles are great in Postgres, but you're SOL in MySQL). That there's virtually nothing "standard" about SQL syntax or functionality at all, except for the simplest of queries and the general basic concepts.
- paulddraper 3y ago
  Well Java and JavaScript differ a lot too. Some similarities, but a lot of differences too.
- magicalhippo 3y ago
  We're in the process of migrating our on-premise business application from SQLAnywhere to MSSQL. Imagine the fun we're having...
  - eddd-ddde 3y ago
    I'm really curious, in the practice, what causes a product to decide "we are moving from abcSQL TO xyzSQL" ?
    - crazygringo 3y ago
      In that direction, it's usually going to be about robustness -- more tooling, less edge cases, a wider developer base, built-in cloud support, and so forth. The more popular a database is, the easier just everything is. Occasionally it's going to be some very specific aspect of performance limits, or a feature like spatial indexes that work in a special way. And occasionally it's going to be about cost or better integration with a particular cloud or other specific piece of tooling.
    - magicalhippo 3y ago
      For us there's been two major motivations, and another few lesser. First and most prominent is that Sybase was bought by SAP and SAP has pivoted SQLAnywhere from a general RMDBS to focus on mobile applications (ie more embedded). Apart from product development changing focus we've struggled to buy licenses for our customers, so the writing on the wall was clear. The seconds is that we've got some big customers who run their own MSSQL servers, and they've been pushing harder and harder for us to use their servers so they can handle administration and access control directly. A third motivation is that while we've been primarily on-prem, a lot of customers these days don't want that, so we've started offering cloud hosted options. Since we're a Windows shop Azure made sense, and as such the hosted MSSQL also makes sense and is quite convenient. Fourth is that, while MSSQL is quite anemic in terms of SQL language functionality, it does support transactional schema updates, which makes schema upgrades much less error prone. Given that MSSQL syntax support is quite limited and we now have developed tooling and library support for multiple databases, we'll probably also add support for PostgreSQL later.
stvltvs 3y ago
I agree that CTEs are a solution to some of the pain listed above, but CTEs aren't exotic. Lots of the major RDBMSs support them.
- fifilura 3y ago
  CTEs are an absolute must to get the right "do-this-then-that" approach.

Oh, I've worked with all the major engines. To which we can add: trying to remember which seemingly basic features are supported by which engines, and which aren't (e.g. percentiles are great in Postgres, but you're SOL in MySQL). That there's virtually nothing "standard" about SQL syntax or functionality at all, except for the simplest of queries and the general basic concepts.

paulddraper 3y ago
Well Java and JavaScript differ a lot too. Some similarities, but a lot of differences too.
magicalhippo 3y ago
We're in the process of migrating our on-premise business application from SQLAnywhere to MSSQL. Imagine the fun we're having...
- eddd-ddde 3y ago
  I'm really curious, in the practice, what causes a product to decide "we are moving from abcSQL TO xyzSQL" ?
  - crazygringo 3y ago
    In that direction, it's usually going to be about robustness -- more tooling, less edge cases, a wider developer base, built-in cloud support, and so forth. The more popular a database is, the easier just everything is. Occasionally it's going to be some very specific aspect of performance limits, or a feature like spatial indexes that work in a special way. And occasionally it's going to be about cost or better integration with a particular cloud or other specific piece of tooling.
  - magicalhippo 3y ago
    For us there's been two major motivations, and another few lesser. First and most prominent is that Sybase was bought by SAP and SAP has pivoted SQLAnywhere from a general RMDBS to focus on mobile applications (ie more embedded). Apart from product development changing focus we've struggled to buy licenses for our customers, so the writing on the wall was clear. The seconds is that we've got some big customers who run their own MSSQL servers, and they've been pushing harder and harder for us to use their servers so they can handle administration and access control directly. A third motivation is that while we've been primarily on-prem, a lot of customers these days don't want that, so we've started offering cloud hosted options. Since we're a Windows shop Azure made sense, and as such the hosted MSSQL also makes sense and is quite convenient. Fourth is that, while MSSQL is quite anemic in terms of SQL language functionality, it does support transactional schema updates, which makes schema upgrades much less error prone. Given that MSSQL syntax support is quite limited and we now have developed tooling and library support for multiple databases, we'll probably also add support for PostgreSQL later.

stvltvs 3y ago

I agree that CTEs are a solution to some of the pain listed above, but CTEs aren't exotic. Lots of the major RDBMSs support them.

fifilura 3y ago
CTEs are an absolute must to get the right "do-this-then-that" approach.