0

I am developing an app using SQL Server, but I've been asked to keep it compatible with the ANSI standard as much as possible in case we need to change to a different database in the future.

Is there a list of functions supported by pure ANSI SQL somewhere? I would have thought there would be, but I haven't been able to find one after 20 minutes on Google.

Robert
  • 91
  • 1
  • 1
  • 9
  • At the moment I'm specifically curious about the DATEDIFF and DATEDIFF_BIG functions, for the record. But I will want a general reference, too. – Robert Oct 06 '20 at 14:35
  • 3
    Neither DATEDIFF nor DATEDIFF_BIG are part of the ANSI SQL standard –  Oct 06 '20 at 14:42
  • 4
    I don't think that the ANSI SQL spec is freely available anywhere... I have been searching once too, and it looks like you have to pay to get it. On the other hand, I don't think that any database is 100% ANSI-compatible, so what you want to do may just not be possible. Date functions, especially, are highly vendor-specifif. – GMB Oct 06 '20 at 14:45
  • 1
    Best you can usually find is drafts of the standards - the final draft is usually "close enough". PostgreSQL used to have links to various drafts from one of their appendices, but they no longer appear to. – Damien_The_Unbeliever Oct 06 '20 at 14:51
  • 1
    E.g. here's the usual link I follow to get to a draft of [ANSI-92](https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt) – Damien_The_Unbeliever Oct 06 '20 at 14:53
  • 1
    Related - https://stackoverflow.com/questions/1714461/ansi-sql-manual – Dave Costa Oct 06 '20 at 14:53
  • 1
    @Robert you can't find the keywords and even if you did, it wouldn't help you. The ANSI SQL standard isn't freely available but then, *no* database offers much beyond basic compliance. Beyond the basic level all database products provide either partial support or use their own extensions. There can be huge differences between databases too, eg MySQL lacked CTEs and analytic functions until 8.0 – Panagiotis Kanavos Oct 06 '20 at 14:58
  • PostgreSQL on the other hand, offers no temporal support even though it was the first to support the temporal model proposed in the late 1990s. That got rejected eventually and PostgreSQL removed all support, even as other databases started supporting system-versioned tables – Panagiotis Kanavos Oct 06 '20 at 15:01
  • Back in the 90s/00s when even Microsoft expected Java to dominate (that's why they build J++), SQLJ tried to standardize embedding SQL into Java. That didn't work as expected either – Panagiotis Kanavos Oct 06 '20 at 15:04
  • 2
    Have you already seen this? https://learn.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-ver15 – shawnt00 Oct 06 '20 at 15:04
  • 1
    ANSI/ISO reserved words can be found at: https://en.wikipedia.org/wiki/SQL_reserved_words – jarlh Oct 07 '20 at 20:02

1 Answers1

2

but I've been asked to keep it compatible with the ANSI standard as much as possible in case we need to change to a different database in the future.

Note that ANSI compatibility is not sufficient to guarantee portability, as SQL Server's ANSI-compatible syntax may not be implemented by some other platform.

Best practice to maximize portability is to minimize database-side stored procedures and functions, as these always are different, and (as you are doing) prefer the ANSI flavor of doing something to a proprietary one. EG use CASE not ISNULL, etc.

At the end of the day you should plan to have an abstraction layer in your application that would allow you to change databases or support multiple. If you use an Object-Relational Mapping (ORM) in your app (like Entity Framework, Hibernate, etc), that can be used to minimize and manage the application code dependency on a particular RDBMS.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67