How I like to learn a t-sql command


January 7, 2013 by Kenneth Fisher

Recently I received a security request and realized I wasn’t comfortable in my ability to script out the t-sql commands I needed. By “comfortable” I mean I couldn’t create a login, then create the user, then add that user to a couple of roles and grant it permissions to some specific tables and stored procedures. All without looking at BOL. Now, that is my comfort level and I certainly wouldn’t push it on anyone else. It can seem a bit excessive to others, or maybe not. And I’ll be honest I don’t stress out if I forget an option or two, but I really feel like I should know how to script the basics of most, if not all of the tasks that I run across. Then if for some reason I can’t use SSMS, for example when using the DAC (dedicated admin connection) I’m still functional. Or sometimes I find that I’m going to repeating the same thing over and over, say creating multiple users, and I can just script it and do some copying and pasting. Frequently I find that after some practice I can script out even a single command faster than I can use the GUI.

Now I could go look at BOL and memorize the commands but that isn’t really my favorite way to learn stuff like this. One of the things I really like about SSMS is the ability to script out almost every action. In this particular case I used the SSMS GUI just like I normally would. Then I hit the script button.

Script Button

Now I have a script that does what I need it to. I read it, try to make sure I understand all the options listed, do my best to remember it for next time, but don’t stress too much about it. If I have to do several repeated actions, say create more than one login, add the user to more than one role etc, I only use the GUI to script out one of them, then type the rest using the first as a model.

Now, the next time I get a similar request I do my best to remember the command. I might write up the script myself, then go through the same process using the SSMS GUI to script it out again. Then I compare the two scripts. Hopefully I’ve gotten it right, but if not, I make any needed corrections to my script. I’ll go through this the next few times I need a given script. Over time I get more and more familiar with the script and with its options and I have to reference back to SSMS or BOL less and less.

Now, please understand, I do go back and read BOL from time to time. You will never learn everything about a command by scripting it. There is a lot of information in BOL, and books and blogs for that matter, that you really should know, that you will never learn using this method. For example the permissions required and information on WHY to use a command. However, I have found this is a good way to learn on the go, while getting my job done.

11 thoughts on “How I like to learn a t-sql command

  1. […] the restore GUI. You may be planning on running the restore through a job, you may be working on learning how to write restore scripts […]

  2. […] standard tasks it can be quite a bit faster to just use a script. In fact I posted a while back on using the script button as a method for learning T-SQL. So other than my personal preference why is it a good idea to know how to work without the […]

  3. […] I went ahead hit the Script button so I can read through the output. It’s one of my favorite ways to learn T-SQL. […]

  4. […] work with SQL Server 2014 or 2012. Well this can cause a bit of a problem when I’m using the script button to generate scripts of changes I make in the GUI. With each version some of the commands change, some of the settings change, etc. This means that […]

  5. […] case you hadn’t noticed, I love built in help. DBCC HELP, the script button in SSMS, even BOL. Well here’s another […]

  6. […]   Here is how the grading will go. Note: If you can, and have done any/all of this recently then give yourself the points. This is about ability not necessarily doing it this time. Be sure you can do it before actually giving yourself the points though. Note: Additional points are granted in some cases. If it says T-SQL that means you wrote the T-SQL yourself and didn’t use the GUI to generate the SQL. […]

  7. […] vast majority of which have been technical, some funny, some discussions of soft topics (expertise, how to learn, when to get help that kind of […]

  8. […] ago I blogged about how I like to use the SSMS scripting feature to learn how to do things. Well now I’m starting to learn Powershell and it turns out there is a GUI here as well that […]

  9. […] all, at least in SSMS it appears you don’t have a GUI. I don’t use the GUI often unless I’m working on a T-SQL command I haven’t used much before but this could be major shock for some people. I right clicked on Security under the database and […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,753 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: