Scripting your database first versus building the database via SQL Server Management Studio and then generating the script

Question

I had a (friendly but heated) argument with my lead developer the other day because our project has TSQL Scripts that I code directly into SQL files which I then run against the database. I find that when I do this, it's easy to work out the schema in advance without fiddly pointing and clicking and then there's no opportunity to forget to generate a script to put into source control as generating the script no longer becomes a chore you have to do after the fact, but is an implicit part of the process (and also leads to cleaner scripts without the extra crap that SQL Server Management Studio inserts into the scripts it generates).

My lead developer insists that having to manually script it out is a pain in the arse and that he absolutely refuses to write his scripts by hand when there are perfectly good tools to do it without coding. I've noticed that the copying of his changes into the actual scripts tends to get delayed a bit as a result though.

What are your thoughts on the pros and/or cons of doing it one way vs the other? Am I being too rigid/old-school in my sticking to hand coding schema scripts or is he being too reliant on third party tools and losing something in the process?

Answer 1

I find its as quick to write a script as it is to use SQLMS. You still have to type names in SQLMS, and the time spent moving from keyboard and mouse could be used writing the proper script anyway.

Answer 2

As I usually collaborate with a colleague during the schema design, I tend to design the schema using the GUI tools, as its easier to discuss it with a diagram of the tables in front of you. I then generate the scripts, being careful to select the exact options that I want to avoid having to make manual changes post-export.

Answer 3

I think a decision on the relative merits of the two approaches might take into account factors such as

• the frequency of changes to the schema
• the frequency with which changes need to be propagated to other schemas (test, user acceptance, production, clients * n, etc)
• the degree to which the schema may vary across development branches
• how well-known in advance your various changes can be scheduled
• whether or not you can generate SQL "diff" scripts between schemas.

On balance, I tend to prefer to work with a script for each change (or "migration"). It lets me resequence change releases as priorities shift.

Just because you can create tables in a graphical tool doesn't necessarily mean you should.

Answer 4

The two of you are almost working with two sets of code. Consistency seems to be a key factor on these types of decisions. In your case, if you create a script, your boss uses the gui to add a field, how do you stay in sync? You can't use your script to rebuild the table without editing it (Chance for error.).

Maybe he should pull rank and force you to format your scripts the same way the GUI creates them - just kidding.

Answer 5

I always script stuff myself because the wizards sometimes don't script things in a way that I like it and will also give funky names to defaults

scripting things yourself is also good in case you get laid off and you have to go for an interview where they ask you to script DDL on the whiteboard

Answer 6

I think you should flip on it..........