Managing SQL Server via Management Studio vs TSQL Commands

Problem

Over the last few months I have heard the terms ‘typer’ and ‘clicker’ related to how SQL Server DBAs manage SQL Server.  Working with one specific customer, the customer came right out and asked me ‘if I was a typer or a clicker’.  I was surprised to hear that question, but I understood why they asked it as they observed me work.  They noticed all of the T-SQL commands that I issued as opposed to using Enterprise Manager.  They were also surprised to see how many SQL Server system tables that I accessed and the information that I gathered to troubleshoot the issue.  This same distinction between DBA management preferences became crystal clear to me during a recent Northern Virginia SQL Server User Group session.  As the group was having a discussion, I was surprised to see hear how how many people provided input based on clicks in the GUI (Management Studio or Enterprise Manager) versus issuing T-SQL commands.  So is managing SQL Server via the GUI or T-SQL commands better than the other approach?  Is there information that is only available in one technique and not the other? 

Solution

So let’s just clarify terms before we jump too far into this tip which could turn into a heated debate.  From the experience I have had, a ‘typer’ is a reference to a DBA that issues T-SQL commands.  Whereas the ‘clicker’ is a reference to a DBA that manages SQL Server with the GUI (Management Studio or Enterprise Manager).  As such, let’s take a look at a comparison matrix for both techniques.















Advantages – GUIAdvantages – T-SQL


  • Time Savings – The typical reason to use the GUI tools are to save time and energy to get the quick answer and get your job done.
  • Simplicity – Rather than having to search for commands to issue or that you cannot remember exactly, you can rest assured that the functionality that Microsoft commonly expects you to use is is all in one place.
  • Enterprise Management – In one interface you can manage SQL Server instances on your desktop to all the way around the world.


  • Internals – In general if you are issuing the T-SQL commands you typically have a good hold on the impacts to the database engine because you know exactly what is happening by the commands you are issuing.
  • Script Library – If you have a propensity towards using commands, you can build a script library over time that you can readily reference when you need to push out code quickly.
  • Development – Overtime understanding the T-SQL commands typically leads to a better understanding of the system objects and more efficient administrative and application development coding practices.
Disadvantages – GUIDisadvantages – T-SQL


  • Internals – When using the GUI you may not have a good feel for the actual commands that are being run (T-SQL or system stored procedures) and the impact to the user community when you click the ‘OK’ button.
  • Repeatable Process – If you have a process that needs to run across every SQL Server instance and you click through the GUI you may inadvertently miss a step and have SQL Server instances configured differently.
  • Black Box – In some circumstances, the GUI acts as a black box because you do not know the commands that are being issued.  I think that is the reason that SQL Server 2005 Management Studio has a ‘Script’ button on the top left of just about every interface in the tool so you can see what commands will be run.


  • Learning Curve – It takes time to learn the T-SQL commands and run them in a development\test environment to ensure you understand them so you are achieve your goals.
  • Timing – Depending on the task and the task frequency, it might take longer to write the T-SQL code and complete the task than using the GUI.  For example, all of the SQL Server counters that are available for System Monitor are available in SQL Server system tables\views, but it is much less time consuming to use System Monitor to capture the values over a few hour period of time.
  • Rewrite the Wheel – If timing is of the essence then writing code to perform a task that is already completed in an acceptable manner via the GUI is probably unnecessary.

Task Comparison

So when I start thinking about SQL Server management tasks, what are some of the common tasks and can they be equally performed with either approach?

































IDTaskCommandsGUI
1Start SQL Server in single user modeUse DOS commands with the needed switches to start the SQL Server servicesUse the Services applet to add the specific switches and restart the SQL Server service
2Perform backup and restore operationsT-SQL BACKUP Command

T-SQL RESTORE Command

Enterprise Manager – Backup and Restore Interface

Management Studio – Backup and Restore Interface

3Review the SQL Server error logsUse xp_cmdshell with the TYPE command to review the logsEnterprise Manager – Error Logs

Management Studio – Log File Viewer

4Build and deploy Service Broker objectsInfrastructure Objects

  • Endpoint
  • Message Type
  • Contract
  • Route
  • Queue
  • Service
  • Remote Binding Service
In SQL Server 2005 Management Studio, a Service Broker interface is not available with Management Studio to point and click and build the objects, but scripting out the objects is available.
5Performance TuningA variety of commands, system stored procedures, scripts, system tables, dynamic management views, etc. are available which can be queried to troubleshoot the performance issue.From a GUI perspective, the following native tools are available:

Third Party Considerations

When it comes to third party products the considerations are a little different.  Most of these tools have focused on a rich GUI that can be used across the enterprise to perform a specific task or two.  These tools provide the ability to make quick configurations that impact all of the SQL Servers in the environment which can be beneficial in some circumstances.  Many of these tools also offer a command line interface option which can address the need to automate or customize the interface as needed.  For information about 200 tools in the SQL Server industry check out the SQL Server Product Listing.

So what’s the bottom line?

As you can see, both of the techniques to interact with SQL Server offer much of the same functionality.  The reality is that the devil is in the details in how the task is carried out.  Independent of which technique you are using, you really need to understand what you are doing and how it is going to interact with SQL Server prior to executing the process.  Here are some additional items to take into consideration as you make a judgment on the best way to interact with SQL Server:

  • Starting off as a DBA – I think one of the reasons why SQL Server has gained in popularity with IT Professionals (DBAs, Developers, Network Admins, etc.) is the set of rich applications from Microsoft and third party vendors that are available at a reasonable price to manage and build applications.  So why not use them starting off and then start to learn the underlying commands so you are well versed in what the applications have to offer and the flexibility of the underlying commands?
  • Options – With SQL Server you have a variety of options to resolve a need.  What might work one day might not work the next.  What I mean by that is that environments change.  When you are in a SQL Server environment with a few instances checking your SQL Server error logs or job status by clicking through the GUI is fine, but as you grow checking each log every morning does not scale well, so adding code to automate this process is essential.
  • Comfort level – Figuring out your comfort level is probably going to be your best bet in the long term.  I have heard and seen that many people have the impression that DBAs do not know what they are doing if the use the GUI.  I think that is incorrect, although I use T-SQL commands more than the GUI, I think the information in this tip shows that using the GUI in a number of circumstances can save time.

I believe that the reality is that all DBAs should know both the T-SQL commands and the SQL Server GUI tools to manage the environment.  The ‘typers’ can learn from the ‘clickers’ that the GUI does provide some short cuts to address quick needs.  The ‘clickers’ can learn from the ‘typers’ the power of the underlying commands as they need to scale in their environment.  So being a ‘clicker’ and a ‘typer’ at the right times make all of the sense in the world.  It is just knowing what is right for the circumstance at hand.

Next Steps

  • After you read this tip today, take note of your propensity towards being a ‘typer’ versus a ‘clicker’ to see if you are missing the boat in what the other technique has to offer.
  • If you have always completed a task a particular way, it might make sense to seek out all of the options to complete the task and determine the right tool for the job. 
  • One appropriate time to conduct this checks and balances of sorts may be when you upgrade to the next version of SQL Server.  The new platform may have new options and completing the task in the same manner as you have for previous versions may or may not be superior to how you are completing the task today.  So have an open mind, research options and determine the right approach for you and your organization.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *