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:
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