The journal of Paul M. Watson.

Wednesday, January 11, 2006

Execute, not save

I am not sure I like the change to how stored procedures are edited in Microsoft SQL Server Management Studio. Back in Enterprise Manager for SQL Server 2000 you edit stored procedures directly in the database. You opened it, changed some T-SQL and saved it back to the database.

With SQL 2005 though you never open anything. You are instead running scripts against the database. So to create a stored procedure you run a CREATED PROCEDURE script and to make changes you run an ALTER PROCEDURE script.

It makes sense in some ways but when I first hit Ctrl+S to save some changes and it asked where to save the T-SQL script on my machine I was confused. Only then did I see the Execute button and the ALTER line.

Frankly the way databases work from a programmers perspective is never optimal. No code files to edit or stick in a code repository. You can't edit locally, compile, run tests and then only commit to the actual database. etc.

But then databases store data so it is difficult to create a system that is optimal for data storage and retrieval and for developers. Hopefully someone smart figures it out one day.

4 Comments:

Anonymous Steve Crane said...

The way I work is to have a local copy of our development database. I write my own scripts in a text editor as I need new database functionality. I then run these to update my local database. When I've finished developing and am ready to commit the changes to the development server I can run my tested and debugged scripts to update it. We have an automated upgrade procedure of our own creation that extracts code and table changes from the development database and prepares scripts that can update production databases.

BTW what is this Enterprise Manager you spoke of? :-)

11:54 AM

 
Blogger Paul Watson said...

That sounds similar to what we are doing though the "automated upgrade procedure of our own creation that extracts code and table changes from the development database and prepares scripts that can update production databases" is the tougher bit.

Enterprise Manager was the MMC snap-in that managed SQL Server 2000.

12:46 PM

 
Anonymous Steve Crane said...

When you have hundreds (or thousands even) of databases installed in 30-odd countries you put a heavy focus on correctly upgrading the database as part of a new release. It gets really fun if your upgrade destroys customers' databases. Particularly as many of them cavalierly ignore the fact that you gave them an easy way to backup and stressed that they should use it. Backups? What backups?

I was making a joke about Enterprise Manager, which actually first appeared with MSSQL7, or perhaps even 6.0 or 6.5. I have worked with MSSQL at one level or another since 4.2 when all there was was a query analyzer type tool.

2:52 PM

 
Blogger Paul Watson said...

I should have realised you were being sarcastic :-D

As for Query Analyzer I really wish SQL2005 came with it. It has always been handy to have a fast, lightweight query app. At least QA from SQL2000 works fine against SQL2005 dbs.

2:56 PM

 

Post a Comment

Links to this post:

Create a Link

<< Home