Sep 30 2011

Avoid overloading Meaning to Existing Database Column

I’ve always found that when defining a database table it is always best to create a integer primary key, even if a unique key such as social security number, ISBN, or some other business value. Recently I had to go through the unpleasant process of updating a database table and associated scripts, resources, and code that had used a business attribute as the primary key but because of business requirements it was no longer unique and had grown to have different meanings. Because your business requirements will change, don’t use business attributes as primary keys for you database design. In addition, don’t overload attributes to more than one meaning. For example, the database that I was working with had a database column called Sequence that functioned as the primary key, the line number, and a workflow execution order to process the data. It is a source of confusion and bugs to overload one attribute to so many different meanings.


Sep 16 2011

Create SQL Server Database and Database Tables From a File

When working with a database, it’s is always handy to develop a series of scripts to backup, restore, create, move databases around. As of late, I’ve been working with SQL Server a lot so I’ve come to appreciate the sqlcmd utility.

You can run the sqlcmd utility from the command line and run a input sql script and capture the output in a file. The input sql script file can run any sql statement, such as creating a new databases, views, tables, or procedures. You can also restore and backup any given database or whatever else you require.

To run the sqlcmd utility and have it read a file, createTables.sql, you can run something like the following from your command line, of course if you have SQL Server Management Studio and sqlcmd installed.

sqlcmd -S localhost -d dbName -U user -P password -i createTables.sql -o output.out