CREATE TABLE [dbo].[ATWorkItem] (
    [WorkItemId]   INT              NOT NULL IDENTITY (1, 1),
    [WorkItemName] NVARCHAR (255)   NOT NULL,
    [Creator]      NVARCHAR (255)   NOT NULL,
    [Created]      DATETIME         NOT NULL DEFAULT GETDATE(),
    [Priority]     INT              NOT NULL,
    [DueDate]      DATETIME         NULL,
    [ATComment]    NVARCHAR (255)   NULL,
    [LockedBy]     NVARCHAR (255)   NULL,
    [ProcessID]    INT              NOT NULL,
    [Expired]      INT              NULL,
    [ObjectId]     UNIQUEIDENTIFIER NULL,
    CONSTRAINT [PK_WorkItem] PRIMARY KEY NONCLUSTERED ([WorkItemId] ASC),
    CONSTRAINT [ATWorkItem_LockedByUser] FOREIGN KEY ([LockedBy]) REFERENCES [dbo].[ATUser] ([UserName]),
    CONSTRAINT [ATWorkItem_Creator] FOREIGN KEY ([Creator]) REFERENCES [dbo].[ATUser] ([UserName]),
    CONSTRAINT [ATWorkItem_Process] FOREIGN KEY ([ProcessID]) REFERENCES [dbo].[ATProcessDefinition] ([ProcessID])
);


GO
CREATE NONCLUSTERED INDEX [ATWIAndLockedBy]
    ON [dbo].[ATWorkItem]([WorkItemId] ASC, [LockedBy] ASC);


GO
CREATE NONCLUSTERED INDEX [ATWorkItem_Composite1]
    ON [dbo].[ATWorkItem]([Creator] ASC, [Created] ASC);


GO
CREATE NONCLUSTERED INDEX [ATWorkItem_IX_Creator]
    ON [dbo].[ATWorkItem]([Creator] ASC);


GO
CREATE NONCLUSTERED INDEX [ATWorkItem_IX_LockedBy]
    ON [dbo].[ATWorkItem]([LockedBy] ASC);


GO
CREATE NONCLUSTERED INDEX [ATWorkitem_IX_ProcessId]
    ON [dbo].[ATWorkItem]([ProcessID] ASC);

