New Episerver solutions will usually use the ASP.Net Identity middleware for OWIN.
This means that users, roles, claims etc. will be stored in tables in the SQL database. User's passwords will be hashed (using PBKDF2) and stored in the AspNetUsers table.
When using OWIN, you can add more authentication middlewares. In Episerver's documentation, this is called mixed-mode OWIN authentication. If you implement the code as per this documentation, and log on using the external IDP for the first time, then Episerver will import that logged-in user to another special user table. This is imported so that you can assign Episerver rights and roles to that user.
The sample code on that documentation page shows how to integrate with Active Directory, but you are not limited to that.
I have to implement site with multiple authentications with IDP and local EPiServer as well. How are the credentials stored in EPi database? How does it work on such scenarios?