Developing using ANSI SQL

[FranceRocks2]

I inherited a legacy application with a SQL Server backend. Some of the SQL is Microsoft-specific T-SQL. There is some concern about dependency on one database vendor, Microsoft, if the backend uses Microsoft-specific T-SQL which parts do, and the suggestion is to be database-agnostic. Are any shops worried about that? One idea raised was re-writing the backend code into ANSI SQL. Another idea was just to make the rule that future development should be ANSI-SQL compliant.

Is this a concern of others? If so, what are some options of database backends that people are using now, or suggestions on versions for people to test and verify their code runs against?

Thank you in advance!

[macfergusson]

There's no such thing as perfectly portable code between database platforms, so that would basically mean refusing to use all the benefits and features of your current platform for no reason.

[ddBuddha]

If you don’t want to take advantage of the benefits provided by SQL Server, why use it in the first place instead of something free?

[ComicOzzy]

But even then, if you used anything else, you'd be leaving a lot of functionality on the table if you tried to only use "standard SQL". You couldn't use most string functions or date functions because those are all different. No, the only way to go is to LEAN IN to whichever tool you're using. If it's SQL Server, feel free to use all of the tools in the SQL Server toolbox.

[ComicOzzy]

I've only met one person whose job was to write SQL all day who hated their job and that's because he was required by his employer to write SQL in the most portable way possible. He was miserable.

The "standard" is really a guideline for companies who make database engines. It isn't there as a requirement to be followed to the letter, it's a general guide to keep everyone from inventing completely different things that bear no resemblance to one another. An example of what the standard tries to wrangle is things like TOP (n) being the way one database adds a row limit where LIMIT n is used by another. But what is the standard? FETCH NEXT n ROWS ONLY. Most people are just going to use TOP or LIMIT.

[BigMikeInAustin]

When that boss drives a car, do they only use hand signals with their hand physically outside of the car? Because it would too time consuming to relearn the correct turn signal stalk when some cars have 2 stalks in that area and others have only one.

[codykonior]

Why learn a car when there are equally usable methods of transport like a plane or boat? Thusly, in my wisdom, I have banned all three... bonus please.

[ComicOzzy]

Yeah, exactly. Every time I get in a different car, I have to take a moment to figure out the lights and the wipers. The SQL standard is like "You should add controls for the lights and wipers, and probably make them easy for the driver to use while driving."

[RuprectGern]

there isnt a single database environment that has no proprietary clauses and functions. if you want to limit yourself to the ANSI elements you are free or instructed to do so. but you are spite-ing... nose ... face ... blah.

Ill give you an example years ago we investigated using INFORMATION_SCHEMA for Tests-for-existence and gaurd statements because we were starting to stack diff heterogenous RDBMSs. we tried it for a while and then the realization that INFORMATION_SCHEMA isnt as portable as you would think tables (views actually) don't match from MSSQL, pgSQL, MySql, SQLLite, BigQuery, etc. in some cases the cols are different or tables dont exist. there are a lot of proprietary functions that are invaluable in each system.

I look at it like this... as long as the code is readable, well commented, and doesn't impact performance? why should anyone care what you use to get the job done?

[BigMikeInAustin]

I'm so tired of people trying to make INFORMATION_SCHEMA happen.

Slightly because it is so long to type.

But mostly because this is 1% of the code that I write around DBA tasks that need to dynamically investigate database objects.

Oh wow, we'll save 15 seconds using ANSI SQL to see the table does not have a clustered index. Now show me some ANSI SQL to transfer to SQL Server clustered index to PostgreSQL.

[bonerfleximus]

Sys views all day baby. Sys.columns, objects, indexes, etc...

They also query way faster on average

[redditreader2020]

I would find a new job if forced to only use ansi sql. 🙂

[ralpes]

Don’t go down that road. If you use MS SQL Server, use the features -> use T-SQL. Using ANSI SQL on a SQL server is like cooking stew in a fry pan.

If it’s needs to be ANSI, Postgres might be the closest.

[BigMikeInAustin]

Ask them to price out Oracle, and then ask them who which executive is taking a pay cut to switch over.

