February 6, 2002 at 3:18 pm
Hello all...
In my researh in the past few weeks, concerning the usage of SQL2000, I have seen much of this....
CODE HERE --
CREATE TABLE Location
(
LocationID int NOT NULL,
LocationName char (30) NOT NULL UNIQUE,
CONSTRAINT PK_Location PRIMARY KEY (LocationID)
)
CREATE TABLE Parts
(
PartID int NOT NULL,
LocationID int NOT NULL,
PartName char (30) NOT NULL,
CONSTRAINT PK_Parts PRIMARY KEY (PartID),
CONSTRAINT FK_PartsLocation FOREIGN KEY (Location ID)
REFERENCES Location (LocationID)
CODE DONE --
What I'm asking is this. Is this normal? In using MSAccess over the past 4 years, I always believed that I worked direcltly in the program itself, creating new tables, setting Primary and Foreign Keys, assigning properties like NULL and integer etc. etc.
Yet it seems like most everyone uses the 'create a dbase table on the fly' methodology judging by the vast number of times I've seen this.
Is this correct? Why would anyone do things on the 'fly' when you can properly plan out a table by using Enterprise Manager taking the time and effort to plan and test first?
Or am I missing something?
Jim
February 6, 2002 at 3:25 pm
You will see this a lot with SQL Server code as I can script everything I pretty much want you to do so you can more easily go thru the steps verbatum. I can give you the exact details of a table, view, or procedure. In many cases these scripts are the ones SQL Enterprise Manager outputs when you right click one of these items and do copy. Most people use Enterprise Manager or some other GUI to build the tables. This is more for the readers benefit than people actually doing it thru code. That is not to say you cannot, it is primarily used to provide the reader the details.
February 6, 2002 at 3:51 pm
short answer, laziness. Even for testing, it saves time in EM.
Personally, I have a template in QA that I use to outline a table and fill in columns.
Steve Jones
February 6, 2002 at 9:20 pm
As has already been hit upon, if presenting examples, it's easier to give a person a bit of code to copy, paste, and execute rather than give a list of steps to carry out through EM to create the same table. It's faster to recreate the example and it's less prone to error.
Second, depending on the complexity of the database structure and the tables, I'll naturally go to Query Analyzer to build tables in code. Unless it's extremely complex, I'll be in QA. This probably comes from my days operating on DOS and Unix, and from the simple fact that I got my start coding in simple text editors before the IDEs started rolling around in the QuickBasic 4.0 and Turbo Pascal era. I am completely comfortable at the "command line" and often find in my new duties managing servers and domains I'm executing command line utilities because there aren't equivalent GUI tools (for instance, the PSTools suite from http://www.sysinternals.com).
Third, one case where you'll see code is for the purposes of change control. It doesn't matter if the code was created by someone typing in through Query Analyzer or operating through a GUI like Enterprise Manager. I can script a database and store it in Visual SourceSafe or whatever change management control system I have. Then, I can script the database at a later time and compare to see what changes might have occurred.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 7, 2002 at 7:39 am
Hmmm...
Thanks fellas for your input. While I don't believe that such a methodolgy will ever replace my own table building methods, I do understand the 'why's' of same.
Seems logical, actually...and as stated quite easy for 'newbies' (hey, that's me!) to learn from...
Tip-O-the-Hat!
Jim
February 7, 2002 at 9:11 am
I don't think there are a whole lot, either. Most of the DBAs around me operate from EM. I usually have QA open (EM not necessarily so) so if I'm coding an example or what have you, I'll go ahead and type it out in QA as that's pretty fast.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 7, 2002 at 10:24 am
Let em add one more qualifier. If I had a good design tool, then I'd use that. At a former job, we had Er-Studio from Embarkadero and I did all table design there and it would generate scripts that worked al most all the time.
Steve Jones
February 7, 2002 at 10:36 am
Hmmm...
Am I then to assume that EM is NOT a good tool for creating dbase tables?
Anyone know of such a tool? What do most of you use IF you use such an item...
Jim
February 7, 2002 at 12:09 pm
EM can be used to create database tables and it works well enough. However, there are other products out on the table that do a lot more.
This goes back to a previous thread where we were talking about various backup solutions (not how to backup databases, but just what everyone was running) and most of us are on Veritas or ArcServe. However, I believe Steve has a friend who swears by the NT backup and a little bit of scripting. It does everything he needs and hasn't been an issue. It's a working solution that's free. Can't beat that.
A little of the same thing with Enterprise Manager. EM works. It's free (since you've purchased SQL Server). But there are 3rd party products out there that are pretty sweet, just with the price tag.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 7, 2002 at 12:35 pm
Okay, Brian...
And where would one find a list of such items -- that I know did NOT come with the MS package...
Jim
February 7, 2002 at 12:50 pm
Actually look close cause Microsoft is pushing for user on Visio, they even have a version built .NET architech. But EM is good for building and tweaking tables especially in diagram which is quite usefull to those reading the database. But there are numerous packages out there depending on what you want to spend.
February 7, 2002 at 1:12 pm
Visio is one of those designers you either love or love to hate. Most of the folks I work with fall into the latter. Rational Rose will also to database modeling, but won't do anything at the physical tier.
If you're looking for a list of products, a good resource is to go to your local techie newstand and pick up the latest SQL Server magazine. As with any trade journal, a ton of ads, all telling you how their product will do things the best. Most of the companies will allow you to have free trial versions for a chance at your business.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 7, 2002 at 1:16 pm
And if you are a business looking you can get most of them to send someone out to demo it, explain it and give you and overview of what they offer. I would look into something for the long term not the here and now.
February 8, 2002 at 10:50 am
ERWin is nice, but $$$$$, ERStudio is almost as good and less, but still $$$. Visio is $$ and it shows. It is cumbersome, IMHO, and seems to reach a point where it kind of flips, especially when I group items. I have had to trash my existing digram and reverse engineer and rebuild twice in two years.
Steve Jones
February 8, 2002 at 4:18 pm
I know I'm late coming into this conversation, but thought I'd add my opinion.
I like using Enterprise Manager and have no desire to seek out a better tool. I develop most of my tables, indexes, jobs, and DTS packages using Enterprise Manager and have no complaints.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply