Managing SQL Server via Management Studio vs TSQL Commands

By:   |   Updated: 2007-08-16   |   Comments (4)   |   Related: More > Professional Development Skills Development


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 - GUI Advantages - 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 - GUI Disadvantages - 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?

ID Task Commands GUI
1 Start SQL Server in single user mode Use DOS commands with the needed switches to start the SQL Server services Use the Services applet to add the specific switches and restart the SQL Server service
2 Perform backup and restore operations T-SQL BACKUP Command

T-SQL RESTORE Command

 Enterprise Manager - Backup and Restore Interface

Management Studio - Backup and Restore Interface

3 Review the SQL Server error logs Use xp_cmdshell with the TYPE command to review the logs Enterprise Manager - Error Logs

Management Studio - Log File Viewer

4 Build and deploy Service Broker objects Infrastructure 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.
5 Performance Tuning A 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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2007-08-16

Comments For This Article




Thursday, May 19, 2011 - 9:18:31 PM - Jeremy Kadlec Back To Top (13868)

Adam,

Thank you for your post and I can understand what you mean.

Please reference the last two sentences of the Solution section:

"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."

Thank you,
Jeremy Kadlec

Tuesday, May 17, 2011 - 6:11:57 PM - Adam Back To Top (13856)

 

I'm kind of on the fence about this article.

I'm not sure if I am a 'typer' or a 'clicker'. I use whatever is available to me to get my job done the best way with taking as little time as possible. I think with the new advancements in SSMS, so many things can be done with 'clicking'. Granted, yes, there are tons of tasks that are easier done by typing but how often do you have to reference BOL to remember all the parameters. For example, sp_addmergepublication. Off the top of your head, which parameters do you need to use with this documented SP?

I think it should be more as - "if you need to type, then type it out, if you want to click, click away". If you are a DBA, you are going to come across problems that can't be solved with the GUI. That's where 'typing' comes in.

I have worked with the guys who spend an hour writing pages of code to do simple things. While what they are doing might be cool, sometimes its overkill.

I just hope these titles don't catch on.

ps - you can do copy_only from the GUI in 2008 r2.

 

 

Thursday, February 17, 2011 - 12:11:24 PM - Jeremy Kadlec Back To Top (12961)

Ray,

Thank you for the feedback, I did try to outline some of your sentiments in the last paragraph of the tip:

"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."

I appreciate the insight with respect to the tools vs. code during the interview process.  It is something to keep in mind.

Thank you,
Jeremy Kadlec

Tuesday, February 15, 2011 - 3:59:55 PM - ray herring Back To Top (12935)

As is often the case with are you red or blue questions the answer is neither and both.  GUI and T-SQL each offer a number of unique capabilities that combine to provide a complete suite of management, trouble shooting, and tuning tools.  The GUI offers a quick method to connect to a server, get a pictorial overview of what the server has installed, etc.  The GUI provides a wide range of BASIC features and tools.  For example it is easy to take a quick backup of a database or modify the Recovery Model setting.  However, if you need to a "copy only backup" (to preserve the Log Sequence) then you need T-SQL.  If you need to modify the Recover Modle setting for 50 databases then a 10 line script is a lot faster.

If you need a quick glimpse of what is going on then Activity Monitor is often entirely adequate.  If you are trying to analyze Dead Locking and Blocking then you will quickly realize the GUI is woefully lacking.

If I interview a potential DBA hire and they proudly claim to be a "clicker" or a "typer" then my conclusion is they have just admitted they have a very narrow and limited view of the task at hand and possible a very limited vision of their responsibilties.  Unless the interviewee were very junior I would probably not call them back for a 2nd or pass them on to the rest of the team.














get free sql tips
agree to terms