We have common user database table for different applications. So we need to implement login functionality only on CD site using external common sql db and for CMS we are using default sitecore user membership. So can one please guide me how it can be implemented in sitecore?
As you mentioned that you're using a custom SQL database for User database, you can implement the Login/Registration backend functionalities similar to a plain .NET application. You can create (if not available) tables like User, UserRole etc. to maintain User information (such as User First Name, Last Name, Password etc.) and you need to implement your own Login/Registration Controller to handle the Registration/Login flow on your CD website.
In terms of Sitecore, you can take advantage of keeping and managing content for your Registration & Login screens in CMS (e.g. Field name, Field type, header, body, validation messages, button text etc. - similar to WFFM module). This gives you benefit of utilizing Sitecore platform and can be further extended to use even out-of-the-box Personalization - if you follow the component based approach while displaying these Login/Registration screens). We use this approach & architecture in many of our projects and it works as expected. Note: If you're using latest Sitecore version (>= 9.0), you may also consider using Sitecore Forms for Login/Registration forms with customized Submit button action.
Future Use-case: Based on my experience with this approach, if you really want to utilize both Sitecore XP & XM powers (which we should ideally), you should also keep in mind extending it and keeping a contact in xDB as well (a xDB contact should not be complete duplicate of User record but only what is required). You may need to write Subscribers, background jobs to keep this xDB contact in sync with User record whenever there's update occurs on User data. You may use this xDB known Contact further for any of your XM need (EXM, Personalization, Marketing Automation etc.)
Hope it helps!