james mckay dot net

I’ve been working a bit lately with a project that uses SQL Server 2005 database projects in Visual Studio 2008 Team System. These are different from the conventional database projects that you get in Visual Studio Professional, since they have extra features that allow you to do schema and data comparisons, and, in theory at least, manage database deployments and migrations.

The idea is that you should be able to design your database using visual designers rather than having to write all that nasty SQL code to script it for you. Visual designers make things so much easier at the planning and initial design stage, and once you are done, you can use the various schema comparison and script generation tools to generate your production database.

The problem comes when you want to manage your database’s entire lifecycle. I’m sure that many developers will have scratched their heads at some stage about this problem. You chop and change your database on your development server, most likely using the visual tools—but how do you reliably replicate these changes on your live server?

The Microsoft approach here is to rely on the schema comparison tools to generate change scripts that you can then run against your database. Some people think it’s a silver bullet. I beg to differ.

The first problem is that while schema comparisons can make a good starting point, the scripts they generate don’t always work properly out of the box, if at all. Some database refactorings simply can’t be done using schema comparison tools. Examples include normalisation refactorings such as moving data from one table or column to another; introducing constraints or changing a column’s data type when you need to do some data cleanup first; or modifying reference data. Even relatively straightforward refactorings—or even, in some cases, no refactoring at all—can be problematic: if your production and development databases get their collation orders out of sync, for instance, the script may refuse to run at all. And the thought that anyone would blindly use this option on the project properties page makes me shiver:

In other words, you’re asking it to guess what’s changed.

Testability—and when you’re dealing with an abstraction as leaky as this one, testability is vital—is another issue. Unfortunately, SQL Server 2005 database projects have serious shortcomings in this area too. They do offer unit testing features, but these only apply to the final database. There doesn’t seem to be any way of integration testing your migrations themselves: you don’t have a consistent record of what’s changed in a format that can easily be applied to a blank or reference database, so there’s no way of verifying that you’re getting the expected results when you’re going from “before” to “after.”

Then there are the change scripts generated by schema compare itself. They are a morass of long winded, convoluted, hard to maintain, spaghetti code. Adding a column to a database table involves dropping and re-creating the table: this is understandable if you need to put the column in the middle of the roster, since SQL Server does not have an AFTER clause in the ALTER TABLE statement like MySQL does, but even if you add a column on to the end, it still drops and re-creates the table. A task that needs only one or two lines of code ends up taking eighty. If you rely on schema comparison tools, sooner or later you are going to need to edit your scripts, and when that happens you’ll find that you’d have been quicker just writing the change that you needed by hand in the first place.

All in all, this seems far too leaky an abstraction to give me any confidence in it to manage a database lifecycle. There is simply no substitute for scripting every database migration, checking it into source control, and having your unit tests run them all on a blank, or reference, database, and having some record in your production database of which scripts have been run and which haven’t. And while schema comparison tools may be a life saver if you lose track of things for any reason, they are a very poor alternative to generating your migration scripts by hand.

Comments (1) »

Posted at 08:00 in SQL Server

Scott Mitchell gave some instructions on 4GuysFromRolla a while back on how to list the sizes of all the tables in a SQL Server database.

His solution uses a mixture of SQL and ASP.NET, but some people will want an alternative in pure SQL. So, with no further ado, here you go:

create procedure sp_get_table_usage
as
begin
    create table #t (
        name varchar(100),
        rows int,
        reserved varchar(100),
        data varchar(100),
        index_size varchar(100),
        unused varchar(100)
    )

    declare @name varchar(100)

    declare c cursor
        for select name from sysobjects where type='U'

    open c
    fetch next from c into @name
    while @@FETCH_STATUS = 0
    begin
        insert into #t
            exec sp_spaceused @name
        fetch next from c into @name
    end
    close c
    deallocate c
    update #t
        set reserved = rtrim(replace(reserved, 'KB', '')),
            data = rtrim(replace(data, 'KB', '')),
            index_size = rtrim(replace(index_size, 'KB', '')),
            unused = rtrim(replace(unused, 'KB', ''))

    alter table #t
        alter column reserved int
    alter table #t
        alter column data int
    alter table #t
        alter column index_size int
    alter table #t
        alter column unused int

    select * from #t
        order by name
end

You can change the sort order by changing the order by clause at the end. For instance, order by data desc will list them in descending order of size.

Comment on this »

Posted at 18:12 in SQL Server