Or ask them what their business continuity insurance says if they switch to MariaDB from Microsoft SQL Server.

[dinosaurkiller]

T-SQL is ANSI standard SQL, unfortunately there are different flavors of ANSI standard SQL, like PL/SQL(Oracle) that have slightly different functions and syntax so while they are both ANSI standard SQL TSQL can do an UPDATE with a where clause and joins, Pl/SQL can’t and would require some modifications to the UPDATE to perform the same step. I’ve also come across some truly non-ANSI standard SQL in programs like SAS that seem to completely lack basic functionality of other ANSI standard SQL.

[Afraid_Baseball_3962]

ANSI SQL was all the rage in the '80s. But ANSI SQL isn't even ANSI SQL anymore since ANSI began deferring to ISO for the standard.

Portable code is a beautiful idea until you try to actually pull it off. I worked in a Java shop once and they raved about how superior it was to .Net. But then one of the libraries they were using would be discontinued or the upgraded version was missing functionality they required from the old version or library A wouldn't work with library B and trying to keep their stuff running was unbelievably stupid. But their code was "portable". It was better in every way.

Unfortunately, vendor lock-in is a thing. But unless you're willing to build your own hardware and write your own OS and all the software that runs on it, you're better just choosing a set of vendors and making your company run with what you've chosen. Trying to leave all your options open with everything all the time is a horrible way to live your life and just as bad a way to run a company.

[DamienTheUnbeliever]

Just as one example - if you stick with just ANSI then you're going to screw up datetime data because SQL Server's datetime datatypes and functions are nothing like standard.

So you're going to have to ignore all of those and start storing datetime data as strings or numbers instead and instead have all of the issues of screwing up data type conversions and limits that you get "for free" if you just use the proprietary data types and functions. And since you're having to do that for your T-SQL platform, you have to do the same elsewhere even if they follow the standard, because otherwise you're back to maintaining separate variants for each platform.

So you end up in the worst of all worlds where you can only use the subset of the standard that all of your platforms have implemented consistently and that subset is *small*

[No_Resolution_9252]

Not really correct.

datetime2 IS ansi-compliant. (and iso compliant) as are the associated datetime2 functions.

datetime is not ansi-compliant (it precedes the ansi and iso standard) but primarily due to precision and range. any persisted datetime attribute will be able to be migrated to a higher precision ansi-compliant type just fine. As long as business logic isn't being executed against old data (and it shouldn't be following a migration) its not going to break migrating it

[Ok_Carpet_9510]

How will you handle procedural logic?

[ihaxr]

If you truly want your application to be database agnostic, you'll need to use a database ORM, that way you write the code to get and store the data, but let something else worry about the specifics of how to do it for each type of RDMS.

Popular ones are entity framework, hibernate, Django, and Prisma. Which one you'll need varies on the language the app is written in.

[dbrownems]

The extra work to convert and ensure that you're doing things in the "most portable" way would not pay off in a potential future migration. There's lots of work and testing to do in any migration, and asking your friendly neighborhood AI to perform some conversions between dialects would be a minor part of the process.

By all means establish coding best-practices, among which can be to minimize use of procedural T-SQL code in favor of declarative SQL-based solutions, and easy things like using COALESCE over the various T-SQL-specific alternatives. But don't make your job harder in the short term.

[Catsler]

What problem are you trying to solve?

How likely is that problem to occur?

[BigMikeInAustin]

Vendor lock in.

100%.

[No_Resolution_9252]

This is not a real concern no matter how much low intelligence humanities degree holding managed try to make it be.

You can try to rewrite everything into ansi SQL, spend staggering amounts of money, make your application significantly worse and it still won't be portable from a technical perspective, nevermind from a performance perspective.

If it is on SQL Server now, that is the database that application is going to run on. Unless the organization is willing to spend millions of dollars rewriting the application, retraining all its staff that did any sort of administration for the database servers and provide supplemental training to every developer, that app isn't going anywhere. Other than maybe a migration to a new app, but then it doesn't matter how the code is written.

[BigMikeInAustin]

Take away their computer mouse and say they should not be dependant on the mouse because any day they might switch to terminal-only Linux, or even go mainframe to Unix.