8

When it comes to developing the prototype of a solution, often the technologies has not been decided yet and might not be the same that will be used in the finished product.

In this scenarios I tend to use Microsoft SQL Server writing the queries as standard as possible to simplify the eventual migration to another server.

Is there a way or some known practice to enforce the use of standard SQL over T-SQL dialect directly in SQL Server or via SQL Server Management Studio (SSMS)?

  • 3
    Portability is a nice textbook goal, but it rarely happens in practice. When you have a choice between standard syntax (<>) and non-standard (!=), where there is no compromise on performance or maintainability, I always choose standard. But when it comes at other costs, or there is no standard equivalent I tap out and go proprietary. The things you give up just for the ability to later completely switch platforms wholesale just aren’t worth it imho. – Aaron Bertrand Dec 28 '18 at 15:12
  • 6
    The only time portability is a realistic goal is when you’re writing an app that needs to integrate with multiple platforms simultaneously because your customers use different platforms. Even then, unless you want functionality to be limited and performance to be terrible on all platforms, I would ship packages meant to take advantage of features of individual platforms. – Aaron Bertrand Dec 28 '18 at 15:14
  • 1
    Just curious; in the history of ever, have you personally changed out the database system an app was using for another one of equivalent grade (e.g. Oracle -> SQLS)? I haven't – Caius Jard Dec 29 '18 at 17:32
  • 2
    The other thing I was curious about; how much of your prototype reasonably survives to be a full production grade system? Most of my prototypes share nearly no aspects with the full systems that are written out of them after the proto has proven a concept and sounded out some sensible approaches to how a solution should look; are you making your prototypes too perfect/hanging on to them too long? – Caius Jard Dec 29 '18 at 17:38
16

User Aaron Bertrand made some comments that align well with my thoughts on your question. This is more of a frame challenge than an answer to your specific question, but I think it's valuable to consider in this context.

Portability is a nice textbook goal, but it rarely happens in practice.

If you have to change platforms at some point, there will be changes needed to the application, the database, and probably many other things. If you can be somewhat "platform agnostic" without too much effort, that's fine. But it's really a bad business decision to use that as a design goal.

There are many places online where people discuss the downsides or programming this way, here's one of them that I find pretty compelling:

Database Abstraction Layers Must Die!

The Portability Fallacy

The author uses an argument I hear all the time: If you use a good abstraction layer, it'll be easy to move from $this_database to $other_database down the road.

That's bullshit. It's never easy.

In any non-trivial database backed application, nobody thinks of switching databases as an easy matter. Thinking that "the conversion will be painless" is a fantasy.

Good engineers try to select the best tools for the job and then do everything they can to take advantage of their tool's unique and most powerful features. In the database world, that means specific hints, indexing, data types, and even table structure decisions. If you truly limit yourself to the subset of features that is common across all major RDBMSes, you're doing yourself and your clients a huge disservice.

That's no different from saying "I'm doing to limit myself to the subset of PHP that's the same in Perl and C, because I might want to switch languages one day and 'painlessly' port my code."

That just doesn't happen.

The cost of switching databases after an application is developed and deployed is quite high. You have possible schema and index changes, syntax changes, optimization and tuning work to re-do, hints to adjust or remove, and so on. Changing mysql_foo() to oracle_foo() is really the least of your problems. You're gonna touch most, if not all, of your SQL--or you'll at least need to verify it.

That doesn't sound "painless" to me.

11

Do not enforce STD SQL.

Decide first which DBMS you will use according to the needs of your project, and take advantage of it.

9

Not really.

There is SET FIPS_FLAGGER 'FULL'.

This prints out a warning for non standard SQL - but some caveats are

  • I am unsure what specific standard this uses (and suspect it may be SQL 92)
  • From a quick test this doesn't complain about use of the + operator for string concatenation or proprietary functions such as GETDATE() so it doesn't seem very comprehensive.
3

"often the technologies has not been decided yet"

I would say this is absolutely NOT the case in my experience. In fact I don't believe I've ever heard of this except for perhaps something very small.

Usually this is established, and the new solution is expected to utilize what is already in use.

I would agree with the commenters above in that even if it's not established, you need to establish that first before you start writing queries and other code. Otherwise you're just potentially letting yourself in for a lot of wasted effort in a re-write right off the bat.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.