T-SQL Still beats the GUI 👊
The SQL Server Management Studio makes a lot of operations very simple. Creating a database takes three clicks. A new login is a minimum of two clicks. So why is it that we at MinionWare still write T-SQL over 90% of the time, instead of using the GUI? We like code better than mouse clicks for three basic reasons:
• T-SQL lets you do more
• T-SQL lets you share your process
• T-SQL lets you keep a record
Do more with code
“Doing more with code” shows up in several ways: flexibility, applicability, and repeatability.
T-SQL is far more flexible, and so you have more options available in code. On the other hand, a GUI is locked into a certain set of options, and so you’re more limited.
Let’s take the example of creating a login. If you compare the CREATE LOGIN syntax with the options available in the SSMS, you will find all of the configurations represented. What you won’t find in SSMS is conditional logic: the option to check for an existing login first, to check the server name before creating the login, to throw a warning up if the server is a member of an Availability Group (which would require creating the login on all nodes of the AG).
T-SQL can be applied to many platforms. Write a set of T-SQL statements or stored procedure calls, and you can use those within C#, PowerShell, Python, HTML, and so on. But you can’t do the same thing with a set of UI clicks. T-SQL can also be used by third-party schedulers, monitoring apps, and more.
T-sql code is repeatable. Let’s go back to that login example. Need to create a login on multiple instances? Code it once, and run it on as many SQL Server instances as you like! If you insist on using Object Explorer, though, you’ll be repeating the same set of clicks and copy-pastes once per each instance. (Just thinking about it makes me fidgety.)
Pass along your process
Judging by the sheer amount of documentation, blogging, and books dedicated to T-SQL, we can agree that there’s a lot of information and scripts that we want to share with each other.
You can directly share T-SQL code; you can only indirectly share GUI operations.
Over the last fifteen years, I have sent hundreds of pieces of code – and blogs about code – to other DBAs, managers, developers, contractors, students, and deployment groups (think network operations centers or NOCs). I have documented the “click-click” version of a process within SSMS maybe a dozen times. I very much prefer the ease of sharing code.
In addition to being harder to document, step-by-step GUI instructions increase the opportunity for mistakes on the receiving end. Whether you’re sending out code for a production system or teaching your junior DBAs a new topic, what you’re really going for is fewer opportunities for mistakes.
You can also comment code. If you’ve seen my training session on commenting code, you know how important it is to explain things in code, give references, and generally mark up your work for the greater good. You can document and comment GUI operations, but generally, we don’t. And we don’t want to.
Use code. Comment your code with good information. We are, after all, data professionals.
Keep a record of operations
There are many, many reasons to keep a record of the code you create, whether we’re talking about different versions of objects you develop or code that’s run against UAT and production systems.
Version control is its own topic, and I won’t dig into it deeply here. It’s enough to say that we as a technical community agree that it’s a really good idea to have older versions of code as it develops and changes.
It is just as important to save a record of operations completed. In a given day, a database administrator may complete dozens of tasks: security grants and audits, index research, spot checks on performance, updates to maintenance routines, and so on and on. It is vital to save as much of this as possible – dated, preferably – so if there is an issue you can go back and see what it is you did.
For example: “I know you don’t have read permissions to DB3, I can see that. But I DID grant you those rights on the fifth. How do I know? I have the script right here, and it’s marked ‘done’.” Yes, I have had these conversations. I’ve also had a conversation with myself where something unexpected happened…and I checked my records to see I’d updated the wrong value. Good to know!
Is there any point to using the GUI?
Yes, of course! There are several reasons to make occasional use of the GUI. I personally find that for certain operations (like log shipping), going through SSMS and having it generate a script is extremely useful. Others use Object Explorer as a learning tool, to see what options are available, and so on.
But the point still remains nine times out of ten, code is superior to a GUI.