Tag Archives: transact sql

Data Vault Series

Hash Diff calculation with SQL Server (Datavault Series)

4 Comments

Updates

Change log

Date Changes
31 January, 2017 Really good news! The 8k limitation on HASHBYTES has been removed in SQL Server 2016!

Today I would like to discuss Hash Diff calculation using SQL Server 2014, from my own experience.

My purpose is NOT to be complete and/or to replicate book “Building a scalable data warehouse with Data Vault 2.0”. For full details please read the book.

However I will give a short intro for those who hardly know what I am talking about.

Introduction

Hash differences or Hash diffs

A hash difference is a hash value of all the descriptive data of a row in a satellite, plus the business key column(s).

A hash difference column is used to detect changes in a new version of a satellite’s row for a particular business key, and can be used in a comparison instead of doing a comparison on every individual descriptive attribute.
The main reason to use hash diff columns is to improve performance when loading new satellite rows (of which one or more attributes have changed).
The more columns the satellite has, the higher the performance gain will be.
To support data warehouse automation patterns, hash diffs can also be used for satellites with only a few or even one descriptive attribute.

I think a different name, like RowHash, RecordHash, DataHash or AttributeValuesHash, would better describe the contents of the column, because the value itself is not a difference, but that’s a different (relatively unimportant) discussion.

Hash keys

This article is not about Hash keys, however to be complete on hashing as far as Data Vault 2.0 is concerned, I will give a short introduction on that as well.

Hash keys replace sequence numbers (generated by the database engine) of the Data Vault 1.0 standard. They support geographically distributed data warehouses, as well as integration with big data environments like Hadoop.

A hash key is a hash value of the business key column(s) used in a Hub or Link. The advantage is that it is predictable, which enables parallel loading and a multiplatform / distributed datawarehouse architecture. For instance a customer with code NL123 will have the same hash key on all distributed parts of the data warehouse. Because the hash key always has the same length and is stored as a (fixed length) CHAR column, performance is usually better than when the business key column(s) are directly used as primary key.

How to calculate a hash difference in T-SQL

For full details about Endianness, differences of hash functions between different platforms, how to cope with new columns and other technical details I refer to the book mentioned above. If you want to work with Data Vault 2.0 you will need the book anyway.

As said, a hash difference is a hash value of all the descriptive data of a row in a satellite, plus the business key column(s). To do the hashing, we have to put all the column values together in one nvarchar value, and then apply a hashing function on it. This is the short version and the base to understand it.

DV-S01E02-hashdiff

Between the different column values you have to use a delimiter, preferably one that does not occur in the column values. Otherwise you have to “escape” the delimiter in column values.

If you want to do a case insensitive compare, you should convert all values to either upper- or lowercase. But this means that if in the source system a customer name was “hans michiels” and is changed to “Hans Michiels”, you will not detect the change and therefore do not store it in the Data Vault. So it depends on the contents of the satellite and your business view on it if this is desired behaviour or not.

The hash diff calculation results in a binary(16) value, when using the hashing algorithm MD5 (which is recommended). To enable better cross-platform- and tools support, this is then converted to a hexadecimal string of 32 characters, stored as a CHAR(32) column.

In all cases you must also convert the hexadecimal hash value to all UPPER- or all lowercase, because not all convert functions give the same output, some lower- and other uppercase. To compare values that were made by different hash functions (read on different systems) making it all uppercase (or lowercase) makes sure the values do not differ in casing only (which could lead to a false negative answer on the “are the rows the same” question).

I understand this is all dry and boring nerd stuff if you do not see an example, so .. a simple example:

Calculating_a_hash_diff.sql

SELECT
      [bla]
    , UPPER(
        CONVERT(CHAR(32),
          HASHBYTES('MD5',
            UPPER(
              CONCAT( ISNULL(CONVERT(NVARCHAR, [BusinessKeyOfHub]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), [Column01]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), [Column02]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), [Column03]), N'')
              ) -- END CONCAT
            ) -- END UPPER
          ) -- END HASHBYTES
        , 2) -- END CONVERT
      ) -- END UPPER
FROM [stg].[bla]

So far nothing shocking, but there are a few pitfalls. If you keep them in mind, you will be an excellent hasher soon. 😉

