I hear this question very often during my discussions with developers and analysts. I had the opportunity to work on SQL Server 7.0 as part of a database project, way back in 2000. Within few years SQL Server 2005 was introduced with SQL Server Management Studio. Honestly at that Object Browser in SSMS was like a magic wand to me.
As the years progressed, SQL server rapidly evolved into a full-fledged RDBMS and now with the latest release of SQL Server 2019 (in CTP now). Apart from investing the SQL Server, Microsoft also invested and improved all the supporting tools for SQL Server.
I will try to attempt to categorize these tools into 2 categories, Native Tools and 3rd Party Tools.
Native tools are developed and supported by Microsoft and free to download and free to use by data professionals.
3rd Party tools made by non-Microsoft companies and they take it to the next level with additional features with super engaging visual representations and task management.
Native Tools from Microsoft
Microsoft SQL Server Management Studio. It is free and loaded with many features. This is the popular choice and more useful features are baked in.
Learn more here https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-ssms?view=sql-server-2017
Download Link https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017
Visual Studio with SSDT (SQL Server Data Tools). The obvious choice if you are a .NET developer and don’t want to leave your favorite IDE or switch to a different tool like SSMS.
Learn more here https://docs.microsoft.com/en-us/sql/ssdt/sql-server-data-tools?view=sql-server-2017
Download Link https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017
Azure Data Studio (formerly known as SQL Operations Studio). This tool is mainly complimenting SQL Server Management Studio but not to replace it. This tool offers a very modern look and feel. It may not replace SSMS but more features are in the works as per Microsoft. What is interesting with this tool is it offers installation of Extensions. Microsoft or 3rd party may develop and distribute different extensions and you can download them directly from this tool. For example, there is an Extension (still in preview) to create External Tables through Wizard rather than hand coding the script.
Visual Studio for Mac (if you are a Mac user)
3rd Party Tools (I will add more to this list as and when I evaluate each tool)
Toad® for SQL Server
TOAD stands for Tool for Oracle Application Developers and still my favorite tool for connecting to Oracle. Quest software bought this some time back and Quest released a version for SQL Server called TOAD for SQL Server.
SentryOne helps data teams write better-performing SQL Server queries, and analyze queries, at every stage of the database. Our industry-beloved Plan Explorer helps you get to the root of the toughest query performance problems.
Hope this info helps and answers the main question. You do have a choice and it all depends on the work you are doing. If you have any suggestion or would like to add any other tools to this list, please feel free to reach out to me.