February 2007 Blog Posts

  • The Abstraction Indicators - A Database Design Measure

    Before I kick this one off, please remember that these measures can only be taken into consideration when the full scope of the database model and its implementation are known.  The Abstraction Indicators try to measure the databases capacity to cope with change and the "profile" of the database to calling applications.  Abstraction in this context means to hide internal schema changes from users. These indicators are relevant to the entire database only and must be taken with a huge grain of salt. There are 2 components View Abstraction I won't...

  • The PUC Index - A Database Design Measure

    In following from my previous post, and in the interests of a free shirt from graz, I present another metric designed to summarise the "goodness" of a physical database schema The Potentially Useless Column (PUC) Index analysis the number of NULL defined columns versus the total number of columns.  The PUC Index has 3 indicators (TotalColumns, NULLColumns and Index) and applies to individual Tables and the Database Interpreting the results is very straight foward.  Any result in either the Table or Database with a PUC Index over 0.00 must be investigated and justified.  If I had to give a general guidline..... Any database that scores...

  • The Hermetic Ratio - A Database Design Measure

    Over the years, several of my contracts involved analysing and critiquing third party databases. On arrival at the client site, I would be given the business model specification, a copy of the database and a room/cubicle to work in.Obviously depending on the complexity of the model, it could take me weeks to give a full analysis. Mostly the report would be centred around normalisation, data integrity, scalability and the quality of the implementation of the business model within the database. On final delivery of report, the client (usually the CIO or CEO) would take me out for a coffee and discuss the results....

  • From the code library - Checking Index health

    Analysing index health is a vital part of database maintenance. Wading through DBCC SHOWCONTIG results is not fun at all. You probably have something similar but I thought I would share. --Stub to execute DBCC ShowContig create proc zzShowContig as set nocount on dbcc showcontig with tableresults , all_indexes go --The table to hold the results create table zzShowContigResults (ObjectName sysname, ObjectID int, IndexName sysname primary key , IndexID int, Level int, Pages int, Rows int, MiniumumRecordSize int, MaximummRecordSize int , AverageRecordSize money, ForwardedRecords int, Extents int, ExtentSwitches int , AverageFreeBytes money, AveragePageDenisty money, ScanDensity money ,...

  • 7 Downsides to IT.....

    While I love lots of different facets of IT, there are a few downsides that I have experienced over the years and I thought I would share... The respect! Don't you just love how the corporate machine has diminished our trade?  Considering how much power we should yield in the modern world, how the hell did we let them treat us like scum sucking pigs?    When everything is running smoothly, we are not noticed.  When the shit hits the fan, we are...

  • Using WITH RECOMPILE?

    Most SQL Server based apps use stored procedures exclusively, so the idea of forcing SQL Server to ignore plan caching is nasty. According to the BOL, it is "unusual" to use this option. But I think I have one... My application has an end user designable reporting component. Put simply, the end user is allowed to create and design reports at runtime.  Users are allowed to either construct queries or browse from a predefined list.  Using the resultant dataset, they can then design a report. I know some of you would be horrified by the fact that users are allowed to write their own...