3 ms·
I firmly believe that teams working in static languages do not easily transition to dynamic languages, and vise versa, yet when working with SQL that is what we
by moron4hire 11y ago
I firmly believe that teams working in static languages do not easily transition to dynamic languages, and vise versa, yet when working with SQL that is what we're constantly asking people to do, because SQL is either less static than C# or Java or more static than Python or JavaScript. It's this weird, bastard of a language that sits halfway in between, with no good way of unit testing it because most operations are stateful [0].
Most SQL engines include a static verifier of some kind, but it ranges from nearly non-existent in MySQL's case to so-pedantic-it-hurts in Postgres' case. Even worse are the ones like MS-SQL that are static in some regards (you can't usually submit a stored procedure that references non-existent tables), but dynamic in other regards (but non-existent columns in those tables are fair game!).
Personally, I'd prefer a lot more static checking. This starts off ok, but I need more. This appears to be Postgres only right now. I think a static checker for vendor-specific SQL is a key component for getting people off of Oracle or MS-SQL. I've seen tons of database conversion projects die on the vine because of the issues with translating vendor-specific extensions. We need a "VM for SQL", some way to write-once-run-anywhere, and ANSI SQL is insufficient. I mean, have any vendors actually gone beyond SQL-92? There have been four newer versions since then.
[0] I mean, that's not a surprise, that's the entire point, it just makes good unit testing very hard.
- greggyb 11y ago>I mean, have any vendors actually gone beyond SQL-92? Yes. Here's a pretty good summary presentation (it says it's Postgres-focused, but it provides adoption data for the major vendors)[0] and the HN comments[1]. What we tend to see (I speak from a position as a BI consultant - I get to see lots of companies' database infrastructure) is that adoption of new SQL constructs is mostly limited by the developers, and in some cases IT departments who won't upgrade SQL server software. New SQL being limited by developers comes in two flavors, crusty DBAs and do-it-all-in-the-app-layer developers. The second type tends to be more common among demographics similar to HN. [0] http://www.slideshare.net/MarkusWinand/modern-sql http://www.slideshare.net/MarkusWinand/modern-sql [1] https://news.ycombinator.com/item?id=9018129 https://news.ycombinator.com/item?id=9018129
- moron4hire 11y agoThat's a pretty abysmal report. You basically can't count on any of those features to be available across databases. You are correct about the fear-of-SQL amongst most application developers.
- greggyb 11y agoThe target is moving. Your question: "Have any vendors moved beyond SQL-92?" The answer is a clear yes. The question was not, "Do all major RDBMSs support the same subset of the SQL standard?" The implied concern with the new question is portability of code across databases. This is a red herring based only on some snippets of SQL syntax and features.[0] Even if the SQL were identical, no major RDBMS has as its selling point that it only supports the SQL standard. Additionally, even with identical SQL, the implementations in each RDBMS are different enough that logically equivalent code would still need to be rewritten. One example: There is nothing in the SQL standard about optimization of queries. One place this shines through is in Postgres CTEs vs MS SQL Server CTEs (I cannot speak to other implementations). In Postgres, a CTE is an optimization boundary - predicates from the query utilizing a CTE will not be pushed down into the CTE. The entire CTE is executed each time it is referenced. This is in contrast to the behavior of sub-queries, which do support predicates being pushed down into them. In SQL Server, CTEs are just an in-line view definition and do support predicates being pushed into the CTE from the outer query. Any codebase converting from MS SQL Server to Postgres would need to have every instance of CTE use evaluated for performance. [0] The previously linked deck highlights only some advanced excerpts of the SQL standard. The venn diagram of supported SQL constructs is much more overlap than exclusive.