SCRIPT IT! SCRIPT IT!! SCRIPT IT!!!

If you use Enterprise Manager (EM) to create or edit table structures, data, stored procedures, or much of anything else, you are a rookie.  Now, each one of us was a rookie at some point, and you do have to start somewhere, but if you want to be considered a professional, you HAVE TO learn the SQL syntax and get comfortable living in Query Analyzer (QA).  Period.

First, a little secret about EM...it doesn't do anything magical.  It just has been programmed how to translate certain actions the user does in the GUI into proper SQL commands and then executes them.  Don't believe me?  Check it out for yourself.  Often in EM there is an option to “Script Changes”.  When you choose this, EM will write the SQL commands out to a file which you could then open in QA and execute.  If you're new to the idea of using QA and the SQL commands, this is a good way to start, so you can see what it's doing.  Start out with simple changes, like adding a new field to an existing table, so there is not too much information to wade through.  This list of SQL commands is also called a script.

It is a good practice to script ALL of your changes (yes, all, as in every one, no matter how simple you think it is, make it a habit and never waver from it).  There are several benefits to using scripts:

  • You can (and should) save your script files (usually ending with a .sql extension) into some form of source control (Visual Source Safe, Concurrent Versioning System, etc.).  This allows you to know exactly what changes have been made, and if you ever need to “roll back“ you have a history of changes.
  • It is easy to try, validate, edit and re-try your script to get it juuuust riiiight.
  • After you have the script working just right on your development server (You DO have a separate development server don't you?  Or at least a separate development database?) it is easy to just execute the same script on your QA and later your production servers and not worry about whether you remembered how to do it, because it's saved right there in your script file.
  • As your system gets more complex, you will need to make more changes at the same time, and oftentimes, the order of changes is critical.  Once you get the script all working properly on your development server, you don't have to try to remember all the little details and proper sequencing, because it's all in your script(s).
  • EM hides some information.  This is most apparent when someone is looking at data within a table.  EM will hide some of the data in long fields in order to get it to fit within the display window.  This has caused more than one person to think their data was messed up.
  • Personal Development:  As you get comfortable working with EM-generated scripts, and familiar with the SQL language and syntax, it opens a whole new world of development and management tools, tips, and tricks available to you.  At this point, your career is really about to take off.

Remember, all the database development and management actions you can take within EM can be done in scripts in QA.  And if you follow my advice, you'll thank me for this later.  Now get out there and SCRIPT!

posted @ Tuesday, October 21, 2003 11:29 PM

Print

Comments on this entry:

# re: SCRIPT IT! SCRIPT IT!! SCRIPT IT!!!

Left by Travis Laborde at 10/22/2003 5:03 AM
Gravatar
I agree wholeheartedly. About the only thing I still do in EM is create tables. The initial GUI for doing that, along with adding the indexes and relationships right there really does save me some time. I keep telling myself to "just do it" in QA. Your post is just another prod in the right direction. Thanks.

# re: SCRIPT IT! SCRIPT IT!! SCRIPT IT!!!

Left by brenth at 10/25/2003 8:31 AM
Gravatar
Query Analyzer? Pansies! Real DBAs use command-line OSQL! :)

Just kidding -- good points, Mark!

# re: SCRIPT IT! SCRIPT IT!! SCRIPT IT!!!

Left by Peg at 11/19/2003 6:36 AM
Gravatar
Hi. My name is Peg and I am a SQL Rookie! *smile*

Are "scripts" just another name for a saved QA query?

# re: SCRIPT IT! SCRIPT IT!! SCRIPT IT!!!

Left by AjarnMark at 11/19/2003 9:34 AM
Gravatar
Hi Peg!

Essentially yes. Scripts are saved sets of SQL statements, whether for SELECTS, INSERTS, CREATES, ALTERS, whatever. The key being that you have a saved file (or files) that contain exactly what changes you made in case you need to modify it or come back later to research "What Happened?!" issues.

# To ALTER or to DROP/CREATE... That is the Question

Left by Ajarn's SQL Corner at 1/24/2004 10:55 PM
Gravatar

# Source Control and the Build Process

Left by Ajarn's SQL Corner at 2/27/2004 12:43 PM
Gravatar
Do the terms Build Master or Development DBA mean anything to you? Learn more about the deployment process...

