Standards and best practices are like flu shots you take before you're infected; Database best practices protect your databases from bad things. But, we all make mistakes. It could be because we're on a time crunch, or we're lazy (which I'm guilty of by the way), or maybe it's part of being a developer.
Common mistakes include: tables without a primary key, column name problems, missing foreign keys, etc., This is where I love LTD's very own SQLCop. I can quickly go on with my database development and rely on SQLCop to detect the issues. It saves time and ensures that database standards are met.
However, there are some issues explicit to data warehouses that SQLCop doesn't look for. I list those issues below and provide scripts to detect them. I use these scripts in conjunction with SQLCop.
Detect tables in a data warehouse that aren't prefixed with either Dim or Fact:
Tables in a warehouse are generally prefixed with Dim and Fact for dimensions and fact respectively, to easily distinguish them.
- SELECT [schema_name] = s.name ,
- table_name = t.name
- FROM sys.tables t
- INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
- WHERE t.name NOT LIKE 'Dim%'
- AND t.name NOT LIKE 'Fact%'
- AND t.TYPE = 'U';
Find tables in a data warehouse that don't have a primary key:
Like in OLTP databases, all tables in a data warehouse also should have a primary key defined.
- SELECT schema_name = SCHEMA_NAME(schema_id) ,
- table_name = name
- FROM sys.tables
- WHERE OBJECTPROPERTY(OBJECT_ID, 'TableHasPrimaryKey') = 0
- ORDER BY SCHEMA_NAME(schema_id) ,
- name;
Detect dimension tables with a composite primary key:
A composite primary key on a dimension table causes degraded performance. It is best to create a single column primary key.
- SELECT c.TABLE_NAME ,
- COUNT(*)
- FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
- INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
- WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
- AND c.TABLE_NAME = pk.TABLE_NAME
- AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
- AND c.TABLE_NAME LIKE 'Dim%'
- GROUP BY c.TABLE_NAME
- HAVING COUNT(*) > 1
Detect dimension tables that don't have Identity column as a primary key:
Usually, surrogate key is made the primary key of the dimension table. Surrogate key is an auto generated Identity value.
- SELECT dim_table = t.name ,
- primary_key = c.name ,
- c.is_identity
- FROM sys.tables t
- INNER JOIN sys.key_constraints kc ON t.OBJECT_ID = kc.parent_object_id
- INNER JOIN sys.indexes i ON i.OBJECT_ID = kc.parent_object_id
- AND i.type_desc = 'CLUSTERED'
- INNER JOIN sys.index_columns ic ON ic.OBJECT_ID = kc.parent_object_id
- AND ic.index_id = 1
- INNER JOIN sys.columns c ON c.OBJECT_ID = t.OBJECT_ID
- AND c.column_id = ic.column_id
- WHERE t.TYPE = 'U'
- AND t.name LIKE 'Dim%'
- AND kc.type_desc = 'PRIMARY_KEY_CONSTRAINT'
- AND c.is_identity = 0
Detect primary keys that don't follow the naming convention:
- SELECT dim_table = t.name ,
- primary_key = c.name
- FROM sys.tables t
- INNER JOIN sys.key_constraints kc ON t.OBJECT_ID = kc.parent_object_id
- INNER JOIN sys.indexes i ON i.OBJECT_ID = kc.parent_object_id
- AND i.type_desc = 'CLUSTERED'
- INNER JOIN sys.index_columns ic ON ic.OBJECT_ID = kc.parent_object_id
- AND ic.index_id = 1
- INNER JOIN sys.columns c ON c.OBJECT_ID = t.OBJECT_ID
- AND c.column_id = ic.column_id
- WHERE t.TYPE = 'U'
- AND t.name LIKE 'Dim%'
- AND kc.type_desc = 'PRIMARY_KEY_CONSTRAINT'
- AND c.name <> REPLACE(t.name, 'Dim', '') + 'Key'
Detect fact tables that have no foreign keys:
Without a foreign key, a fact table isn't really a fact table.
- SELECT table_name = t.name
- , fk_count = COUNT(*)
- FROM sys.tables t
- INNER JOIN
- sys.foreign_keys fk ON t.OBJECT_ID = fk.parent_object_id
- WHERE t.name LIKE 'fact%'
- GROUP BY t.name
- HAVING COUNT(*) < 1
Detect fact tables that have foreign key(s) to another fact table:
It's unlikely to have a fact table related to another fact table.
- SELECT foreign_key = fk.name ,
- child_table = t.name ,
- parent_name = rt.name
- FROM sys.foreign_keys fk
- INNER JOIN sys.tables rt ON rt.object_id = fk.referenced_object_id
- INNER JOIN sys.tables t ON t.object_id = fk.parent_object_id
- WHERE rt.name LIKE 'Fact%'
Detect missing foreign key(s) in fact tables - Columns suffixed with Key, but don't have foreign key constraint:
I stole the following query from here posted by George Mastros, and replaced ID with Key to use it for data warehouse scenario.
- SELECT C.TABLE_SCHEMA,C.TABLE_NAME,C.COLUMN_NAME
- FROM INFORMATION_SCHEMA.COLUMNS C
- INNER Join INFORMATION_SCHEMA.TABLES T
- ON C.TABLE_NAME = T.TABLE_NAME
- And T.TABLE_TYPE = 'Base Table'
- AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
- LEFT Join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE U
- ON C.TABLE_NAME = U.TABLE_NAME
- And C.COLUMN_NAME = U.COLUMN_NAME
- And U.TABLE_SCHEMA = C.TABLE_SCHEMA
- WHERE U.COLUMN_NAME IS Null
- And C.COLUMN_NAME Like '%Key'
- ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME
Results of above queries aren't always issues. They are just rare, you've to look at them closely and make sure there is a reason for each choice. Also, you may use different naming conventions that make these queries void. In that case, I hope you're able to alter them to your needs.
Follow me on Twitter! @SamuelVanga
