I am using ArcGIS 10.4.1 and Alter Field from Model builder in SDE and it behaves differently in these two cases:
What I did is that I truncate the feature class to empty it, and I altered the field changing the type from numeric to text in ArcCatalog. With ArcCatalog I had the illusion that the position of the field did not change. But after closing and reopening Arcatalog the field shows at the end of the feature class.
Any suggestion in how to change the type of a field by Model builder, Python or .Net and keep the position of the field.
As can't speak for all EGDBs, but for SQL Server what you are seeing isn't unexpected given the actions you are describing. For SQL Server, ArcGIS Text fields are stored as NVARCHAR. Changing an NVARCHAR field from a smaller to a large value is mostly a metadata change, and it would not require dropping a column and adding it back. Going from a larger to a smaller value is a different story because you might truncate data, but I am not sure if that forces a column drop or not.
DMBSs don't really convert a column from one data type to another. It might appear in a UI or geoprocessing tool that a column datatype is being changed, but really a column is being dropped and a new one added with the same name but different data type, hence why you see the column show up at "the end of the feature class."