Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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)?
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.
Do not enforce STD SQL.
Decide first which DBMS you will use according to the needs of your project, and take advantage of it.
Not really.
There is SET FIPS_FLAGGER 'FULL'.
This prints out a warning for non standard SQL - but some caveats are
+ operator for string concatenation or proprietary functions such as GETDATE() so it doesn't seem very comprehensive.
"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.
<>) 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.