Sql-server – Lead/Lag Implementation in SQL Server 2008 R2: Max memory exceeded

performancequery-performancesql serversql-server-2008-r2window functions

Background

I'm trying to establish a "visits" sequence, wherein if an animal is detected at essentially the same place (General_Location) it counts as a single visit, but if it goes elsewhere and then back, that's an additional visit to the same location. So, if animal is detected in a Location sequence
A1, A2, A3, A3, A3, A1, B2, D4, A2
where e.g. all the A(n) locations belong to General_Location "A", this would have the first 6 detections as visit 1 (@A), next as visit 2 (@B), next as visit 3 (@D), next as visit 4 (back @A).

As LAG and LEAD are not available in SQL Server 2008R2 (nor is UNBOUNDED PRECEDING in the PARTITIONing clause), I'm attempting a work-around as described in this SQL Authority blog entry.

I'm running into memory issues (not to mention computation time) with the following:

WITH s AS (
    SELECT
        RANK() OVER (PARTITION BY det.Technology, det.XmitID ORDER BY DetectDate ASC, ReceiverID ASC) as DetID,
        COALESCE(TA.AnimalID, det.Technology+'-'+cast(da.XmitID AS nvarchar), 'BSVALUE999') as AnimalID,
        det.Technology, det.XmitID, DetectDate, det.location as Location, RL.General_Location as GLocation, ReceiverID
    FROM
        Detections_with_Location as det JOIN
        Receiver_Locations as RL
            ON det.Location=RL.Location LEFT OUTER JOIN
        Tagged_Animal as TA
            ON det.AnimalID=TA.AnimalID
)
INSERT INTO ##ttOrder_det (AnimalID, Technology, XmitID, DD, Location, GLocation, ReceiverID, DetID, PrevDD, BinInc)
    SELECT 
        s1.AnimalID, --was a coalesce
        s1.Technology, s1.XmitID, s1.DetectDate, s1.Location, s1.GLocation, s1.ReceiverID,
        s1.DetID, 
        sLAG.DetectDate,
        CASE WHEN sLAG.DetectDate IS NULL
            THEN 1 
            ELSE CASE WHEN sLAG.GLocation = s1.GLocation
                THEN 0
                ELSE 1
            END
        END AS BinInc
    FROM s as s1
    LEFT OUTER JOIN s AS sLAG ON
        s1.DetID = sLAG.DetID + 1 AND
        s1.AnimalID= sLAG.AnimalID --and s.Technology=sLAG.Technology and s.XmitID=sLAG.XmitID;

As various users (@MartinSmith, @Frisbee) have mentioned or alluded to, the use of AnimalID is not the complete primary key of Tagged_Animal, nor is defined in a UNIQUE constraint. However, the count of rows in the table where A.AnimalID=B.AnimalID AND A.TagSN<B.TagSN is (currently) zero. To make this query robust, I would have to enforce that being unique (or just drop TagSN from the PK).

Table & Index definitions

##ttOrder_det (temp table)

Indexes are currently created before filling the table; I am undergoing tests where I shift the NONCLUSTERED non-UNIQUE index creation to a position after the temp table is filled.

CREATE TABLE ##ttOrder_det (
    AnimalID nvarchar(50) not null,
    Technology varchar(25) not null,
    XmitID int not null,
    DD DateTime not null,
    [Location] [nvarchar](255) NULL,
    [GLocation] nvarchar(255) NULL,
    PrevDD DateTime NULL,
    ReceiverID int not null,
    DetID int NOT NULL,
    BinInc int NULL,
    BinNum int NULL,
