Data Warehousing Tip: T-SQL Script to Create DEFAULT Constraints by Data Type


There is an ongoing discussion/debate regarding the handling of NULLS in a data warehouse, however, the following article by Michelle Poolet summarizes it beautifully – “consider the data warehouse business requirements” (see http://sqlmag.com/database-administration/null-data-warehouse).


Beyond consideration, there are some data types that should get the DEFAULT treatment (bit, char, nchar, nvarchar, and varchar) and the following script can assist in creating these across every applicable column in your database.


SELECT Object_schema_name(T.[object_id], Db_id()) AS [Schema], 
       T.[name]  AS [table_name], 
       AC.[name] AS [column_name], 
       TY.[name] AS system_data_type, 
       AC.[max_length], 
       AC.[precision], 
       AC.[scale], 
       AC.[is_nullable], 
       AC.[is_ansi_padded], 
       'ALTER TABLE dbo.' + T.[name] 
       + ' ADD CONSTRAINT ' + AC.[name] + ' DEFAULT(' 
       + CASE TY.[name] 
            --WHEN 'int' THEN '0' 
          --WHEN 'bigint' THEN '0' 
          WHEN 'varchar' THEN '' 
          WHEN 'bit' THEN '' 
          WHEN 'char' THEN '' 
          WHEN 'smalldatetime' THEN '' 
          WHEN 'datetime' THEN ''
          --WHEN 'tinyint' THEN '0'
          --WHEN 'smallint' THEN '0' 
          WHEN 'nvarchar' THEN '' 
          --WHEN 'money' THEN '0.00'
          ELSE '' 
       END +') FOR '+ac.name AS sqlstring 
   FROM   sys.[tables] AS T 
       INNER JOIN sys.[all_columns] AC 
               ON T.[object_id] = AC.[object_id] 
       INNER JOIN sys.[types] TY 
               ON AC.[system_type_id] = TY.[system_type_id] 
                  AND AC.[user_type_id] = TY.[user_type_id] 
WHERE  T.[is_ms_shipped] = 0 
       AND T.[name] LIKE ('DIM%') AND AC.[name] NOT LIKE ( '%SrgKey%' )  AND 
TY.[name] in ('varchar', 'bit' , 'char', 'smalldatetime', 'datetime' , 'nvarchar')

I’ve taken the approach of defaulting values for all columns (less those affected by the WHERE clause) in my STAGING Database.  The thought is that the non-NULL values inserted into my staging tables will persist to the TARGET database and I won’t experience one of a variety of issues (OLAP, T-SQL, or otherwise) that a development and/or user team will encounter due to having NULL values in the data warehouse.

 

T-SQL Tip: Sniffing out special characters in a table using LIKE

Here’s a handy snippet of code if you need to find special characters in a table. I had to implement this for a client after discovering that a third party export process was not correctly handling special characters (i.e. the per table export would flip question marks to upside down question marks…weird huh!). We needed to find a way to identify those tables that contained special characters and develop custom exports for these tables only. To address the aforementioned issue, the developer can use the SQL Server system tables to retrieve meta-data of their tables and auto-generate code similar to the snippet below for all applicable tables and columns (i.e. nchar, char, varchar, etc).

CREATE TABLE #SpecialCharTable

(
colA VARCHAR(50),
colB VARCHAR(50),
colC VARCHAR(50)
)

INSERT INTO #SpecialCharTable
VALUES      (‘A’,  ‘B’, ‘C’)

INSERT INTO #SpecialCharTable
VALUES      (‘A’, ‘B’, Char(63))

INSERT INTO #SpecialCharTable
VALUES      (Char(1), Char(2), Char(3))

SELECT colA,
colB,
colC
FROM   #SpecialCharTable
WHERE  ( colA LIKE ‘%[^0-9a-zA-Z ]%’ )
OR ( colB LIKE ‘%[^0-9a-zA-Z ]%’ )
OR ( colC LIKE ‘%[^0-9a-zA-Z ]%’ )

SELECT TOP 1 *
FROM   #SpecialCharTable
WHERE  ( colA LIKE ‘%[^0-9a-zA-Z ]%’ )
OR ( colB LIKE ‘%[^0-9a-zA-Z ]%’ )
OR ( colC LIKE ‘%[^0-9a-zA-Z ]%’ )

SELECT *
FROM   #SpecialCharTable
DROP TABLE #A_SpecialCharTable

Note: PATINDEX is a handly function to find the location of special characters in a column.
http://msdn.microsoft.com/en-us/library/ms188395.aspx

%d bloggers like this: