Database versioning

Since I have abandoned Visual studio database edition because of it not being able to handle tables with data I have returned to my old, proven, way; checking in/committing the update scripts and storing a database version number in a Setting table.

In Microsoft SQL Server Management Studio (or whatever it was called earlier) there is a setting to always create update scripts whenever a change is saved.

Then whenever a change is done, store the update scripts with consecutive version numbers.  These versions are not the same as the release versions but one version number per commit.

013_01_S_Customer_Added the CompanyColour field.sql
013_02_D_Customer_Set the CompanyColour to red except blue for McCarthysen.sql
013_03_S_Customer_Set CompanyColour to notnull and no default value.sql
013_99_D_Setting_Updated database version.sql

013 is the version number.  Next commit is 014 etc.
01, 02, 03 are consecutive numbers.
99 is the final number for this commit.  It always contains an update in the Setting (or whatever you call it) table and sets the version number record to 13.
S and D are Schema and Data respectively.  I have learned that it is good to see this already in the script's filename.
Customer and Setting are the names of the main manipulated tables.
The rest is free text that explains what the script does.

It is now very easy to see how far a database is in the development flow.  Select the the row from the Setting table and notice its number.  Then run all scripts with higher version numbers in the right order.  The scripts are easy to order by their names directly in the version manager or in the file explorer after a checkout/getlatest.

This technique works well with several developers.  Store the scripts locally until they you are ready to commit the code.  Update the version number if someone already used the version number.  Commit.

A tool like SQL compare does approximately the same job.  If you have only a few updates with a few databases it is way faster to just create a diff-script and run it.  Contrary to VSDatabase edition SQLcompare handles tables with data.  It costs money but 1) there is a trial version and 2) it is way cheaper than hacking the code yourself.
There are other tools as well and I have used one but I cannot remember the name of it.  It was not as easy to use as SQLcompare if I recall correctly.

If you have to handle several versions while developing the procedure I have described here is good since it is so easy to check the version of the database.

(I just stumbled upon a situation where saving a view and only updating the output fields from lower to upper case (customername -> CustomerName) did not result in a script file.  In this case there was no problem in scripting a drop/create script through the Object explorer tool box though.)

Unable to open the physical file Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)". (Microsoft SQL Server, Error: 5

Try to attach a database with Management Studio when using Windows 7 and SQLServer 2008 developer edition and one might get an error message similar to the title of the article.

Whether this is a bug or not is beyond my knowledge but the solution/workaround is to connect as SA and not as windows authentication.

Visual studio database edition - how to think to get started

Visual studio database edition is part of the "normal" Visual studio.  One cannot be totally sure of this since Microsofts licences are as comprehensible as scientology.

I made two tries at it before I got it to work.  Then it failed me anyway, but that is another posting.

The way to think is to think about the database as source code.  You make changes, compare them to the checked in/commited code and let the machine do its magic merge and check in the result.

Visual studio net database edition does not handle data

The title is not totally correct; it does handle data. But it cannot do version management on your database if you have data in it properly.

Like this:
A year into the project you/the customer realize that a Customer can be split into InternalCustomer and ExternalCustomer and a decision is made to make Customer the base class and the other two children.  An accordingly correct decision is made to create two new tables in the database and have them have their corresponding fields.

Technically, or database script wise, this means
1) create two new tables
2) select data from the Customer table into the two child tables <- oops
3) drop some fields in the Customer table

Visual studio database edition can handle 1) and 3) but there is no way to tell it to run a custom script in between.

This unfortunately makes the product useless for me.

Slow sqlserver express

I got informed of something somewhat strange the other day.  Microsoft SQLServer Express runs (connects) faster if you have a query window to the database already.

Like this:

Have a freshly booted machine with only Visual studio running and your web application of choice.  The time for a refresh of you web page might take 5 seconds.

Now start Management Studio and open a query window.  Let it stay open.
Refresh your page and you are down to a second.

The figures might of course change but they are what I have on a dual core 2GHz 4MB machine for a project of size X and database of size Y.

----

I haven't checked if the same thing happens with another script editor like for instance AnySQLMaestro but guess it does.  I guess it has to do with connections - as long as there is a connection to SQLExpress, it doesn't close.  If so, it corresponds with the product's raison d'être, a database for applications.

----

I have noticed a correspondence between debug-start-delay and quality of code.  I say that every second wasted waiting on debug start makes the developer unwilling to test more thoroughly and leads to more bugs in the product.

----

Another trick to make web debug start faster here.

Select distinct is considered bad

Correction; Considered a warning.

Here is a good rule-of-thumb: If you have to write an SQL with Select Distinct, do adjust your chair, get a cup of coffee, walk around the office but above all talk to someone else because Select Distinct is a symptom of something being wrong, either in the database or in your solution.

/* Select Distinct is normally not needed when your database is lagom normalized. But if you do need a Select Distinct then this is a very good example of a situation that needs to be documented.*/

Managing versions of a database

There are in SQLServer2005 ways to version control it through tools by Microsoft. Since I havn't had the time to learn how to and some of us use other databases there is a common technique that works so so but at least gives us a chance of reverting to an older version.

Any time I decide that this is version x.y. I check in the database as binary. Image or backup doesn't matter as long as it is recreateable without anything else.
After that all updates must be scripted. These scripts are checked in in the same folder as the database.
One can then get the database and use the date stamps on the scripts to get the exact version of the database.

After a while there are too many scripts so we check in the database again.

One then sorts the files on date and starts by getting the database. Then run the scripts to the proper version.

This is not a perfect way; it is easy to forget to script a change. But it is a start at least.

Use stored procedures

Some people complain about stored procedures as bad because they inject business logic into the database.
My answer to this is that then the stored procedures are used the wrong way.

Stored procedures should be used to get data in and out the database.  Used this way they represent the holes in and out of the database.
Name the stored procedures cunningly and you have an easy way to find the only ways to insert data into the customer table, the only way to update the order table and only ways to select multiple rows from role.

I have not found the perfect naming scheme.  Anyone?

What to choose for primary key in a relational database

Anything simple.
Anything simple but not something the user knows about.

A 32 bit integer, a GUID, a string. But not the CustomerNumber or PostalCode or EmailAddress. No matter what the user/customer/client says - those things are not unique. I have been harrassing customers whether the CustomerNumber is unique or not. It has been unique for two weeks until someone remembered about foreign offices with their own numbering system or that they sometimes are changed.

Alas - do not use anything you find in the business logic as primary key.