Commands and Abstractions

{

I clash with people occasionally over my approach to SQL Server. My approach to SQL Server is quite simple: Transact SQL. Never one for layers of abstraction and always seduced by speed, I prefer to keep my management activity in Query Analyzer. SQL Management Studio seems a bit slower than its older cousin, so quite often I prefer Query Analyzer even with the brand new SQL Server 2005.

Often times for me preferences like this develop after bad experiences but today was a reminder of what kinds of things a layer of abstraction is capable of doing behind your back if you don't monitor it. We have a table with 82 columns and something like 50,000 rows. One of these columns needed to be extended from VARCHAR(10) to VARCHAR(50). I was walking through the steps as they may occur in Enterprise Manager so I opened the product (wait a few seconds), selected my database (wait a few seconds), opened the tables node (wait a few seconds), and then right clicked on my table to examine and manipulate the column (another few seconds, plus scrolling up and down to locate the column I wanted). I made my change and then proceeded to make a "change script" which, to my horror, creates a temp table, copies data and affects other tables referenced by the table whose column I was changing.

My usual approach to something like this is the following: Start->Run (Instantaneous). "isqlw" and hit enter (Instantaneous). In the query window I type the following:

1 USE MyTestDatabase

2 GO

3

4 ALTER TABLE Foo

5 ALTER COLUMN Bar VARCHAR(50)

6 GO

This is a simple example, titled away from the GUI but there's more to this. Because the GUI represents an abstraction, there are limitations a person who is there encounters and begins to operate with. A simple example is the following: assume a table has 3 stored procedures (select, insert, update) and you've added a few columns that you'd like to include as parameters in each of the procs. In Enterprise Manager, or SQL Management Studio, a person is inclined to think in terms of a single procedure at once and likely to visit each procedure to make this change. A wise Unix gray beard or someone else who's comfortable with a text editor will dump all the procedures they need to modify into a single file, do some search/replace operations as a single step, and do the update.

The point for me really veers from SQL Server at this point and gets into the question of the abstractions which I myself rely on unnecessarily - which prevent my thinking to get past a certain point. Although I have come to rely on the command line more and more over time, there are still things I learn: DOS things! that have really changed how I think about certain types of problems. A simple example of this is from a few months ago when I learned of the existence of pushd and popd, commands for storing and retrieving working directories on a stack. These relatively trivial techniques make it easy to do things that never really occured to me before. Like the fact that I could just use fc to check for differences in files. Or piping a request for help to a file so I could have documentation for a command. Here is an example that might be interesting: wsdl.exe /? >wsdlParameters.txt

One last point of clarification: I am not against abstractions. In many instances they work quite well. A while back I wrote a web based tool for viewing objects in SQL Server databases - basically outputting queries to the Information Schema views. Everything that tool does can be duplicated in query analyzer or in SQL Management Studio. But in terms of raw speed, flipping back and forth on table definitions in the web based tool beats clicking around in the GUI or retyping the same select statement with a different table name in the where clause. What I believe is that abstractions are used best when what they are hiding is understood fully. This is counter intuitive; most people think the abstractions - an easy GUI, a simple command, and so on - are there to make things simpler for people who want to keep details hidden. That is always dangerous, even when it's effective. I'd prefer to struggle with what's underneath and then live blissfully choosing the abstractions I find useful.

}