Query: Investigate New T-SQL Functions in SQL Server 2017

[anpete]

https://www.simple-talk.com/blogs/new-t-sql-functions-sql-server-2017/

[pmiddleton]

Is there currently a way to distinguish what version of sql server you are running against? I have not seen one, but I might have missed it.

[smitpatel]

Reverse Engineering checks for it See here https://github.com/aspnet/EntityFramework/blob/dev/src/EFCore.SqlServer/Scaffolding/Internal/SqlServerDatabaseModelFactory.cs#L165

For runtime,

• Most things are explicit configuration. Like MemoryOptimizedTable If user specified fluent API then we assume that SqlServer supports it.
• For explicit downgrade case like Fetch/Offset not supported, user needs to configure with apis like UseRowNumberPaging

[anpete]

Opting-in to version specific translations via options doesn't seem terrible.

[pmiddleton]

It does seem like the number of options you have to opt into is going to get rather large in the long run. Something small like this would be nice if it just worked.

However if you need to opt in it does make things easier to implement because we don't have to figure out what database version we are running against to see if the functions exist.

[smitpatel]

We support from SqlServer 2008 + so at this stage perhaps going into default of SqlServer 2017 wouldn't be possible. Perhaps at some point in future we can make it default.
The opt-ins are generally based on buckets rather than SqlServer version. But we can certainly create bucket for "features of SqlServer 2017" or something along those lines which makes sense from user perspective.

I believe, we did not want to get into figuring out SqlServer version from connection therefore the current mechanism of opt-in exists. We will discuss during Triage about this one.

[roji]

I remember this discussion took place a long time ago (can't find the issue right now). PostgreSQL has also added some features over the years which make it tricky to support many versions back. Note that changes in the database's type system specifically can be covered by #7275 but there's nothing more general.

I think I can imagine what a general solution would entail here, and I can imagine why you guys are averse to going down that road. But from a user perspective it does seem odd (to say the least) to have to tell EF Core to opt into features it could be finding out itself. It may sometimes be a bit unreasonable to expect users to know which features can be activated on which database version, and things become more dangerous where the absence of a feature isn't a clear "not supported" error but rather an optimization in a later version of a database which simply isn't turned on because users aren't aware of it. The EF Core provider is in a much better place to make version-based decisions than each database developer.

But I don't have anything catastrophic in PostgreSQL specifically - the above is more theoretical from my side at least.