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

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, 
       '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 ' 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.


Parallel Data Warehouse (PDW) Lesson 1: – Basic Architecture Overview

I’ll begin my coverage of Microsoft’s Parallel Data Warehouse (PDW) architecture / appliance with a brief overview of the Massively Parallel Processing (MPP) architecture and the specific hardware components of the PDW appliance.

For starters, think of PDW as a single, pre-configured server and network environment, comprised of all the hardware and software neccessary to perform a very specific task – process and store massive amounts of data and retrieve it as quickly and reliably as possible. Overall, this goal is achieved by limiting the user workloads with the PDW to reading vs. writing, parallel load balancing, and multi-level system redundancies.

The Parallel Data Warehouse appliance architecture is based on  MPP technology which has been in existence for a number of years going back the early 80’s. Additionally, MPP was not widely adopted at that time due to high hardware costs. The primary difference between traditional SQL Server symmetric multiprocessing (SMP) systems that we are all currently working with and the re-emerging MPP architecture lies in the method of processing, storage, and hardware configuration.

Scale-Up vs. Scale-Out
With SMP systems, as the size and demands on the data grow, many server administrators choose to “throw more hardware” at emerging performance issues or increasing storage requirements. Whether it is adding more CPUs, RAM, or discs to the storage area network (SAN) to an existing SMP implementaion, this approach is referred to as ‘scaling- up’. There are many issues with scaling-up and I will briefly summarize the heavy hitters below:
– Performance Bottlenecks: Increasing the number of discs managed by a single storage controller negatively impacts performance. In an SMP environment, the single system bus cannot efficiently manage multiple user requests against an excessive number of discs.
– System Availability: Maintenance tasks and server outages typically impacts all users.
– Hardware Limitations: Eventually the system architecture/hardware will not support any more CPUs or memory.
– System Compatibility: Typically the SMP is a multivendor solution which leads to technical challenges and incompatibility.
– Total Cost of Ownership: ongoing maintenance and costs of changing server and network compacity lead to significant ownership costs over time.

The other school of thought is to ‘scale-out’, which offers a modular approach to scaling the server capabilities.  When scaling-out, storage units or ‘racks’ are added to a MPP appliance as needed. Scaling-out not only addresses the scaling up issues from above but also offers parallel processing of user requests via MPP specific distribution and partitioning methods (This will be covered in Lesson 3: Distribution and Replication Tables).

Performance Bottlenecks RESOLVED:
Parallel Data Warehouse addresses performance bottlenecks via parallel processing, dual infiniband networks, multi-core CPUs, ample amounts of RAM, and multiple system buses.

System Availability RESOLVED:
Parallel Data Warehouse includes multiple levels of redundancy including hot-swap drives, “warm” failover servers, mirrored drives, dual cooling fans, dual networks, dual power supplies, failover clusters

Hardware Limitations RESOLVED
Parallel Data Warehouse is expandable up to four full data racks

System Compatibility RESOLVED
Parallel Data Warehouse is a single vendor turn-key appliance

Total Cost of Ownership RESOLVED
Parallel Data Warehouse is known for ease of maintenance and effortless scalability

Microsoft has paired up with hardware vendors, namely HP and Dell, to provide a few configuration options that fit the specific storage needs for any mid to large size customer (more on that later)

The base configuration for a “full rack” is comprised of two physical racks – the control rack and the data rack as shown below.


Control Rack
The control rack essentially hosts the management hardware/software and acts as the liason between user requests and the control nodes.  As seen in the diagram above, the management node and the control node have backup servers to provide redundancy.  I will provide detail for each of the servers found in the Control Rack.

Management Node – Think of this as the nerve center of the PDW. The management node contains node images for reimaging activities, deploys software updates to all appliance nodes, monitors system health, serves as the domain controller while handling non-login authentication, and performs general hardware and software management functions.

Control Node – Serves as  the central point of control for the appliance, point of client connection and user query processing, system and database metadata storage, and supports a centralized hardware monitoring scheme.

Landing Zone – Primarily serves as the point of entry for incoming data streams as data is cleansed and stored here.  Authorized NT users are able to access the LZ as it is visible to the corporate network. Third party tools such as the Nexus Query editor by Coffing Data Warehousing are also installed here.

Backup Node – Acts as a central backup and restore location for data. As with any backup location, files can be copied from here and stored in a separate backup archive.

Data Rack
On the other hand, the data rack handles the parallel execution of user queries, and PDW can support up to 4 data racks. By definition, 4 data racks is the currently published scale out limit of PDW architecture with each rack containing 10 compute nodes (effectively a 40 compute node limit).

Compute Node – Think of a compute node as a single standalone SQL Server instance with dedicated storage and memory. Current base specs for each compute node include 96 Gb of memory, hex-core CPUs, and local tempdb workspace.  Also, each node has a dedicated disc array which is managed via a SAN component and interconnected to other disc arrays via a dual Fibre Channel data bus.  The data bus supports high-speed disk I/O plays an integral role in disk redundancy. Additionally, the primary data is stored as RAID1 on the SANs.

So to make a more direct connection to the DBA and/or developer that may be reading this, let’s walk through a specific scenario where a user wants to execute a query against the PDW.  The interaction with these system entities is as follows: The user connects to the control node via a query editing tool. The control node gets the request from the tool and creates a distributable query plan to execute on the compute nodes.  The compute nodes work on their subsets of data and return the result sets to the control node.  The control node compiles the result sets into one collection and outputs the results to the query editor/user.  Pretty simple stuff.

Dual Infiniband Network

The compute nodes are connected together using dual InfiniBand network which enables high-speed data sharing and computations between nodes called data shuffling. Compute nodes are also connect to the control and admin nodes to support high-speed backups/restores, table copies, data loads, and query execution.

Dual Fibre Channel

Essentially serving as the data bus, the dual fibre channel ensures super-fast I/O processing and failover redundancy.

General PDW Base Features:

Strong relational engine
Parallel Database Copy
Microsoft product stack integration
Physical separation of server workloads
Separation of IO patterns
Parallel loading – 1.5TB per hour on 1 rack
High speed scanning – 20 to 35GBps per rack

The “Half-Rack”

I think it is important to note here that the current PDW offerings best serve customers that have between 10 Tb and 500 Tb projected storage requirement over the next few years.

To support those customers that do not have hundreds of terabytes worth of data but still want to take advantage of the features of PDW, HP and Microsoft agreed on an additional topology called the Half-Data Rack.  The Half-Data Rack provides around 40% of the performance of a full rack and is around 65% of the price.

Below is a breakdown of the current offerings:

Configuration Servers Processors Cores Space (Tb)
HP PDW  Full Rack 17 22 132 125
HP PDW  Full Rack with 4 Data Racks 47 82 492 500
HP PDW  Half Rack 11 8 48 15-60 (Optional disc sizes   available)

That wraps up this initial discussion on the basics of PDW hardware architecture. I hope you have found this post useful and have a better understanding of the hardware components that make up the PDW architecture.

%d bloggers like this: