CHECKDB WITH DATA_PURITY

I’ve Learned this recently, about two months ago.

Again on Paul Randal’s blog, click here if you want to see the post.

The thing is SQL 2005 introduced a check on DBCC CHECKDB, that checks for columns values like, is my column values within range of my selected data type?

When migrating databases I’ve seen some negative DateTimes (which Query Analyzer does display, some weird float values that even query analyzer does not display)

The First question you might have is how the hell did this values get in there?
Well… On SQL 2000 and bellow, SQL allowed data Import without some checking with ended up with those values in there.

If you created the database on SQL 2005, this check is always done when you issue a:

DBCC CHECKDB ([db_name])

if your database is from a SQL 2000, you may have to perform a explicit check, and once this check is completed successfully a value is changed in the database boot page, so SQL know that it passed once and will enforce checking every time.

Here’s the code for an explicit check:

DBCC CHECKDB ([db_name]) WITH DATA_PURITY

Remember to change the db_name for your database name, ok?

If you got any problem with the invalid values know you have two choices, and I don’t like any of them.

1. You can delete those rows. (lame solution right).
2. You can update the values for those rows, but probably you won’t be able to see what’s currently stored if you get this problem.

For more information around this you can check this MS KB. 

Generating a Table’s diagram and Script

Great tip for Entity-relationship model and script generation.

A great tool for building Entity-relationship models for databases and it’s scripts

http://ondras.zarovi.cz/sql/demo/

it generates mysql, sqlite, web2py, mssql (Microsoft sql), postgresql, oracle, sqlalchemy, vfp9 scripts for a designed model.

You can select the language in the options, and then start designing the database, or do that in the end when you want to generate the full script, this helps you to create a ERM model and visually see the relationship’s test your ideas and see if they really work.

Creating Tables

You can start by creating tables using the menu Add Table button, you may be tempted to drag a table, but that won’t work, just click the diagram and there will be a dialog asking you the name of the table. The table comes with an default id field, you can edit it, changing it’s type by double clicking it, then selecting it's name, type, size (when applicable), if it is a auto increment field, and if it allows nulls. You can add new fields with the Add field button also in the Menu and create new fields on the desired table.

Creating Relationships

Relationships can be created in two ways in here, first you can create the destiny field in the desired tables and then select which one is part of the key (only keys can be related)  then click Connect foreign key, and the connection between the fields will be created (and also the foreign key). The other way of doing it is by only creating the field on the source table and then selecting the field (must be part of the key),  clicking on the menu Create foreign key and then selecting the table you want to have the key, the field should be created automatically.

Setting a Primary Key

When you select a table you can use the menu to define it’s unique key (a table doesn’t need to have one, but its highly desired), you can click the Keys menu item, there you can define which columns are part of the primary key, moreover you can also define indexes, and which columns are part of the index, however I don’t think you can create an index that includes some columns but are not part of the index.

After that you can select Save/Load and Generate SQL for the script.

Also one nice trick is that you can download this to work locally, click on documentation and Installation then follow the instructions and use.

Here’s a diagram example:

Hope this helps
Good Luck
The Developer Seer.