SQL Server Error 601: Could not Continue Scan with NOLOCK due to Data Movement

We have noticed the following error message periodically during mass data update on one of our productions servers: ‘Error 601: Could not continue scan with NOLOCK due to data movement’. This server is hosting instance of SQL Server 2008R2 SP1 Standard edition. After some research we have found a detailed description of this issue in this KB article: http://support.microsoft.com/kb/960770/en-us.

In our case the error was generated from a code similar to this pattern:

DECLARE @TempTable TABLE (...)

INSERT INTO @TempTable
    SELECT * FROM ....

INSERT INTO @TempTable
    select *
    from ( select distinct * from @TempTable ) tt
        cross join dbo.TableA a
    where 
        a.Col1 >=  1

The issue is that data are inserted into @TempTable at the same time when they are retrieved. Writing into this table is causing the reallocation of data pages which should first be read and at the time of reading they aren’t located at the expected place.

A simple and intuitive solution is code refactoring: Data are inserted into #Distinct temporary table first and after that used to prepare the final data to be inserted into @TempTable. This way we can prevent error to be fired.

DECLARE @TempTable TABLE (...)

INSERT INTO @TempTable
    SELECT * FROM ....

SELECT DISTINCT * 
    INTO #Distinct
FROM @TempTable

INSERT INTO @TempTable
    SELECT *
    FROM #Distinct tt
        cross join dbo.TableA a
    WHERE 
        a.Col1 >=  1
GO