Archive for August, 2015

Resolving issues while enabling Temporal Data on an existing Table

August 7, 2015 9 comments

In my [previous post] about Temporal Data I showed how to setup Temporal on an existing table. That was a simple scenario where the tables does not have historical data, and we added the 2 Period columns on that table, and we didn’t face any issues.

–> Check demo to setup Temporal Tables:


–> Resolving potential issues while enabling Temporal Data on an existing Table:

There might be other scenarios where an existing table already contains the Period columns and you want to assign them as the Temporal Period Columns. Here, in this post I’ve tried to do the same with an existing table and ran into some issues while setting it up. Resolved the issues in process to make that table a Temporal Table, which we will see below:

–> My existing table looks like this, EffectiveStartDate & EffectiveEndDate datetime type columns as Period Columns:

CREATE TABLE dbo.ResourceTD (
	 -- other columns here
	,EffectiveStartDate datetime,
	,EffectiveEndDate datetime,


–> 1. First Issue: While setting up Parent Temporal Table with the ALTER TABLE statement:

ALTER TABLE dbo.ResourceTD 

… I got following error below:

Msg 13575, Level 16, State 0, Line 42
ADD PERIOD FOR SYSTEM_TIME failed because table ‘SQL2016POC.dbo.ResourceTD’ contains records where end of period is not equal to MAX datetime.

This was evident by the error message, as the columns datatype was datetime initially and I ALTERed them to datetime2 but the values set were not MAX, but like this “9999-12-31 00:00:00”. So, you need to set them to the MAX possible date, like “9999-12-31 23:59:59.9999999” as shown below:

UPDATE dbo.ResourceTD 
SET EffectiveEndDate = '9999-12-31 23:59:59.9999999'


–> 2. Second Issue: Finally, while enabling the Temporal Table with History Table:

-- Enabling the System-Versioning ON
ALTER TABLE dbo.ResourceTD 

… I got following error below:

Msg 13541, Level 16, State 0, Line 85
Setting SYSTEM_VERSIONING to ON failed because history table ‘SQL2016POC.dbo.ResourceTDHistory’ contains invalid records with end of period set before start.

I found that the EffectiveStartDate & EffectiveEndDate columns values were not correct, actually EffectiveStartDate was greater than EffectiveEndDate. That might be due to some previous processing of business logic and manual error. So, I swapped the value for those records.

–> You will need to check for all possible scenarios until the Historical Table gets created successfully, some as follows (but not limited to these):

-- Scenario #1
SELECT * FROM ResourceTDHistory WHERE EffectiveStartDate IS NULL
SELECT * FROM ResourceTDHistory WHERE EffectiveEndDate IS NULL

-- Scenario #2
FROM ResourceTDHistory 
WHERE EffectiveStartDate > EffectiveEndDate


I faced these two issues related to this single table. You might face similar or other issues with other tables, based upon different scenarios.

I will update this post if I see any other issues while setting up Temporal tables. Or if you come across, please let me know by your comments, Thanks !!!

Check & Like my FB Page

Error Msg 8672, MERGE statement attempted to UPDATE or DELETE the same row more than once – MSDN TSQL forum

August 5, 2015 Leave a comment

–> Question:

I am trying to do update/insert operation using merge statement, but getting below error:

“The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.”

I am using SQL Server 2014. I knew there was a bug in older version of SQL Server (like version 2008). Does this issue still persist in latest version of SQL Server?

–> My Answer:
There might be 2 reasons:

1. Your source/target tables might be having DUPLICATES.

2. There might NO DUPLICATES, but you might be using NOLOCK hint? If yes, then your source table might be very big and going through constant changes (INSERT/UPDATE/DELETE), which might be causing PAGE-SPLITS and causing to read single source row TWICE. Try removing NOLOCK and run your MERGE stmt.

–> Another Answer by Naomi:

You need to fix the source of the problem, not apply patches. If your source has duplicate rows for the same key used to update, when how would you know which row will be used to update? The simple solution is to eliminate duplicates by either using correct combinations of keys to join or creating an extra key with ROW_NUMBER() approach.

Ref Link.