CONVERT(NVARCHAR without specifying the size

Do this only for values of 30 or less characters, otherwise the value will be cut off which could lead to wrong hash diff values, not changing when the attribute value changes. It is for instance okay for integers, decimals (unless the precision is more than 28, mind the decimal separator and possible negative sign), datetime stamps, and bits. When converting (n)varchar safest thing to do is follow the original size, for unique identifiers use 38 or 40.

Beware for CONCAT

Beware for concatenation of (N)VARCHAR(MAX), (N)TEXT and XML columns using the CONCAT function. It appears that only the first 8000 bytes of (N)VARCHAR(MAX) columns are in the concatenated result.

And even worse, HASHBYTES ..

HASHBYTES does only hash a NVARCHAR value with a length of 4000 or less. Above that the error “String or binary data would be truncated” occurs. This is really important to keep in mind.
The 8000 characters (or 4000 unicode characters) limitation on any string hashed with the HASHBYTES function has been removed in SQL Server 2016! Now you can hash larger (N)VARCHAR values!

Cope with the HASHBYTES and CONCAT limitations

4000 characters is quite something, right? Only with very wide satellites (a lot of columns) and/or usage of (N)VARCHAR(MAX), (N)TEXT or XML columns (hereafter called ‘unlimited length columns’) you can get into trouble.

So, what is wise to do? Some measures you can take are:

Make your satellites not too wide

This is a best practice, not only from hashing function perspective. Split the satellites by source system, then by rate of change. Keep in mind that the business key column(s) and all satellite column values are converted to NVARCHAR and then (including all delimiters) may not exceed the 4000 characters if you want to use the HASHBYTES function.

Size unlimited length columns according to the actual maximum length

What I mean is, if for instance a Country name is stored in an unlimited length column in the source system, you know that a size of 100 will be long enough. When you doubt just do some data profiling on the source to see what is the maximum length. Use that size plus a safety margin for the satellite column size. Problem gone.

Handle true unlimited length columns

Put true unlimited length columns (for instance free entry ‘memo’ fields or xml columns in source systems in their own satellite! Otherwise if any other (small) attribute changes, the 4k memo is copied, even when it was not changed. This absorbs storage. But that alone is not enough, as said, with HASHBYTES, above 8000 characters (4000 when unicode) you are still f*c**d. Luckily there is a system function in the master database, which uses the MD5 algorithm and takes a binary value as input. So to compute a MD5 hash value for a text column with unlimited length, you could do something like:

Hash_for_memo.sql

SELECT master.sys.fn_repl_hash_binary(CONVERT(VARBINARY(MAX),
[MyHugeMemoField)) AS [HashDiff]

But don’t make the mistake to use the function [sys].[fn_repl_hash_binary] for all hashing, because the performance of HASHBYTES is much better!
On my machine HASHBYTES is three times faster than [sys].[fn_repl_hash_binary] on the same dataset.
You can check for yourself using this script:

Performance_comparison.sql

USE [msdb]
GO

DECLARE @start DATETIME2(7)
DECLARE @stop DATETIME2(7)
DECLARE @elapsed1 BIGINT
DECLARE @elapsed2 BIGINT
--\-------------------------------------------------------------------------
---) TEST PERFORMANCE of HASHBYTES function.
--/-------------------------------------------------------------------------
SELECT @start = GETDATE();
SELECT TOP1000000
      UPPER(
        CONVERT(CHAR(32),
          HASHBYTES('MD5',
            --UPPER(
              CONCAT( ISNULL(CONVERT(NVARCHAR(128), o1.[name]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[object_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[principal_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[schema_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[parent_object_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[type]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[type_desc]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[create_date], 126), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[modify_date], 126), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[is_ms_shipped]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[is_published]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[is_schema_published]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(128), o2.[name]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[object_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[principal_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[schema_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[parent_object_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[type]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[type_desc]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[create_date], 126), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[modify_date], 126), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[is_ms_shipped]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[is_published]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[is_schema_published]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(128), o3.[name]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[object_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[principal_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[schema_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[parent_object_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[type]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[type_desc]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[create_date], 126), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[modify_date], 126), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[is_ms_shipped]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[is_published]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[is_schema_published]), N'')
              ) -- END CONCAT
            --) -- END UPPER
          ) -- END HASHBYTES
        , 2) -- END CONVERT
      ) -- END UPPER
  FROM [sys].[objects] o1
  CROSS JOIN [sys].[objects] o2
  CROSS JOIN [sys].[objects] o3;

SELECT @stop = GETDATE();
SELECT @elapsed1 = DATEDIFF(MICROSECOND,@start, @stop);

--\-------------------------------------------------------------------------
---) TEST PERFORMANCE of [master].[sys].[fn_repl_hash_binary] function.
--/-------------------------------------------------------------------------
SELECT @start = GETDATE();
SELECT TOP 1000000
      UPPER(
        CONVERT(CHAR(32),
          master.sys.fn_repl_hash_binary(CONVERT(VARBINARY(MAX),
            --UPPER(
              CONCAT( ISNULL(CONVERT(NVARCHAR(128), o1.[name]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[object_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[principal_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[schema_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[parent_object_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[type]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[type_desc]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[create_date], 126), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[modify_date], 126), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[is_ms_shipped]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[is_published]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o1.[is_schema_published]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(128), o2.[name]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[object_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[principal_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[schema_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[parent_object_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[type]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[type_desc]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[create_date], 126), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[modify_date], 126), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[is_ms_shipped]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[is_published]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o2.[is_schema_published]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(128), o3.[name]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[object_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[principal_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[schema_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[parent_object_id]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[type]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[type_desc]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[create_date], 126), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[modify_date], 126), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[is_ms_shipped]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[is_published]), N'')
                    , ';', ISNULL(CONVERT(NVARCHAR(50), o3.[is_schema_published]), N'')
                 ) -- END CONCAT
            --) -- END UPPER
          ) -- END HASHBYTES
          ) -- END CONVERT
        , 2) -- END CONVERT
      ) -- END UPPER
  FROM [sys].[objects] o1
  CROSS JOIN [sys].[objects] o2
  CROSS JOIN [sys].[objects] o3;

SELECT @stop = GETDATE();
SELECT @elapsed2 = DATEDIFF(MICROSECOND,@start, @stop);

SELECT @elapsed1 AS [Elapsed_HashBytes]
, @elapsed2 AS [Elapsed_fn_repl_hash_binary]
, 'HASHBYTES is ' + CONVERT(VARCHAR, CONVERT(decimal(19, 2), @elapsed2 / @elapsed1))
+ ' times faster than [fn_repl_hash_binary]' AS [Result]

That’s about it.
Just a few more things:

  • If you use a different hashing algorithm (e.g. SHA1) you cannot use the sys.fn_repl_hash_binary function, in that case you will have to seek your solution in a C# script task or CLR stored procedure, doing the hashing in .NET code.
  • Don’t use CONCAT if you have more than one column in the satellite containing [MyHugeMemoField], or you still can run into problems.

Conclusion / Wrap up

I have explained in short why Hash diff columns are used in a Data Vault 2.0 satellite, and how these values can be calculated.

After that, you have read about some limitations of the CONCAT and HASHBYTES function when used for calculating Hash diff values, and how to cope with these limitations.

Credits
My thanks go to Vincent Kuiper for reviewing the initial draft of this article.

(c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

T-SQL Scripting Series

How to copy a database table structure (T-SQL Scripting Series)

9 Comments

Problem

Do you recognize the need to copy a table structure in MS SQL Server, for temporary or permanent usage?

Most of you will know the famous

Insert Into Example.sql
SELECT TOP 0 *
INTO [dbo].[some unexisting table]
FROM [dbo].[some existing table]

But this approach has some limitations: Computed columns change into regular columns, and primary and foreign keys are not copied. Sometimes you just need more than this.

Solution

So I have made a script to copy a SQL server table structure with foreign keys but without indexes.
This script handles user defined types and computed columns gracefully.

My credits go to Aaron Bertand (see his post on mssqltips). I used his script for copying the foreign keys and added my own parts to it (download it here).

TSQL-S01E01-copy_table

TSQL-S01E01-CopyTableStructure.sql

--\
---) Author: Hans Michiels
---) Script to copy a sql server table structure with foreign keys but without indexes.
---) This script handles user defined types and computed columns gracefully.
--/
/*
(c) Copyright 2016 - hansmichiels.com
 
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
 
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
 
You should have received a copy of the GNU General Public License
along with this program. If not, see .
*/

--\
---) VARIABLES DECLARATIONS
--/
DECLARE @CrLf NVARCHAR(2)
DECLARE @Indent NVARCHAR(2)
DECLARE @nsql NVARCHAR(MAX)
DECLARE @SimulationMode CHAR(1)
DECLARE @SourceSchemaAndTable NVARCHAR(260)
DECLARE @TargetSchemaAndTable NVARCHAR(260)
DECLARE @FkNameSuffix NVARCHAR(128)
DECLARE @TableOptions NVARCHAR(500)

--\
---) CONFIGURATION: set the source and target schema/tablename here, and some other settings.
--/
SELECT
    @SimulationMode = 'Y' -- Use Y if you only want the SQL statement in the output window without it being executed.
  , @SourceSchemaAndTable = '[dbo].[Order]'
  , @TargetSchemaAndTable = '[dbo].[OrderCopy]'
  , @TableOptions = ' WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]'
  , @FkNameSuffix = '_' + REPLACE(CAST(NEWID() AS VARCHAR(40)), '-', '') -- A Guid is added to the foreign key name to make it unique.
  , @CrLf = CHAR(13) + CHAR(10)
  , @Indent = SPACE(2)
  -- For 'min' script use this (in case sql is near 4000 characters):
  -- , @CrLf = ' '
  -- , @Indent = ''

--\
---) BUILD SQL FOR CLONING TABLE
--/
SELECT @nsql
    = ISNULL(@nsql, '')
    + CASE col_sequence WHEN 1 THEN
      @CrLf + 'IF OBJECT_ID(N''' + @TargetSchemaAndTable + ''', ''U'') IS NOT NULL DROP TABLE ' + @TargetSchemaAndTable + ';'
    + @CrLf + 'CREATE TABLE ' + @TargetSchemaAndTable + @CrLf + @Indent + '( ' ELSE @CrLf + @Indent + ', ' END
    + [definition]
FROM (
      SELECT ROW_NUMBER() OVER (PARTITION BY tb.object_id ORDER BY tb.object_id, col.column_id) AS col_sequence
        , QUOTENAME(col.name) + ' '
        + COALESCE(
            'AS ' + cmp.definition + CASE ISNULL(cmp.is_persisted, 0) WHEN 1 THEN ' PERSISTED ' ELSE '' END,
            CASE
              WHEN col.system_type_id != col.user_type_id THEN QUOTENAME(usr_tp.schema_name) + '.' + QUOTENAME(usr_tp.name)
              ELSE
                QUOTENAME(sys_tp.name) +
                CASE
                  WHEN sys_tp.name IN ('char', 'varchar', 'binary', 'varbinary') THEN '(' + CONVERT(VARCHAR, CASE col.max_length WHEN -1 THEN 'max' ELSE CAST(col.max_length AS varchar(10)) END) + ')'
                  WHEN sys_tp.name IN ('nchar', 'nvarchar') THEN '(' + CONVERT(VARCHAR, CASE col.max_length WHEN -1 THEN 'max' ELSE CAST(col.max_length/2 AS varchar(10)) END) + ')'
                  WHEN sys_tp.name IN ('decimal', 'numeric') THEN '(' + CAST(col.precision AS VARCHAR) + ',' + CAST(col.scale AS VARCHAR) + ')'
                  WHEN sys_tp.name IN ('datetime2') THEN '(' + CAST(col.scale AS VARCHAR) + ')'
                  ELSE ''
                END
            END
            )
        + CASE col.is_nullable
            WHEN 0 THEN ' NOT NULL'
            ELSE CASE WHEN cmp.definition IS NULL THEN ' NULL' ELSE ' ' END
          END AS [definition]
       FROM sys.tables tb
       JOIN sys.schemas sch
         ON sch.schema_id = tb.schema_id
       JOIN sys.columns col
         ON col.object_id = tb.object_id
       JOIN sys.types sys_tp
         ON col.system_type_id = sys_tp.system_type_id
        AND col.system_type_id = sys_tp.user_type_id
       LEFT JOIN
            (
            SELECT tp.*, sch.name AS [schema_name]
            FROM sys.types tp
            JOIN sys.schemas sch
            ON tp.schema_id = sch.schema_id
            ) usr_tp
         ON col.system_type_id = usr_tp.system_type_id
        AND col.user_type_id = usr_tp.user_type_id
       LEFT JOIN sys.computed_columns cmp
         ON cmp.object_id = tb.object_id
        AND cmp.column_id = col.column_id
      WHERE tb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U')
      ) subqry
;
SELECT @nsql
    = ISNULL(@nsql, '')
    + CASE col_sequence
        WHEN 1 THEN @CrLf + ', PRIMARY KEY ' + CASE is_clustered_index WHEN 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END
           + @CrLf + @Indent + '( '
        ELSE @CrLf + @Indent + ', '
      END
    + QUOTENAME(pk_cols.column_name)
    + CASE is_descending_key
        WHEN 1 THEN ' DESC'
        ELSE ' ASC'
      END
FROM (
      SELECT TOP 2147483647 sch.name as schema_name, tb.name as table_name, col.name as column_name
               , ROW_NUMBER() OVER (PARTITION BY tb.object_id ORDER BY tb.object_id, ic.key_ordinal) AS col_sequence
               , ic.is_descending_key
               , CASE WHEN idx.index_id = 1 THEN 1 ELSE 0 END AS [is_clustered_index]
       FROM sys.tables tb
       JOIN sys.schemas sch
         ON sch.schema_id = tb.schema_id
       JOIN sys.indexes idx
         ON idx.is_primary_key = 1
        AND idx.object_id = tb.object_id
       JOIN sys.index_columns ic
         ON is_included_column = 0
        AND ic.object_id = tb.object_id
        AND ic.index_id = idx.index_id
       JOIN sys.columns col
         ON col.column_id = ic.column_id
        AND col.object_id = tb.object_id
      WHERE tb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U')
      ORDER BY tb.object_id, ic.key_ordinal
      ) pk_cols
 
SELECT @nsql = @nsql + @CrLf + @indent + ') ' + @TableOptions

IF @SimulationMode = 'Y'
BEGIN
  PRINT '-- Simulation mode: script is not executed.'
END
PRINT @nsql;
IF @SimulationMode != 'Y'
BEGIN
  EXEC(@nsql);
END
 
--\
---) Copy foreign key constraints
---) A guid is added to the foreign key name to make it unique within the database.
--/
SET @nsql = N'';
SELECT @nsql += N'
ALTER TABLE '
      + @TargetSchemaAndTable
      + ' ADD CONSTRAINT [' + LEFT(fk.name + @FkNameSuffix, 128) + '] '
      + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(col.name)
      -- get all the columns in the constraint table
      FROM sys.columns AS col
      JOIN sys.foreign_key_columns AS fkc
        ON fkc.parent_column_id = col.column_id
        AND fkc.parent_object_id = col.[object_id]
      WHERE fkc.constraint_object_id = fk.[object_id]
      ORDER BY fkc.constraint_column_id
      FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
      + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rtb.name)
      + '('
      + STUFF((SELECT ',' + QUOTENAME(col.name)
      -- get all the referenced columns
      FROM sys.columns AS col
      JOIN sys.foreign_key_columns AS fkc
        ON fkc.referenced_column_id = col.column_id
       AND fkc.referenced_object_id = col.[object_id]
      WHERE fkc.constraint_object_id = fk.object_id
      ORDER BY fkc.constraint_column_id
      FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
JOIN sys.tables AS rtb -- referenced table
   ON fk.referenced_object_id = rtb.[object_id]
JOIN sys.schemas AS rs
   ON rtb.[schema_id] = rs.[schema_id]
JOIN sys.tables AS ctb -- constraint table
   ON fk.parent_object_id = ctb.[object_id]
WHERE rtb.is_ms_shipped = 0 AND ctb.is_ms_shipped = 0
  AND ctb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U');

IF @SimulationMode = 'Y'
BEGIN
  PRINT '-- Simulation mode: script is not executed.'
END
PRINT @nsql;
IF @SimulationMode != 'Y'
BEGIN
  EXEC(@nsql);
END

Download the script here.

Conclusion / Wrap up

With this script you can copy the structure of a table including primary and foreign keys, computed columns and columns using a user defined datatype.
Last updated: 14 July, 2016.

(c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

All blog posts, Archive

The CONTROLLER – TASK design pattern for stored procedures

1 Comment

(This blog post was first published on 25-nov-2013 on my former blog)

The challenges

Working with SQL Server and stored procedures for several years, I have faced several challenges like:

  1. reuse of stored procedures: make sure stored procedures are small components that you can use for different purposes.
  2. maintainability of stored procedures: make sure stored procedures do not become large ‘procedural written’, otherwise they can become hard to maintain due to this.
  3. generating code versus manual maintenance: how to merge generated and manual SQL code easily.
  4. where to implement error handling and the automic unit using a transaction: how to ensure these without harming the needs above.

And I have found a way to deal with this: The CONTROLLER - TASK design pattern for stored procedures. This pattern solves my challenges into a working solution!

1) Reuse of stored procedures

Sometimes you want to reuse a particular INSERT, UPDATE or DELETE statement. When it is in a stored procedure you can simply call this stored procedure! But what if this stored procedure also contains other code, uses its own transaction or implements its own error handling? Then this might be not that simple!

The CONTROLLER-TASK design pattern for stored procedures makes reuse of SQL code very easy.

2) Maintainability of stored procedures

A best practice in C# programming (or any type of programming really) that has become even more popular over the last years, is to create short methods containing only a small amount of code, and having a “main” method to call all the short methods.

Advantages: the main method is very readable (when method names are self-explaining), and code stays really maintainable. Also, short methods can be unittested easier.

Why not do the same in SQL code then?

Why not have an inventory of short stored procedures, either generated or manually made, that do only one task, and shop around to glue together what you need in a “main” stored procedure?

When working like this in a team:

- tasks can be divided easier when stored procedures are short

- stored procedures are easier to understand for new developers when they are short

The CONTROLLER-TASK design pattern for stored procedures makes your SQL code better maintainable.

3) Generating code versus manual maintenance

How convenient would it be to generate generic stored procedures, for instance UPDATE stored procedures for each table, a LOAD stored procedure for each hub in a datavault datawarehouse, and so on? Generating is not extremely more difficult than creating normal stored procedures. Just use tables like sys.objecs, sys.columns, and so on, and a lot is possible.

Imagine these stored procedures populate your inventory of “tasks”, ready to use and glue together in  a “main” stored procedure?

The CONTROLLER-TASK design pattern makes combining both manually made and generated SQL code to a working solution extremely easy.

4) Where to implement the error handling and automic unit using a transaction?

Is a stored procedure still reusable, if it is running in its own transaction? Maybe, if you use nested transactions. But there are drawnbacks and risks. Code can become messy when not implemented in the same way in different stored procedures.

Is a stored procedure still reusable, if it implements its own error handling? Maybe, but you have to be careful. If it ‘swallows’ the error, the stored procedure that calls it will not notice it and will continue after the error. This is almost never what you want. Besides that, implementing error handling in every stored procedure, makes your code base consist of many more lines.

So .. why not divide the “core” SQL code to run, and the “housekeeping” (automic unit, error handling)? No more confusion, it is clear where things are done, and code is easier reusable and maintainable.

The CONTROLLER-TASK design pattern divides core functionality and housekeeping code, therewith keeping your code cleaner and again .. better maintainable!

My solution, easy to implement

My answer to the challenges above is the CONTROLLER-TASK design pattern for stored procedures. As a picture says more than a thousand words, take a close look at the one below.

controller_task_design_pattern

Getting started

Allright, if you see the value of it, how can you implement the CONTROLLER-TASK design pattern?

In its basic form, you’ll need only a few things: a naming convention and two stored procedure templates.

I will tell you how I did this, so you can use or modify it in the way you prefer.

Choosing a naming convention

As explained, you have three types of stored procedures:

  • Task stored procedures (manually made)
  • Task stored procedures (generated)
  • Controller stored procedures

I just prefix my stored procedures with the typename as follows:

  • TASK_<BaseStoredProcName> for stored procedures that are manually made
  • GTSK_<BaseStoredProcName> for stored procedures that are generated
  • CTLR_<BaseStoredProcName> for controller stored procedures

Of course you can invent your own different naming convention, as long as you use it consequently and document it for new developers working on your project.

Using two templates

Here are my templates, you can modify for your own needs

Prerequisites

prerequisites.sql
CREATE SCHEMA [sp] AUTHORIZATION [dbo];

TASK stored procedures

TASK stored procedures.sql
PRINT ‘ScriptFile P10.sp.TASK_ReplaceWithStoredProcBaseName.sql’
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N’sp.TASK_ReplaceWithStoredProcBaseName’) AND type in (N’P’, N’PC’))
    DROP PROCEDURE sp.TASK_ReplaceWithStoredProcBaseName
GO
/* PRESS CTRL-SHIFT-M in SSMS to fill parameters
==========================================================================================
Author :
Create date :
Parameters:
- Input : none
- Output : none
Returnvalue : 0 if executed successfully.
Description : >
==========================================================================================
*/
CREATE PROCEDURE sp. TASK_ReplaceWithStoredProcBaseName
AS
BEGIN
  ---------------------------------------------------------------------------------------------------
  -- DECLARATIONS
  ---------------------------------------------------------------------------------------------------
  --N/A
  ---------------------------------------------------------------------------------------------------
  -- INITIALIZATION
  ---------------------------------------------------------------------------------------------------
  --N/A
  ---------------------------------------------------------------------------------------------------
  -- MAIN
  ---------------------------------------------------------------------------------------------------
  SELECT 1 AS [Replace with your own code]

END
GO

 

Controller stored procedures

Controller stored procedures.sql
PRINT ‘ScriptFile P20.sp.CTLR_ReplaceWithStoredProcBaseName.sql’
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N’sp.CTLR_ReplaceWithStoredProcBaseName’) AND type in (N’P’, N’PC’))
    DROP PROCEDURE sp.CTLR_ReplaceWithStoredProcBaseName
GO
/* PRESS CTRL-SHIFT-M in SSMS to fill parameters
==========================================================================================
Author :
Create date :
Parameters:
- Input : none
- Output : none
Returnvalue : 0 if executed successfully.
Description : >
==========================================================================================
*/
CREATE PROCEDURE sp.CTLR_ReplaceWithStoredProcBaseName
AS
BEGIN
  ---------------------------------------------------------------------------------------------------
  -- TEMPLATE CODE: CONFIGURATION OF CONTROLLER, BEGIN TRANSACTION, INFO LOGGING
  ---------------------------------------------------------------------------------------------------
  BEGIN TRY

    SET XACT_ABORT ON
    BEGIN TRANSACTION

    ---------------------------------------------------------------------------------------------------
    -- (NON-TEMPLATE) DECLARATIONS
    ---------------------------------------------------------------------------------------------------
    -- N/A
    ---------------------------------------------------------------------------------------------------
    -- EXECUTE TASK STORED PROCEDURES
    ---------------------------------------------------------------------------------------------------
    -- Example call:
    EXECUTE sp.TASK_ReplaceWithStoredProcBaseName
    --

    ---------------------------------------------------------------------------------------------------
    -- TEMPLATE CODE: COMMIT TRANSACTION
    ---------------------------------------------------------------------------------------------------
    COMMIT TRANSACTION

  END TRY
  BEGIN CATCH
    ---------------------------------------------------------------------------------------------------
    -- TEMPLATE CODE: CATCH BLOCK WITH ROLLBACK AND THROW
    ---------------------------------------------------------------------------------------------------
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION;
    END
    -- Options here:
    --\
    ---> 1) Log the error (optional)
    --/
    -- Not part of the BASIC template

    --\
    ---> 2) Rethrow the error (optional)
    --/
    -- Syntax for SQL Server 2012:
    -- ; THROW

    -- Syntax for SQL Server 2008 and earlier:
    DECLARE @ORIGINAL_ERROR NVARCHAR(MAX)
          , @ERROR_SEVERITY INT = ERROR_SEVERITY()
          , @ERROR_STATE INT = ERROR_STATE()

    SELECT @ORIGINAL_ERROR = ” + ERROR_MESSAGE()
                        + ‘ (ERROR_NUMBER=’ + CAST(ERROR_NUMBER() AS NVARCHAR)
                        + ‘, ERROR_LINE=’ + CAST(ERROR_LINE() AS NVARCHAR)
                        + ‘, ERROR_PROCEDURE=’ + ERROR_PROCEDURE() + ‘)’
    RAISERROR(@ORIGINAL_ERROR, @ERROR_SEVERITY, @ERROR_STATE)

  END CATCH

  ---------------------------------------------------------------------------------------------------
  -- RETURN THE RESULT WHEN EXECUTED SUCCESFULLY
  ---------------------------------------------------------------------------------------------------
  RETURN 0;
    
END
GO

 

Summary

In this blogpost I have explained to you the advantages of the CONTROLLER - TASK design pattern for stored procedures, which are:

  • makes reuse of SQL code very easy.
  • makes your SQL code better maintainable.
  • makes combining both manually made and generated SQL code to a working solution extremely easy.
  • divides core functionality and housekeeping code, therewith keeping your code cleaner and again .. better maintainable!

After that you were shown how to get started with a basic implementation of this design pattern.

Thank you for reading my blogpost and I hope you enjoyed it and found it useful.

All blog posts, Archive

Query hints WITH (NOLOCK) and WITH (READPAST) in action

(This blog post was first published on 18-jan-2013 on my former blog)

Download all used scripts here.

Query hints WITH (NOLOCK) and WITH (READPAST) are a great way to increase performance on (readonly) queries that run on one or more tables in which data is added , edited or deleted at the same time.

However those query hints have also some drawbacks, to be aware of, when using them.

In this article I will demonstrate how these query hints work, and when to use them.

Let’s start with some theory: what do these query hints do?

It makes no sense to copy the MSDN description here. If you need more detailed information you can read it yourself on msdn

I will just summarize what these query hints can do for me.

WITH (READPAST) – shows only committed data. When a record gets updated, it will not be selected at all, not the previous version of the record, nor the updated but uncommitted version.

WITH (NOLOCK) – shows both committed and uncommitted (dirty) data. When a record gets updated, the new uncommitted values of the record will be selected.

When to use

In general, do not use these queryhints, when you retrieve data to edit, e.g. in disconnected scenario’s like editing in a webapplication. Working with uncommitted data will in this case seldom be what you want.

When it comes to read only access, it depends. You have to decide what is more important: showing always the right (committed) data, or showing data fast. If the risk that uncommitted data is shown is acceptable, WITH (NOLOCK) is the query hint for you, if the risk that data being changed is not shown at all, WITH (READPAST) is the query hint for you.

Also for semi-static data (dimension- or system tables), that is seldom changed, the risk of the drawbacks of both queryhints, is usually acceptable.

Executing the demo

Step 1

First of all we have to do some setup work. I have made a script for that. You can run it in SQL Server Management Studio (see link to download all scripts at the top of this article).

Note that for simplicity of the demo I have created a CHAR column to be primary key. I know it is not a best practice, but for the demo it is sufficient. Normally you would always strive for numeric primary keys.

step1_setup.sql

— Step 1: Create a QueryHintsDemo database with one table, DemoData
USE [master]
GO
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N’QueryHintsDemo’)
  CREATE DATABASE [QueryHintsDemo]
GO
USE [QueryHintsDemo]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DemoData]’) AND type in (N’U’))
DROP TABLE [dbo].[DemoData]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DemoData](
 [DemoDataCode] CHAR(6) NOT NULL,
 [DemoDataDescription] VARCHAR(100) NOT NULL,
 CONSTRAINT [PK_dbo_DemoData] PRIMARY KEY CLUSTERED
(
 [DemoDataCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Download all used scripts here.

Step 2

What is next?

Now you have to make sure you have two query windows open in Management Studio, so you can execute one script, and before that is finished, execute another script as well.

The first script will insert some initial data in the DemoData table, and then update it in a transaction. To be able to do this demonstration properly, the WAITFOR DELAY feature is used to wait 20 seconds before the transaction is rolled back.

In the first query window, use this script:

step2a_keep_transaction_open.sql

TRUNCATE TABLE [QueryHintsDemo].[dbo].[DemoData]
GO

INSERT INTO [QueryHintsDemo].[dbo].[DemoData]
           ([DemoDataCode]
           ,[DemoDataDescription])
     VALUES
           (‘DEMO01’
           ,‘This is the original text.’)
GO

BEGIN TRANSACTION

  UPDATE [QueryHintsDemo].[dbo].[DemoData]
     SET [DemoDataDescription] = ‘This is the updated text that gets rolled back later.’
   WHERE [DemoDataCode] = ‘DEMO01’

  WAITFOR DELAY ’00:00:20′

ROLLBACK TRANSACTION
GO

BEGIN TRANSACTION

  UPDATE [QueryHintsDemo].[dbo].[DemoData]
     SET [DemoDataDescription] = ‘This is the updated text that gets committed.’
   WHERE [DemoDataCode] = ‘DEMO01’
  
COMMIT TRANSACTION
GO

 

This is the script for the second querywindow:

step2b_select.sql

— WITH READPAST, will not show the updated, non committed record
— but also not the ‘old’ version of the record, before it got updated.
— So you potentially miss out some data when using this query hint.
SELECT ‘WITH (READPAST)’ AS [READPAST], *
FROM [QueryHintsDemo].[dbo].[DemoData] WITH (READPAST)
GO

— WITH NOLOCK, will show the updated, but uncommitted record. This is also called a “dirty read”.
— The risk here is that you use/display data, that in the end will
— not be committed and therefor never became ‘official’.
SELECT ‘WITH (NOLOCK)’ AS [NOLOCK], *
FROM [QueryHintsDemo].[dbo].[DemoData] WITH (NOLOCK)
GO

— Without using any queryhint, the select statement will wait until the
— first transaction is rolled back, and then show the original value.
— This is the most reliable, but also the slowest.
— You will notice that the first two SELECT statements will return
— a result immediately, but that the query below will only show result after almost
— 20 seconds, caused by the 20 seconds delay in the previous script.
SELECT ‘Without queryhint (1)’ AS [No hint 1], *
FROM [QueryHintsDemo].[dbo].[DemoData]

— Wait a few seconds, so that the update to be committed in the other script, is completed.
WAITFOR DELAY ’00:00:02′
— Without using any queryhint, the select statement will wait until the
— update is finished, and then show the final value.
SELECT ‘Without queryhint (2)’ AS [No hint 2], *
FROM [QueryHintsDemo].[dbo].[DemoData]

 

As you might already have understood, you should execute both scripts in SQL Server Management Studio: execute the first script, and while it is still running, start the second script in a seperate query window as soon as possible (but at least within 20 seconds after starting the first script).

Analyzing the query result

20130118_analyze_result2

 
Clean up
Use this script to clean up.

cleanup.sql

USE [master]
GO
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N’QueryHintsDemo’
GO
ALTER DATABASE [QueryHintsDemo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE [QueryHintsDemo]
GO

 

Conclusion

The queryhints WITH (NOLOCK) and WITH (READPAST) are a great way to improve performance.

However they both have their own drawnbacks, to be aware of, when using them:

  • When using WITH (READPAST), you potentially miss out some data that is being updated, when your select query runs.
  • When using WITH (NOLOCK), the risk here is that you use/display data, that in the end will not be committed and therefor never became ‘official’.

It depends on the situation, when it is acceptable to use these queryhints.