Suggest an Idea

[MikeWB]

When creating a new column through DDL (ALTER TABLE), the following limitation apply: 
  
> Limitations and considerations 
https://help.qlik.com/en-US/replicate/November2020/Content/Replicate/Main/Endpoints/DDLStatements.ht... 
  
The following DDL statement does not replicate the DEFAULT value/constraint to the target: 
 
ALTER <table> ADD  <column> <data_type> NOT NULL DEFAULT <defaultvalue> 
 
From what we can see, QLIK does not execute the same DDL statement on the target – it creates the column but drops the DEFAULT and allows NULLs in the column.  It appears to execute a statement like this:  
 
ALTER <table> ADD  <column> <data_type> NULL 
 
The key impact of this is when the source table has data in it prior to this change. In that case it results in material differences between source and target:

• The pre-existing rows in the source have the default value populated to the new column  
• The pre-existing rows in the target have nulls 

Additional Notes:  

1. MS SQL Server out-of-the-box replication handles this correctly and does not result in this failure to replicate.
2. We believe we understand why Qlik ‘misses’ this update of the existing rows. The update appears to not be represented in the DB transaction log. It is handled in a special way by SQL when a column is originally created with a DEFAULT constraint. We can provide further documentation / links around this.
3. Adding the DEFAULT constraint to the target after the fact does not trigger the same behavior by SQL server. From out tests it has to be done on the original ADD <column> step.
 
Use Case: 
 
Fundamentally – this gap results in data not being fully and accurately replicated for the SQL Server -> SQL Server use case. 

This is a common situation for us. We add hundreds of columns each year to our source DB which is controlled by an application called Microsoft Dynamics AX 2009.  AX creates DEFAULT constraints in SQL for every column on every table. We have used MS SQL Server Replication for 15+ years and not run into this issue. We will have to take extra steps including custom development to handle this gap between Qlik and standard MS SQL Replication. 
  
Functionality: 
 
Execute the same DDL statement on the target as was run on the source. Do not remove the DEFAULT constraint. This should result in the target DB taking the same  
  
Motivation: 
  
We bought Qlik Replicate to manage transaction replication and understood it to be superior to native SQL Service Replication. This gap represents an area where Qlik Replicate is less robust than the free native SQL Server Replication.  While Qlik Replicate has many other advantages that make it worth the investment of time and money, in this respect it is “behind” the free standard solution from Microsoft. 
 
Importance: 
  
It is impacting a project that is going on and preventing us from going live with Qlik Replicate in any meaningful way.  
 
Benefits: 
 
It is an important enhancement for any customers of yours that use MS SQL -> SQL replication and closes a gap against the free out-of-the-box replication MS provides.

Additional Information:

Add a NOT NULL column to an existing table – Performance improvement SQL Server 2012

http://www.intellectsql.com/post-add-a-not-null-column-to-an-existing-table-performance-improvement-....

Due to the defect, column2 would be NULL for both rows on the target.

One more visual aid showing that if Qlik doesn't add the default when the column is created, we have no way to add it (manually) to the target later as a work around. Not pictured is another possible option: Just add the DEFAULT but don't make the column NOT NULL. This doesn't work either. SQL does not 'back-fill the existing rows in that case.

[bobvecchione]

Mike - thanks for the detailed ideation. We are reviewing and will respond ASAP.

--bobv--

[bobvecchione]

Hi - I wanted to provide an update here. R&D did a lot of reviewing and analysis on this and it is a “do-able” task but with a fair amount of effort. Right now, with this being the first and only request, we cannot prioritize it as a high enough item to be worked on in the short term. This doesn’t mean we would never do it, just that it will not be on the short term roadmap which covers through the January 23 SR.

I will update if anything else changes from our end. Thanks again

Bob V

[Nulee_Massaro]

Thank you for your request. However, we do not have any short term plans to address this.