CONSTRAINT [PK_ttRDA] PRIMARY KEY CLUSTERED 
    ([AnimalID] ASC, [DD] ASC, ReceiverID 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
CREATE NONCLUSTERED INDEX NIX_F on ##ttOrder_det (AnimalID ASC);
CREATE NONCLUSTERED INDEX NIX_VTC ON ##ttOrder_det (ReceiverID ASC, AnimalID ASC);
CREATE NONCLUSTERED INDEX NIX_TCD ON ##ttOrder_det (AnimalID ASC, DD ASC);
CREATE NONCLUSTERED INDEX NIX_R ON ##ttOrder_det (DetID ASC);
CREATE NONCLUSTERED INDEX NIX_A ON ##ttOrder_det (GLocation ASC);
CREATE NONCLUSTERED INDEX NIX_DD ON ##ttOrder_det (DD, PrevDD);
CREATE UNIQUE INDEX UX_CTR ON ##ttOrder_det (AnimalID ASC, DetID ASC);
CREATE NONCLUSTERED INDEX NIX_Bi ON ##ttOrder_det (BinInc ASC);
CREATE NONCLUSTERED INDEX NIX_CT ON ##ttOrder_det (XmitID ASC, Technology ASC);

Tagged_Animal

CREATE TABLE [dbo].[Tagged_Animal](
    [DateTagged] [datetime] NULL,
    [AnimalID] [nvarchar](50) NOT NULL,
    [TagSN] [nvarchar](50) NOT NULL,
    [XmitID] [int] NULL,
    [Technology] [varchar](25) NULL,
    [Animal_SubType] [nvarchar](50) NULL,
    [Species] [nvarchar](30) NULL,
    [StudyID] [nvarchar](50) NULL,
    [Weight] [float] NULL,
    [Length] [int] NULL,
    [Length_Type] [nvarchar](50) NULL,
    [Date_Released] [datetime] NULL,
    [Release_Location] [nvarchar](50) NULL,
    [Lat] [float] NULL,
    [Lon] [float] NULL,
    [Course_Dist_km] [float] NULL,
    [Sex] [nvarchar](255) NULL,
    [Life_Stage] [nvarchar](255) NULL,
    [Marking_Method] [nvarchar](255) NULL,
    [Tag_Type] [varchar](30) NULL,
    [Notes] [nvarchar](255) NULL,
 CONSTRAINT [PK_tbl_Tagged_Animal] PRIMARY KEY CLUSTERED 
(
    [AnimalID] ASC,
    [TagSN] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [I_TF_TagCode] ON [dbo].[Tagged_Animal] 
(
    [XmitID] ASC,
    [Technology] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Detections_with_Location

CREATE TABLE [dbo].[Detections_with_Location](
    [AnimalID] [nvarchar](50) NOT NULL,
    [XmitID] [int] NOT NULL,
    [Technology] [varchar](25) NOT NULL,
    [DetectDate] [datetime] NOT NULL,
    [ReceiverID] [int] NOT NULL,
    [Data] [float] NULL,
    [Units] [varchar](50) NULL,
    [Location] [nvarchar](255) NULL,
    [StartD] [datetime] NULL,
    [StopD] [datetime] NULL,
    [fname] [nvarchar](255) NULL,
    [notes] [nvarchar](max) NULL,
 CONSTRAINT [PK_dlwk] PRIMARY KEY CLUSTERED 
(
    [ReceiverID] ASC,
    [Technology] ASC,
    [XmitID] ASC,
    [DetectDate] ASC,
    [AnimalID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NIX_VTC] ON [dbo].[Detections_with_Location] 
(
    [ReceiverID] ASC,
    [AnimalID] ASC,
    [XmitID] ASC,
    [Technology] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NIX_TCpi] ON [dbo].[Detections_with_Location] 
(
    [XmitID] ASC,
    [Technology] ASC
)
INCLUDE ( [DetectDate],
[ReceiverID],
[Data],
[Units],
[Location]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NIX_TCD] ON [dbo].[Detections_with_Location] 
(
    [AnimalID] ASC,
    [XmitID] ASC,
    [Technology] ASC,
    [DetectDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NIX_F] ON [dbo].[Detections_with_Location] 
(
    [AnimalID] ASC
)
INCLUDE ( [XmitID],
[Technology],
[DetectDate],
[ReceiverID],
[Data],
[Units],
[Location]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NIX_DSS] ON [dbo].[Detections_with_Location] 
(
    [ReceiverID] ASC,
    [Location] ASC,
    [StartD] ASC,
    [StopD] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Receiver_Locations

CREATE TABLE [dbo].[Receiver_Locations](
    [Region] [nvarchar](50) NULL,
    [Location_Long] [nvarchar](50) NULL,
    [Location] [nvarchar](50) NOT NULL,
    [Lat] [float] NULL,
    [Lon] [float] NULL,
    [Altitude] [float] NULL,
    [Elevation] [float] NULL,
    [RiverKm] [float] NULL,
    [LocationType] [nvarchar](50) NULL,
    [General_Location] [nvarchar](50) NULL,
    [Nearest_Access] [nvarchar](50) NULL,
    [Responsible_Agent] [nvarchar](50) NULL,
    [Agent_Phone] [nvarchar](255) NULL,
    [Agent_Email] [nvarchar](255) NULL,
 CONSTRAINT [PK_tbl_Receiver_Locations] PRIMARY KEY CLUSTERED 
(
    [Location] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Table sizes

Tagged_Animals: 10 thousand
Detections_with_Location: 46+ million entries
Receiver_Locations: 800

Specific Errors Received

Could not allocate space for object 'dbo.SORT temporary run storage:
140737631617024' in database 'tempdb' because the 'PRIMARY' filegroup
is full. Create disk space by deleting unneeded files, dropping
objects in the filegroup, adding additional files to the filegroup, or
setting autogrowth on for existing files in the filegroup.

The transaction log for database 'tempdb' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column
in sys.databases (tempdb ACTIVE_TRANSACTION)

An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown –(if doing s as a direct select after about 33 million records).

(Estimated) Execution Plan Summary of initial code

INSERT cost 0%
SEQUENCE cost 0% but drawing from 9 different sub-steps. Those sub-steps (with typical costs) are an Index Insert (2%), followed by Sort (8%), followed by Table Spool (2%). NIX_A has a cost of 9% for Index Insert and neither NIX_TCD nor NIX_F have a Sort step; The Table Spool for NIX_F is free.
The Clustered Index Insert cost is 10%.
There's also a Sort cost of 2% and a Parallelism cost of 1% for distribution of streams.
For the SEQUENCE cost, it seems to add to 95%, with other steps costing 13%, so obviously there are some rounding "errors" somewhere, likely mostly in the 14% of Index Insert – Sort – Table Spool sequence.

Notes/Refs

LAG/LEAD implementation based on SQL Authority blog entry
See also this Stackexchange Thread

My Questions

Any suggestions to improve?
Can I partition also when I join the copies of s?
Would things improve if I make s into a discreet temporary table
and index it appropriately?
Would it be more efficient to create the non-UNIQUE indices in the temp tables after all the inserts are performed? I assume that the UNIQUE (and thus PRIMARY KEY) indices have to be created up-front to prevent key constraint violations.

To answer one of my own questions

Yes, yes it would. After further optimizations
- 21 minutes to fill temp table with data
- 1 minutes to index

This process previously went at least 1.5 hrs, errored-out and did not produce a results table. Before I started fiddling with the logic of the query, it would actually take 4+ hours before erroring out.

Server specifications:

Processor: Xeon E3-1240 V2 @ 3.4 GHz (4 core/8 thread)
Memory: 16 GB
Paging file: 16 GB on 111 GB SSD (52 GB free)
tempdb + my database on 223 GB SSD (119 GB free)

Current Status

See my posted solution/answer.

Best Answer

I'm submitting this as an answer, as I am currently avoiding the out-of-memory type errors as well as have reduced the time of run significantly (was 4+ hours, ending in failure; now is 1.25 hrs, ending in success). However, I'm sure that after about 10 million more records, this problem may reoccur, so I would appreciate any additional comments or answers aimed at making this more memory-efficient for the future.

The "solution" to this point was to drop unneeded fields and especially indices from the design of the temp tables. Additionally, index creation for non-constraint keys was deferred until after the table was filled.

To address the issue (pointed out first by @MartinSmith) of partitioning not matching the later JOIN, I created a field in a subquery which I then used for both PARTITIONing and for JOINing.

Code for the Q

set nocount on;
SET XACT_ABORT ON;
SELECT
    cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
    'started' as reason; --last run 2015-12-16 18:22:02
GO

CREATE TABLE ##ttOrder_det (
    AnimalID nvarchar(50) not null,
    DD DateTime not null,
    ReceiverID int NOT NULL,
    Location nvarchar(255) NOT NULL,
    GLocation nvarchar(255) NULL,
    DetID int NOT NULL,
    BinIncORNum int NULL, -- first pass=Inc, second pass=Num
CONSTRAINT [PK_ttRDA] PRIMARY KEY CLUSTERED 
    ([AnimalID] ASC, [DD] ASC, ReceiverID 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
CREATE UNIQUE INDEX UX_CTR ON ##ttOrder_det (AnimalID ASC, DetID ASC);
SELECT
    cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
   'created first temporary table' as reason;  --last run 2015-12-16 18:22:02
GO
WITH s AS (
    SELECT 
        AnimalID, DetectDate,
        ReceiverID, Location, GLocation,
        ROW_NUMBER() OVER (
            PARTITION BY AnimalID ORDER BY DetectDate ASC, ReceiverID ASC
        ) as DetID
    FROM (
        SELECT 
            COALESCE (
                TF.AnimalID,
                Det.Technology+'-'+cast(Det.XmitID AS nvarchar(10)),
                Det.AnimalID,
                N'BSVALUE999'
            ) as AnimalID,
            DetectDate,
            ReceiverID,
            COALESCE (
                Det.location,
                N'Unknown Location'
            ) as Location,
            COALESCE (
                ML.General_Location,
                N'Invalid General Location - Orphaned Receiver'
            ) as GLocation
        FROM
            Detections_with_Location as Det LEFT OUTER JOIN
            Receiver_Locations as ML ON Det.Location=ML.Location LEFT OUTER JOIN
            Tagged_Animal as TF ON Det.AnimalID=TF.AnimalID
    ) AS T
)
INSERT INTO ##ttOrder_det (AnimalID, DD, ReceiverID, Location, GLocation, DetID, BinIncORNum)
    SELECT 
        s1.AnimalID,
        s1.DetectDate, s1.ReceiverID, s1.Location, s1.GLocation,
        s1.DetID, 
        CASE WHEN sLg.DetectDate IS NULL
            THEN 1 
            ELSE CASE WHEN sLg.GLocation = s1.GLocation
                THEN 0
                ELSE 1
            END
        END AS BinInc
    FROM s as s1
    LEFT OUTER JOIN s AS sLg ON
        s1.AnimalID= sLg.AnimalID AND
        s1.DetID = sLg.DetID + 1
SELECT
    cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
   'filled first temp table' as reason,
   COUNT(*) as SizeOfFirstTemp FROM ##ttOrder_det; --2015-12-16 18:43:03, 46627879
GO
CREATE NONCLUSTERED INDEX NIX_F on ##ttOrder_det (AnimalID ASC);
CREATE NONCLUSTERED INDEX NIX_R ON ##ttOrder_det (DetID ASC);
--dropped several additional indices: `NIX_`s VTC, TCD, A, DD, Bi
SELECT
    cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
    'indexed first temp table' as reason; --2015-12-16 18:44:12
GO

Quick comments

I switched to using ROW_NUMBER instead of RANK. This is non-deterministic, but at least will result in no "ties", which would result in breaking of the LAG implementation around that point. Ties shouldn't exist, but this is just future-proofing against General_Locations that are very close together that co-detect the same transmission.

Again, as two users pointed out in the comments above, I'm not using the full PK of the Tagged_Animal table, so there's a possibility that there be a JOIN with an ambiguous AnimalID. However, at present, both AnimalID and TagSN are UNIQUE, albeit unconstrained.

I was planning on dropping the ReceiverID field in favor of using the Location, but I had a period in which there were two Receivers deployed at the same location (one receiver was presumed lost but later found) which did indeed detect the same animal at the same time

Follow-up code to complete the task

CREATE TABLE ##ttOrder_det2 (
    AnimalID nvarchar(50) not null,
    DetID int NOT NULL,
    BinNum int NULL,
CONSTRAINT [PK_ttRDA2] PRIMARY KEY CLUSTERED 
    ([AnimalID] ASC, [DetID] 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
SELECT
    cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
    'second temp table created' as reason; --2015-12-16 18:44:15
GO
-- SET XACT_ABORT ON will cause the transaction to be uncommittable
-- when the constraint violation occurs. 
BEGIN TRANSACTION
  BEGIN TRY
   DECLARE @AnimalID as nvarchar(50);
    DECLARE @DetID as int;
    DECLARE @BinInc as int;
    DECLARE @BinNum as int;
    DECLARE @AnimalVisit as CURSOR;
    SET @AnimalVisit = CURSOR FOR SELECT AnimalID, DetID, BinIncORNum FROM ##ttOrder_det ORDER BY AnimalID, DetID;
    OPEN @AnimalVisit;
    FETCH NEXT FROM @AnimalVisit INTO @AnimalID, @DetID, @BinInc;
    WHILE @@FETCH_STATUS = 0
      BEGIN
        IF (@DetID <= 1) SET @BinNum = 0;
        SET @BinNum += @BinInc;
        INSERT INTO ##ttOrder_det2 (AnimalID, DetID, BinNum) VALUES (@AnimalID, @DetID, @BinNum);
        FETCH NEXT FROM @AnimalVisit INTO @AnimalID, @DetID, @BinInc;
      END
    CLOSE @AnimalVisit;
    DEALLOCATE @AnimalVisit;
  END TRY
  BEGIN CATCH
    exec sp_lock; -- added to display the open locks after the timeout
--    exec sp_who2; -- shows the active processes
    EXECUTE usp_GetErrorInfo;
    --RETURN -- ignoring this error for brevity
        -- Test XACT_STATE:
        -- If 1, the transaction is committable.
        -- If -1, the transaction is uncommittable and should 
        --     be rolled back.
        -- XACT_STATE = 0 means that there is no transaction and
        --     a commit or rollback operation would generate an error.

    -- Test whether the transaction is uncommittable.
    IF (XACT_STATE()) = -1
    BEGIN
        SELECT
            cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
            'The transaction is in an uncommittable state. Rolling back transaction.' as reason
            SET XACT_ABORT off
            RETURN -- 1 --error
        ROLLBACK TRANSACTION;
    END;
    -- Test whether the transaction is committable.
    IF (XACT_STATE()) = 1
    BEGIN
        SELECT
            cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
            'The transaction is committable. Committing transaction.' as reason
        COMMIT TRANSACTION;   
    END;
  END CATCH;
  IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO
SELECT 
    cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
    'filled second temp table' as reason,
    COUNT(*) as Table2Size from ##ttOrder_det2; --2015-12-16 19:11:17, 46627879
GO
CREATE NONCLUSTERED INDEX NIX_CT2 ON ##ttOrder_det2 (AnimalID ASC);
CREATE NONCLUSTERED INDEX NIX_R2 ON ##ttOrder_det2 (DetID ASC);
SELECT
    cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
    'indexed second temp table' as reason;
--EXEC tempdb.dbo.usp_PruneTemp;
GO
BEGIN TRANSACTION
    BEGIN TRY
        UPDATE a
            SET a.BinIncORNum=b.BinNum
        FROM ##ttOrder_det AS a
        INNER JOIN ##ttOrder_det2 AS b ON 
            a.AnimalID=b.AnimalID AND a.DetID=b.DetID;
        SELECT
            cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
            'backfilled first temp table with info from second table' as reason,
            @@ROWCOUNT as EntriesAffected;  --2015-12-16 19:19:54, 46627879
        DROP TABLE ##ttOrder_det2;
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        SELECT name, log_reuse_wait_desc FROM sys.databases
        SELECT
          cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
          'ERROR: backfilling first temp table, see sys.databases info' as reason;
        EXECUTE usp_GetErrorInfo;
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SET XACT_ABORT off
        RETURN --1 --error
    END CATCH
--IF @@TRANCOUNT > 0 COMMIT TRANSACTION
GO
CREATE TABLE derived__AnimalVisits (
    AnimalID nvarchar(50) not null,
    DetectDate DateTime not null,
    ReceiverID int NOT NULL,
    Location nvarchar(255) NOT NULL,
    GeneralLocation nvarchar(255) NULL,
    DetOrder int NOT NULL,
    VisitNum int NOT NULL,
CONSTRAINT [PK_dFV] PRIMARY KEY CLUSTERED 
    (AnimalID ASC, DetectDate ASC, ReceiverID 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
CREATE UNIQUE INDEX UX_CTR ON derived__AnimalVisits (AnimalID ASC, DetOrder ASC);
SELECT 
    cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
    'created persistent derived table' as reason; --2015-12-16 19:20:53
GO

--DECLARE @FVError as int = 0;
BEGIN TRY
    INSERT INTO derived__AnimalVisits (AnimalID, DetectDate, ReceiverID, Location, GeneralLocation, DetOrder, VisitNum)
        SELECT AnimalID, DD, ReceiverID, Location, GLocation, COALESCE(DetID,0), COALESCE(BinIncORNum,0) FROM ##ttOrder_det;
END TRY
BEGIN CATCH
    SELECT
        cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
        'ERROR: filling persistent derived table' as reason;
    EXECUTE usp_GetErrorInfo;
    SET XACT_ABORT off
    RETURN --1 --error
END CATCH
CREATE NONCLUSTERED INDEX NIX_CT ON derived__AnimalVisits (AnimalID ASC);
CREATE NONCLUSTERED INDEX NIX_VTC ON derived__AnimalVisits (ReceiverID ASC, AnimalID ASC);
CREATE NONCLUSTERED INDEX NIX_TCD ON derived__AnimalVisits (AnimalID ASC, DetectDate ASC);
CREATE NONCLUSTERED INDEX NIX_R ON derived__AnimalVisits (DetOrder ASC);
CREATE NONCLUSTERED INDEX NIX_Bi ON derived__AnimalVisits (VisitNum ASC);
SELECT
    cast(cast(current_timestamp as datetime2) as varchar(19)) as time,
    'indexed / now cleaning up' as reason; --2015-12-16 19:31:18
GO
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N'[tempdb].[dbo].[##ttOrder_det2]') AND xtype=N'U')
    DROP TABLE tempdb.[dbo].[##ttOrder_det2];
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N'[tempdb].[dbo].[##ttOrder_det]') AND xtype=N'U')
    DROP TABLE tempdb.[dbo].[##ttOrder_det];
SET XACT_ABORT off
--cleaning up of transaction logs, etc done at 2015-12-16 19:39:07