Which SQL version or edition should we use out of SQL server 2017std/ent or 2016std/ent in terms of better performance gain when you are dealing with SP's (approx 1000 or more)in single database which are equipped with complex joins over tables, views, udfs and also having SORT and ORDERBY clauses in many of the SPs. Thanks
There is not much different between 2016 and 2017 especially in terms of performance (except following points). I believe, the main aim of SQL 2017 is to cover cross platform support (Windows, Linux and Docker).
Of course always newer versions would be having additional benefit in general, you need to consider other aspects while deciding on version/edition, hope this blog post would be useful
For version, 2017. Later versions have additional features and improvements, so for performance always prefer the latest version.
and has more sophisticated High Availability and Disaster Recovery features. But they have the same query optimizer and support the same programability features.
In general large databases, and databases with very demanding throughput requirements may perform better on Enterprise Edition, but smaller databases on smaller servers will perform the same.
See generally Editions and supported features for all of the differences.
If you need to check your code for any game-breaking issues, you can use the Database Migration Assistant against your current database.
You can then see if going to either one will be impossible due to features you are using. Off the top of my head, I don't know of any that changed between 2016-2017. It is possible that your current version won't work on either though, so you should check.
Be aware that the new cardinality estimator can also cause you problems, so you should evalaute that as well.