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