From time to time, I’ll run into a situation where I need to compare database schemas or data from two different databases. This usually happens when a developer makes changes to a development database and neglects to keep track of those changes. When the time comes to push to production, it’s hard to remember all of the changes and if something ends up getting left out, you have to spend time tracking down what was missed.

This use to happen to us a lot until we started using scripts to create and update databases.

Those new to SQL Server performance tuning or database indexes in general are probably confused by the differences between clustered and non-clustered indexes. Usually, there is a performance problem that prompts research and you finally have to "bite the bullet" and look into what the hell these index types are.

What is a Database Index?

When I went to install SQL Express the other day, I couldn't find Full-text indexing. Our immediate reaction was that Microsoft must have removed it as a "free component" in SQL Express. But after some further checking, it turns out that Full-text indexing is still available.

There are actually 2 versions of SQL Server 2005 Express. Both are free to download. You can download all versions here:

http://www.microsoft.com/express/2005/sql/download/default.aspx

One nice way to build "documentation" directly into your database, you can add extended properties to your tables to describe the columns. This is useful because this data can be displayed in your database diagram.

NOTE: This has only been tested in SQL Server 2005 and 2008.

Normally, to display / manage the descriptions of your columns, you use the SQL Database Diagram tool. Here's what my Database Diagram for my simple database. By default, the "description" field is not displayed.

Here is a useful script I've been able to put together from multiple posts online that gets the estimated index fragmentation for all tables in your database where the fragmentation percentage is greater than the where clause. Here's the SQL:

select object_name(itable.object_id) as tablename,

itable.name as IndexName,

indexfrag.avg_fragmentation_in_percent