CREATE TABLE [dbo].[ATWorkItemReference] (
    [WorkItemReferenceId] INT            NOT NULL IDENTITY (1, 1),
    [WorkItemId]          INT            NOT NULL,
    [QueueId]             INT            NULL,
    [SourceUser]          NVARCHAR (255) NULL,
    [SourceQueueId]       INT            NULL,
    [ArrivalDate]         DATETIME       NULL,
    [EventID]             INT            NULL,
    [ExpirationTime]      DATETIME       NULL,
    [Expired]             INT            NOT NULL,
    [Successor]           INT            NULL,
    [ServerRequest]       INT            NULL,
    [ServerRequestParam]  INT            NULL,
    [Status]              INT            NOT NULL,
    [AssignedTo]          NVARCHAR (255) NULL,
    [LastAction]          NVARCHAR (255) NULL,
    [PendEventId]         INT            NULL,
    [PendQueueId]         INT            NULL,
    [Draft]               INT            NULL,
    [Priority]            INT            NULL,
    [PendExpirationTime]  DATETIME       NULL,
    [LastActionDate]      DATETIME       NULL,
    [LastViewDate]        DATETIME       NULL,
    CONSTRAINT [PK_WorkItemReference] PRIMARY KEY NONCLUSTERED ([WorkItemReferenceId] ASC),
    CONSTRAINT [ATWorkItemReference_ATWorkItem] FOREIGN KEY ([WorkItemId]) REFERENCES [dbo].[ATWorkItem] ([WorkItemId]),
    CONSTRAINT [ATWorkItemReference_Queue] FOREIGN KEY ([QueueId]) REFERENCES [dbo].[ATQueue] ([Id]),
    CONSTRAINT [ATWorkItemReference_SourceQueue] FOREIGN KEY ([SourceQueueId]) REFERENCES [dbo].[ATQueue] ([Id]),
    CONSTRAINT [ATWorkItemReference_SourceUser_ATUser] FOREIGN KEY ([SourceUser]) REFERENCES [dbo].[ATUser] ([UserName]),
    CONSTRAINT [ATWorkItemReference_AssignTo_ATUser] FOREIGN KEY ([AssignedTo]) REFERENCES [dbo].[ATUser] ([UserName]),
    CONSTRAINT [ATWorkItemReference_ATEventDefinition] FOREIGN KEY ([EventID]) REFERENCES [dbo].[ATEventDefinition] ([EventID]),
    CONSTRAINT [ATWorkItemReference_ATEventDefinition_Successor] FOREIGN KEY ([Successor]) REFERENCES [dbo].[ATEventDefinition] ([EventID])
);


GO
CREATE NONCLUSTERED INDEX [ATWIAndATWIRef]
    ON [dbo].[ATWorkItemReference]([WorkItemReferenceId] ASC, [WorkItemId] ASC);


GO
CREATE NONCLUSTERED INDEX [ATWorkItemReference_Composite1]
    ON [dbo].[ATWorkItemReference]([QueueId] ASC, [SourceQueueId] ASC, [ServerRequest] ASC);


GO
CREATE NONCLUSTERED INDEX [ATWorkItemReference_Composite2]
    ON [dbo].[ATWorkItemReference]([SourceUser] ASC, [ServerRequest] ASC);


GO
CREATE NONCLUSTERED INDEX [ATWorkItemReference_IX_EventId]
    ON [dbo].[ATWorkItemReference]([EventID] ASC);


GO
CREATE NONCLUSTERED INDEX [ATWorkItemReference_IX_Queue]
    ON [dbo].[ATWorkItemReference]([QueueId] ASC);


GO
CREATE NONCLUSTERED INDEX [ATWorkItemReference_IX_SQueue]
    ON [dbo].[ATWorkItemReference]([SourceQueueId] ASC);


GO
CREATE NONCLUSTERED INDEX [ATWorkItemReference_IX_Sucesor]
    ON [dbo].[ATWorkItemReference]([Successor] ASC);

GO
CREATE NONCLUSTERED INDEX [ATWorkItemReference_IX_AssignedTo]
    ON [dbo].[ATWorkItemReference]([AssignedTo] ASC);


GO
CREATE NONCLUSTERED INDEX [ATWorkItemReference_IX_WIId]
    ON [dbo].[ATWorkItemReference]([WorkItemId] ASC);


GO
CREATE NONCLUSTERED INDEX [ATWorkItemReferenceEventExpiredAndServer]
    ON [dbo].[ATWorkItemReference]([EventID] ASC, [Expired] ASC, [ServerRequest] ASC, [Status] ASC);


GO
CREATE NONCLUSTERED INDEX [ATWorkItemReferenceEventExpiredAndServerAndExpireDate]
    ON [dbo].[ATWorkItemReference]([EventID] ASC, [ExpirationTime] ASC, [Expired] ASC, [ServerRequest] ASC, [Status] ASC);


GO
CREATE NONCLUSTERED INDEX [ATWorkItemReferenceEventExpiredAndServerWithOrder]
    ON [dbo].[ATWorkItemReference]([WorkItemId] ASC, [ArrivalDate] ASC, [EventID] ASC, [Expired] ASC, [ServerRequest] ASC, [Status] ASC, [Priority] ASC);