# re: SCRIPT IT! SCRIPT IT!! SCRIPT IT!!!

Left by Lisa at 3/19/2004 7:32 AM
Gravatar
Great article.
I am another "rookie". I learned SQL syntax in college but in my real life...I use EM for faster. After reading your article, I realize that I have missed so much. Thx.

# re: SCRIPT IT! SCRIPT IT!! SCRIPT IT!!!

Left by Heavy D at 3/30/2004 9:43 AM
Gravatar
Your a clown! You make yourself sound so exclusive in your skills. "Oh if you use EM you are such a rookie!!! Waahhh!!!" And then you go on to say use Query Analyzer. Whoopty Doo hero. I am so impressed by the way you really go out on a limb by moving to QA. Take it easy partner, don't get too crazy!

# re: SCRIPT IT! SCRIPT IT!! SCRIPT IT!!!

Left by AjarnMark at 3/30/2004 10:10 AM
Gravatar
Oh yeah, Heavy, you've got my number there. You can tell by all my smart-ass answers on SQLTeam what a goofball I really am. I'm really just a slick salesman. Let me flish-flash a couple of scripts in your face and I know you'll be impressed, which after all, is the most important thing, isn't it? I mean if you can't impress people, then why go on, right? Just flash and dash! No need to learn anything about what's really going on in SQL because everyone at the company will be more ignorant than you, right? So just baffle them with bullshit and you'll be okay. Oh yeah, that's it.

By the way, I believe the phrase you were looking for was "You're a clown". "You're" being the contraction of "you are" as opposed to "your" which is possessive. I don't own any clowns.

Have a nice day and thanks for stopping by, though.

# re: SCRIPT IT! SCRIPT IT!! SCRIPT IT!!!

Left by Lisa at 3/31/2004 7:36 AM
Gravatar
Hi Mark,

Again, thanks for your posts.
I am on the process of learning SQL commands and I have 2 questions regarding to your post above.

Question 1: You stated: "Often in EM there is an option to “Script Changes”. When you choose this, EM will write the SQL commands out to a file which you could then open in QA and execute. If you're new to the idea of using QA and the SQL commands, this is a good way to start, so you can see what it's doing."

I don't see any "Scrip Changes" in the BOL when I did a search. However, I did a search in the index of BOL and I find this: "script generating". Am I closed? According to BOL, I right click on the db, All tasks, Generate SQL scripts and finally it saves all the SQL commands into a file with a default ext (.sql). GREAT. However, when I open up the sql file, I was surprised by the amount of the code that is genrated. I mean, there are about 60 lines of code!?!??!??! So far, I only created 1 db called, "TestingDB". I thought for a simple db, it should only a few line of commands...


2) You stated: "You can (and should) save your script files (usually ending with a .sql extension) into some form of source control (Visual Source Safe, Concurrent Versioning System, etc.). "

What is the benefit of saving .sql script into VSS or CVS?
Do I need a special softwares to open these files?


You also stated: "This allows you to know exactly what changes have been made, and if you ever need to “roll back“ you have a history of changes." I understand what you are saying but it is hard for me to visualize without real example. So, if I want to create an example to see all the changes have been made, where do I begin? Am I going to start create a CVS file? Then what is next???

Thank you in advance,
Lisa

# Scripts and Source Control - 102

Left by Ajarn's SQL Corner at 3/31/2004 12:44 PM
Gravatar
In the comments section of my SCRIPT IT post, Lisa asked a couple of questions for clarification. These were a good reminder to me that some of the things that I just take for granted these days are actually very new concepts to others who haven't had the same experience. So rather than just post a reply in comments, I thought it'd be worth a post unto its own.

# re: SCRIPT IT! SCRIPT IT!! SCRIPT IT!!!

Left by Ben Attwood at 6/10/2004 6:35 AM
Gravatar
I agree with the article and am an SQL rookie. Working for a degree I know that my tutor would laugh in my face if I said I created tables using EM. I do find it useful to see what sort of mess I have created after i have run my scripts!
Comments have been closed on this topic.
«September»
SunMonTueWedThuFriSat
31123456
78910111213
14151617181920
21222324252627
2829301234
567891011