Your question is a mélange of misinformation and cross-threaded topics, which makes it exceedingly difficult to answer.
First off, there is no comparison of the "SDE API" and "MSSQL Native geodata format". ArcSDE is many things, including a code library for accessing data (not spatial data, all types of data, including geometry) in multiple SQL (and no-SQL) databases, a networking protocol, several geodata storage implementations, an application server, and a suite of utilities written using the API. The ArcSDE API is compatible with geometry storage in MSSQL
Any "complicated" SQL query can be submitted through both the ArcSDE API and through the ArcObjects (and Python) interface, as is. It's not complicated which causes trouble so much as non-standard/unsupported return datatypes, or results which are missing a unique identifier (which is an ArcObjects, not ArcSDE, requirement).
Esri has been phasing out direct support for the ArcSDE API for some time. ArcGIS 10.2 was the last release in which the API, command-line utilities, and application servers were available. Most of the functionality of the command-line utilities have been either obviated or replaced with ArcObjects/Python geoprocessing utilities (some work to support spatial view registration is still ongoing). In addition, ArcGIS 10.0 introduced Query Layers, which are direct SQL queries through RDBMS drivers (not ArcSDE), which removed the requirement of a full enterprise geodatabase install for spatial data access.
Finally, there is no one configuration option for geodata storage type. Any Esri enterprise geodatabase installation has access to all supported storage formats for that RDBMS at all times (using DBTUNE keywords). You can certainly choose to migrate part or all of your data to any of the supported formats, though there may be performance implications that are specific to individual datasets and tuning options, but you can continue to create new feature classes in any supported format.
In general, the GEOMETRY and GEOGRAPHY types perform well in comparison to SDEBINARY storage (+/-10%), though they really ought to perform much better, given the natural advantages they have (these advantages, most notably the "one table" solution vice an ugly three-way join, are why
GEOMETRY has been the default storage format since ArcGIS 10.1). The issue is generally the difficulty in tuning the Microsoft spatial index scheme. With proper tuning, most datasets in GEOMETRY format can at least equal SDEBINARY performance, though some users have given up on the native formats and moved back to SDEBINARY.
In the end, Esri users can choose the storage format which meets their needs, which span too many potential parameters for a one-size-fits-all solution. As with all tuning situations, it's probably wise to evaluate performance with actual data (or a properly scaled representative sample) before committing to a format change.