-------------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID('QueryWithOnlineOrOffline','P') IS NOT NULL
BEGIN
	DROP PROCEDURE [dbo].[QueryWithOnlineOrOffline]
END
GO
-- Helper stored procedure to execute cluster index changes with online or offline based on SQL Server Edition
CREATE PROCEDURE [dbo].[QueryWithOnlineOrOffline]
	@Query NVARCHAR(MAX)
AS
BEGIN
	DECLARE @OnlineState AS NVARCHAR(5) = 'OFF'
	
	-- Reference: https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql
	IF 3 = SERVERPROPERTY('EngineEdition')
		SET @OnlineState = 'ON'

	DECLARE @FinalQuery AS NVARCHAR(MAX) = REPLACE(@Query, '<@OnlineState>', @OnlineState)

	EXEC (@FinalQuery)
END
GO
-------------------------------------------------------------------------------------------------------------------------------

CREATE INDEX IX_BSCRIPTS ON dbo.BranchScripts(ParentID, Name)
go

CREATE CLUSTERED INDEX IX_CALLBACKEVENTS ON dbo.CallbackEvents(EvTime) 
go

CREATE CLUSTERED INDEX IX_FREQUSEDITEMS ON dbo.FreqUsedItems(CatID)
go

CREATE INDEX IX_MAILCATCODE ON MAILCATEGORIES(Code)
go

CREATE UNIQUE CLUSTERED INDEX IX_MAILATTACHMENT ON dbo.MailAttachment(MsgID, AttachName)
go

Create CLUSTERED Index IX_CUSTEMAIL On dbo.MailCustomer(CustEmail)
go

CREATE CLUSTERED INDEX IX_CLASSDATE ON dbo.MailClassification(ClassificationDate) 
go

Create CLUSTERED Index IX_MAILEVENTS On dbo.MailEvents(EvDate) 
go

Create CLUSTERED Index IX_CUSTEMAILID On dbo.MailIncident(CustEmailID, IncidentID) 
go

CREATE NONCLUSTERED INDEX [IX_UNRESPONDEDMAIL]
ON [dbo].[MailMessage]
([RouteToType],[RouteToID],[Status],[CompanyID],[IncidentID],[ExpiryTime])
INCLUDE ([MsgID],[DateRecv])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX IX_INCIDENTID ON dbo.MailMessage(IncidentID, MsgId) 
go

CREATE NONCLUSTERED INDEX [IX_MailMessage_RouteToTypeStatus]
ON [dbo].[MailMessage] ([RouteToType],[RouteToID],[Status])
GO 

CREATE CLUSTERED INDEX IX_MAILMESSAGEEX ON dbo.MailMessageEx(MsgID, PropName)
go

CREATE NONCLUSTERED INDEX IX_MAILMESSAGEEX_PROPNAMEFLAG ON MailMessageEx(PropName, PropFlags) INCLUDE (MsgID)
go

CREATE NONCLUSTERED INDEX [IX_MAILMESSAGEEX_PROPNAMEMSGID]
ON MailMessageEx(MsgID, PropName)
INCLUDE ([PropValue],[PropFlags])
GO

CREATE CLUSTERED INDEX IX_MAILNOTES ON dbo.MailNotes(MsgID) 
go

CREATE CLUSTERED INDEX IX_PHONEEVENTS ON dbo.PhoneEvents(EvTime) 
go

CREATE INDEX IX_PHONEDIRECTORYGID ON dbo.PhoneDirectory(GroupId) 
go

CREATE CLUSTERED INDEX IX_PHONEDATACALLDATE ON dbo.PhoneData(CallDate)
go

CREATE INDEX IX_PHONEDATAKEY1 ON dbo.PhoneData(Key1)
go

CREATE INDEX IX_PHONEDATAKEY2 ON dbo.PhoneData(Key2)
go

CREATE INDEX IX_QUEUES ON dbo.Queue(Status, ParentID, Name)
go

CREATE CLUSTERED INDEX IX_QUEUEEVENTS ON dbo.QueueEvents(EvTime) 
go

CREATE CLUSTERED INDEX IX_REPEVENTS ON dbo.RepEvents(EvTime) 
go

CREATE INDEX IX_REPORTS ON dbo.Reports(Owner)
go


CREATE CLUSTERED INDEX IX_SESSIONEVENTSTIME ON dbo.SessionEvents(EvTime) 
go

CREATE INDEX IX_SESSIONEVENTSID ON dbo.SessionEvents(SessionID)
go

CREATE CLUSTERED INDEX IX_SESSIONINFO ON dbo.SessionInfo(CreateTime)
go

CREATE INDEX IX_SessionEmail ON SessionInfo(CustEmail)
go

CREATE INDEX IX_SSUMMARYEX ON dbo.SessionSummaryEx(CatIDs)
go

CREATE CLUSTERED INDEX IX_STRANSCRIPTTIME ON dbo.SessionTranscript(EvType, EvTime) 
go

CREATE INDEX IX_STRANSCRIPTID ON dbo.SessionTranscript(SessionID)
go


CREATE INDEX IX_TIMESCRIPTS ON dbo.TimeScripts(ParentID, Name)
go

CREATE INDEX IX_WARMTRANSINFO ON WarmTransInfo(SessionID)
go

CREATE INDEX IX_WARMTRANSTRANSCRIPT ON WarmTransTranscript(ID)
go

CREATE UNIQUE INDEX IX_Questionnaires_questid ON Questionnaires(questid)
go

CREATE NONCLUSTERED INDEX [IX_EvID_SessionID_EvType] ON [dbo].[SessionEvents] 
(
	[EvID] ASC,
	[SessionID] ASC,
	[EvType] ASC
)
go

CREATE NONCLUSTERED INDEX [IX_EvType_EvID_EvTime_EvParam] ON [dbo].[SessionEvents] 
(
	[EvType] ASC,
	[EvID] ASC,
	[EvTime] ASC,
	[EvParam] ASC
)
INCLUDE (SessionID)
go

CREATE CLUSTERED INDEX IX_SYSTIMESSTATS_DATE ON SystemTimedStats(StatDate)
go

CREATE CLUSTERED INDEX IX_USERTIMESSTATS_DATE ON UserTimedStats(StatDate)
go

CREATE CLUSTERED INDEX IX_QUEUETIMESSTATS_DATE ON QueueTimedStats(StatDate)
go

CREATE CLUSTERED INDEX IX_MBOXTIMESSTATS_DATE ON MailboxTimedStats(StatDate)
go


CREATE INDEX IX_PROACTSESS_BROWSER ON dbo.ProactiveSessions(BrowserID)
go

CREATE INDEX IX_PROACTSESS_OS ON dbo.ProactiveSessions(BrowserOS)
go

CREATE NONCLUSTERED INDEX IX_PROACTSESS_PROACTIVESESSIONID ON dbo.ProactiveSessions(ProactiveSessionID)
go


CREATE INDEX IX_SALESEVENTVALUE ON SalesEvents(SaleValue)
go 

Create Index IX_USERPOSITIONS_POSUSER on UserPositions(PositionID,UserID)
go

Create Index IX_USERROLES_ROLEUSER on UserRoles(RoleID,UserID)
go

Create Index IX_POSROLES_ROLEPOS on PositionRoles(RoleID,PositionID)
go

Create Index IX_MAILCLASSIFICATION_MSGID on MailClassification(MsgId)
go

CREATE NONCLUSTERED INDEX IX_MAILCLASSIFICATION_IDMSG ON dbo.MailClassification (ClassificationID, MsgID)
go

Create Index IX_MAILREPLY_REPLYDATE on MailReply(ReplyDate)
go

CREATE INDEX [ix_MailReply_AgentID_ReplyTypeReplyDate_includes] ON [MailReply] ([AgentID], [ReplyType], [ReplyDate],[ReplySubject] )  
INCLUDE ([ReplyToID], [ReplyTo], [ReplyCC], [ReplyBCC], [ReplySize], [Attachments], [ReplyFrom])


Create Index IX_PROACTIVEEVENTS_TIME on ProactiveEvents(EvTime)
go

Create Index IX_KBEVENTS_DATE on KBEvents(EvDate)
go

Create Index IX_SALESEVENTS_TIME on SalesEvents(EvTime)
go


Create Index IX_MAILNEW_NEWDATE on MailNew(NewDate)
go

Create Index IX_MAILNEW_NEWTO on MailNew(NewTo) INCLUDE (ID, AgentID, NewDate, NewFrom, NewCc, NewBcc, NewSubject)
go

Create Index IX_MAILNEW_AGENTID on MailNew(AgentID)
go

CREATE NONCLUSTERED INDEX [IX_MAILMESSAGE_AGENTID] ON dbo.[MailMessage]
(
	[AgentID] ASC
)
INCLUDE ( 	[MsgID],	[Status],	[ApprovalStatus]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_MAILMESSAGE_STATUS] ON [dbo].[MailMessage]
([Status],[CompanyID],[ApprovalStatus])
INCLUDE ([MsgID],[RouteToType],[RouteToID],[ApprovalTimeout])
GO

Create Index IX_MAILEVENTS_MSGID on MailEvents(EvMsgID, EvType) INCLUDE (EvParam2)
go

Create Index IX_MAILNEWEX_PROPFLAGS on MailNewEx(PropName, PropFlags)
go

Create Index IX_MAILCUST_NAME On dbo.MailCustomer(CustName)
go

Create Index IX_MAILCUST_ACCOUNT On dbo.MailCustomer(CustAccount)
go

Create Index IX_MAILCUST_DOMAIN On dbo.MailCustomer(CustDomain)
go

CREATE INDEX IX_MMCUSTEMAILID ON dbo.MailMessage(CustEmailID)
go

CREATE INDEX IX_PORTALEVENTS_DATE ON dbo.PortalEvents(evDate)
go

CREATE INDEX IX_PROACTIVEVISITORS_SDATE ON dbo.ProactiveVisitors(StartTime)
go

CREATE INDEX IX_PROACTIVEVISITORS_EDATE ON dbo.ProactiveVisitors(EndTime)
go

CREATE INDEX IX_PROACTIVEMISSEDOPS_DATE ON dbo.ProactiveMissedOps(evDate)
go

CREATE INDEX IX_CONVERSIONSALES_SESSION ON dbo.ConversionSales(SessionID)
go

CREATE INDEX IX_SESSIONINFO_PERF ON SessionInfo	(CustName, SessionID, QueueID)
go

CREATE NONCLUSTERED INDEX IX_SESSIONINFOEXTERNALS_DATA ON dbo.SessionInfoExternals (DataName, DataValue) INCLUDE (SessionID)
go
-- foriegn key relationships


ALTER TABLE [dbo].[Department] ADD 
	CONSTRAINT [FK_Department_Department] FOREIGN KEY 
	(
		[pid]
	) REFERENCES [dbo].[Department] (
		[ID]
	)
go

ALTER TABLE [dbo].[MailboxPermissions] ADD 
	CONSTRAINT [FK_MailboxPermissions_Mailboxes] FOREIGN KEY 
	(
		[MailboxID]
	) REFERENCES [dbo].[Mailbox] (
		[MailboxID]
	),
	CONSTRAINT [FK_MailboxPermissions_Roles] FOREIGN KEY 
	(
		[RoleID]
	) REFERENCES [dbo].[Roles] (
		[ID]
	)
go

ALTER TABLE [dbo].[Position] ADD 
	CONSTRAINT [FK_Position_Department] FOREIGN KEY 
	(
		[department]
	) REFERENCES [dbo].[Department] (
		[ID]
	),
	CONSTRAINT [FK_Position_Position] FOREIGN KEY 
	(
		[pid]
	) REFERENCES [dbo].[Position] (
		[ID]
	)
go

ALTER TABLE [dbo].[ServiceLinePermissions] ADD 
	CONSTRAINT [FK_ServiceLinePermissions_Roles] FOREIGN KEY 
	(
		[RoleID]
	) REFERENCES [dbo].[Roles] (
		[ID]
	),
	CONSTRAINT [FK_ServiceLinePermissions_ServiceLines] FOREIGN KEY 
	(
		[ServiceLineID]
	) REFERENCES [dbo].[Queue] (
		[QueueID]
	)
go

ALTER TABLE [dbo].[UserRoles] ADD 
	CONSTRAINT [FK_AgentRoles_Roles] FOREIGN KEY 
	(
		[RoleID]
	) REFERENCES [dbo].[Roles] (
		[ID]
	),
	CONSTRAINT [FK_AgentRoles_Users] FOREIGN KEY 
	(
		[UserID]
	) REFERENCES [dbo].[Users] (
		[ID]
	)
go

ALTER TABLE [dbo].[PositionRoles] ADD 
	CONSTRAINT [FK_PositionRoles_Position] FOREIGN KEY 
	(
		[PositionID]
	) REFERENCES [dbo].[Position] (
		[ID]
	),
	CONSTRAINT [FK_PositionRoles_Roles] FOREIGN KEY 
	(
		[RoleID]
	) REFERENCES [dbo].[Roles] (
		[ID]
	)
go

ALTER TABLE [dbo].[UserPositions] ADD 
	CONSTRAINT [FK_AgentPositions_Position] FOREIGN KEY 
	(
		[PositionID]
	) REFERENCES [dbo].[Position] (
		[ID]
	),
	CONSTRAINT [FK_AgentPositions_Users] FOREIGN KEY 
	(
		[UserID]
	) REFERENCES [dbo].[Users] (
		[ID]
	)
go


ALTER TABLE [dbo].[UserProperties] ADD 
	CONSTRAINT [FK_UserProperties_User] FOREIGN KEY 
	(
		[OwnerID]
	) REFERENCES [dbo].[Users] (
		[ID]
	)
go


ALTER TABLE [dbo].[DepartmentPermissions] ADD 
	CONSTRAINT [FK_DepartmentPermissions_Dept] FOREIGN KEY 
	(
		[DepartmentID]
	) REFERENCES [dbo].[Department] (
		[ID]
	),
	CONSTRAINT [FK_DepartmentPermissions_Roles] FOREIGN KEY 
	(
		[RoleID]
	) REFERENCES [dbo].[Roles] (
		[ID]
	)
go

ALTER TABLE [dbo].[RolePermissions] ADD 
	CONSTRAINT [FK_RolePermissions_Role2] FOREIGN KEY 
	(
		[RoleID2]
	) REFERENCES [dbo].[Roles] (
		[ID]
	),
	CONSTRAINT [FK_RolePermissions_Roles] FOREIGN KEY 
	(
		[RoleID]
	) REFERENCES [dbo].[Roles] (
		[ID]
	)
go

ALTER TABLE [dbo].[UserPermissions] ADD 
	CONSTRAINT [FK_UserPermissions_Users] FOREIGN KEY 
	(
		[UserID]
	) REFERENCES [dbo].[Users] (
		[ID]
	),
	CONSTRAINT [FK_UserPermissions_Roles] FOREIGN KEY 
	(
		[RoleID]
	) REFERENCES [dbo].[Roles] (
		[ID]
	)
go

ALTER TABLE dbo.LayoutPermissions ADD 
	CONSTRAINT FK_LayoutPermissions_Layout FOREIGN KEY 
	(
		LayoutID, OwnerID
	) REFERENCES dbo.Layouts (
		LayoutID, LayoutOwner
	),
	CONSTRAINT FK_LayoutPermissions_Roles FOREIGN KEY 
	(
		RoleID
	) REFERENCES dbo.Roles (
		ID
	)
go

CREATE NONCLUSTERED INDEX IX_MailDomainList ON dbo.MailDomainList
(Used) Include (id, domainname, status) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE NONCLUSTERED INDEX [NCI_MailTemplate_ID_Name] ON [dbo].[MailTemplate]
([TemplateID],
[Name]
)
GO

CREATE NONCLUSTERED INDEX [NCI_MailTemplate_Name] ON [dbo].[MailTemplate]
(
[Name]
)
go
CREATE NONCLUSTERED INDEX [NCI_ETMP_ID_UID_TYPE] ON [dbo].[ETManagementPermissions]
([uid],
[type],
[folderid]
)
GO
CREATE NONCLUSTERED INDEX [NCI_Properties_Type_Status] ON [dbo].[Properties]
([Status],
[Type]
)
GO
CREATE NONCLUSTERED INDEX [NCI_Properties_ID_Type] ON [dbo].[Properties]
([Property_ID],
[Type]
)
GO

IF EXISTS (SELECT TOP 1 1 FROM sysindexes WITH (Nolock) WHERE name = 'IX_AGENTLOGONSTATUS_UPDATEDVERSION' AND id = object_id('dbo.AgentLogonStatus'))
BEGIN
	DROP INDEX dbo.AgentLogonStatus.IX_AGENTLOGONSTATUS_UPDATEDVERSION
END
go

CREATE  NONCLUSTERED INDEX IX_AGENTLOGONSTATUS_UPDATEDVERSION ON dbo.AgentLogonStatus (UpdatedVersion) INCLUDE (UserID, ServerIP, UserStatus)
go

IF EXISTS (SELECT TOP 1 1 FROM sysindexes WITH (Nolock) WHERE name = 'IX_MAILDRAFTNOTES' AND id = object_id('dbo.MailDraftNotes'))
	DROP INDEX dbo.MailDraftNotes.IX_MAILDRAFTNOTES
go

CREATE NONCLUSTERED INDEX IX_MAILDRAFTNOTES ON MailDraftNotes (DraftID, AgentID)
go

CREATE NONCLUSTERED INDEX IX_SpellCheckLangsAudit ON dbo.SpellCheckLangsAudit (	LangCode )
go

IF EXISTS (SELECT TOP 1 1 FROM sysindexes WITH (Nolock) WHERE name = 'IX_MAILFAILEDINPARSE' AND id = OBJECT_ID('dbo.mailsFailedInParse'))
	DROP INDEX dbo.mailsFailedInParse.IX_MAILFAILEDINPARSE
go

CREATE NONCLUSTERED INDEX IX_MAILFAILEDINPARSE ON dbo.mailsFailedInParse(MailboxID)
go

IF EXISTS (SELECT TOP 1 1 FROM sysindexes WHERE NAME = 'IX_WORKFLOWTRACE_WORKFLOWID')
	DROP INDEX dbo.workflowtrace.IX_WORKFLOWTRACE_WORKFLOWID
go

CREATE NONCLUSTERED INDEX IX_WORKFLOWTRACE_WORKFLOWID ON dbo.workflowtrace(workflowid) include (id, objectid, executiondate, eventtype)
go

IF EXISTS (SELECT TOP 1 1 FROM sysindexes WHERE NAME = 'IX_WORKFLOWFILTERTRACE_WORKFLOWTRACEID')
	DROP INDEX dbo.WorkflowFilterTrace.IX_WORKFLOWFILTERTRACE_WORKFLOWTRACEID
go

CREATE NONCLUSTERED INDEX IX_WORKFLOWFILTERTRACE_WORKFLOWTRACEID ON dbo.WorkflowFilterTrace(workflowtraceid) include (id, filterid, executiondate, status)
go

CREATE INDEX [ix_WorkflowFilterCondition_workflowid] ON [dbo].[WorkflowFilterCondition] ([workflowid])
GO

IF EXISTS (SELECT name FROM sys.indexes WITH (Nolock) WHERE name = N'IX_MAILGREETINGS_FID_MGT_AT')
    DROP INDEX IX_MAILGREETINGS_FID_MGT_AT ON dbo.MailGreetings ;
GO

CREATE NONCLUSTERED INDEX IX_MAILGREETINGS_FID_MGT_AT ON dbo.MailGreetings (FolderID,MailGreetType,AccessType);
GO


IF EXISTS (SELECT name FROM sys.indexes WITH (Nolock) WHERE name = N'IX_MAILCATEGORIES_FID_CT_AT')
    DROP INDEX IX_MAILCATEGORIES_FID_CT_AT ON dbo.MailCategories ;
GO

CREATE NONCLUSTERED INDEX IX_MAILCATEGORIES_FID_CT_AT ON dbo.MailCategories  (FolderID,CategoryType,AccessType);
GO

IF EXISTS (SELECT name FROM sys.indexes WITH (Nolock) WHERE name = N'IX_ATTACHMENTFOLDERS_PID')
    DROP INDEX IX_ATTACHMENTFOLDERS_PID ON dbo.AttachmentFolders ;
GO

CREATE NONCLUSTERED INDEX IX_ATTACHMENTFOLDERS_PID ON dbo.AttachmentFolders (PID);
GO

IF EXISTS (SELECT name FROM sys.indexes WITH (Nolock) WHERE name = N'IX_ATTACHMENTLIBRARY_FOLDERID')
    DROP INDEX IX_ATTACHMENTLIBRARY_FOLDERID ON dbo.AttachmentLibrary ;
GO

CREATE NONCLUSTERED INDEX IX_ATTACHMENTLIBRARY_FOLDERID ON dbo.AttachmentLibrary (folderid);
GO

IF EXISTS (SELECT name FROM sys.indexes WITH (Nolock) WHERE name = N'IX_USERMAILBOXPERMISSIONSSTAGE1')
    DROP INDEX IX_USERMAILBOXPERMISSIONSSTAGE1 ON dbo.UserMailboxPermissionsStage1 ;
GO

CREATE NONCLUSTERED INDEX IX_USERMAILBOXPERMISSIONSSTAGE1 ON UserMailboxPermissionsStage1 (uqid, id, mailboxid, aid) INCLUDE (permissions)
go

IF EXISTS (SELECT name FROM sys.indexes WITH (Nolock) WHERE name = N'IX_UserMailboxPermissionsStage2')
    DROP INDEX IX_UserMailboxPermissionsStage2 ON dbo.UserMailboxPermissionsStage2 ;
GO

CREATE NONCLUSTERED INDEX IX_UserMailboxPermissionsStage2 ON UserMailboxPermissionsStage2 (uqid, id, mailboxid) include (permissions)
go

IF EXISTS (SELECT name FROM sys.indexes WITH (Nolock) WHERE name = N'IX_USERMAILBOXPERMISSIONSSTAGE4')
    DROP INDEX IX_USERMAILBOXPERMISSIONSSTAGE4 ON dbo.UserMailboxPermissionsStage4 ;
GO

CREATE CLUSTERED INDEX IX_USERMAILBOXPERMISSIONSSTAGE4 ON UserMailboxPermissionsStage4 (uqid, userid, mailboxid, id)
go

IF EXISTS (SELECT name FROM sys.indexes WITH (Nolock) WHERE name = N'IX_USERMAILBOXPERMISSIONSFINAL')
    DROP INDEX IX_USERMAILBOXPERMISSIONSFINAL ON dbo.UserMailboxPermissionsFINAL ;
GO

CREATE CLUSTERED INDEX IX_USERMAILBOXPERMISSIONSFINAL ON UserMailboxPermissionsFINAL (uqid, userid, mailboxid, id)
go

IF EXISTS (SELECT TOP 1 1 FROM sys.indexes WHERE name = 'IX_USERMAILBOXPERMISSIONS' AND [object_id] = object_id('dbo.UserMailboxPermissions'))
	DROP INDEX dbo.UserMailboxPermissions.IX_USERMAILBOXPERMISSIONS
go

CREATE NONCLUSTERED INDEX IX_USERMAILBOXPERMISSIONS ON dbo.UserMailboxPermissions (DepartmentID, MailboxID, Permissions)
go

IF EXISTS (SELECT TOP 1 1 FROM sys.indexes WHERE name = 'IX_USERSERVICELINEPERMISSIONS_1' AND [object_id] = object_id('dbo.UserServicelinePermissions'))
	DROP INDEX dbo.UserServiceLinePermissions.IX_USERSERVICELINEPERMISSIONS_1
go

CREATE NONCLUSTERED INDEX IX_USERSERVICELINEPERMISSIONS_1 ON dbo.UserServiceLinePermissions (UserID, DepartmentID, Permissions, ServiceLineID) --Include(Permissions)
go

IF EXISTS (SELECT TOP 1 1 FROM sys.indexes WHERE name = 'IX_USERS_1' AND [object_id] = object_id('Users'))
	DROP INDEX dbo.Users.IX_USERS_1
go

CREATE NONCLUSTERED INDEX IX_USERS_1 ON dbo.Users (Status, Active) INCLUDE (ID, DisplayName, LoginName)
go

CREATE NONCLUSTERED INDEX [IX_ServicePartitionByObjectId] ON [dbo].[ServicePartition] ([ObjectID],[ServiceType]) INCLUDE ([ServiceID])
go


IF EXISTS(SELECT 1 FROM sys.indexes WHERE name = 'IX_UserAllPermissions_Department')
BEGIN
	DROP INDEX UserAllPermissions.[IX_UserAllPermissions_Department]
END
go

CREATE NONCLUSTERED INDEX [IX_UserAllPermissions_Department]
ON [dbo].[UserAllPermissions] ([DepartmentID])
INCLUDE ([UserID])
WHERE [DepartmentID] > 0
GO

IF EXISTS(SELECT 1 FROM sys.indexes WHERE name = 'IX_UserMailboxPermissions_MailBoxId')
BEGIN
	DROP INDEX [UserMailboxPermissions].[IX_UserMailboxPermissions_MailBoxId] 
END
GO 
CREATE NONCLUSTERED INDEX [IX_UserMailboxPermissions_MailBoxId] 
ON [dbo].[UserMailboxPermissions] (	[MailboxID] ASC	)
GO 

IF EXISTS(SELECT 1 FROM sys.indexes WHERE name = 'IX_UserMailboxPermissions_DepartmentID')
BEGIN
	DROP INDEX [UserMailboxPermissions].[IX_UserMailboxPermissions_DepartmentID] 
END
GO 
CREATE NONCLUSTERED INDEX [IX_UserMailboxPermissions_DepartmentID] 
ON [dbo].[UserMailboxPermissions] (	[DepartmentID] ASC	)
INCLUDE ([UserID],[MailboxID],[Permissions])
GO 

IF EXISTS (SELECT TOP 1 1 FROM sysindexes WHERE NAME = 'IX_SCHEDULINGWORKINGHOURS_PROFILEID_STARTDATETIME_ENDDATETIME_INCLUDES')
BEGIN
	DROP INDEX [dbo].[SchedulingWorkingHours].IX_SCHEDULINGWORKINGHOURS_PROFILEID_STARTDATETIME_ENDDATETIME_INCLUDES
END
GO
CREATE INDEX [IX_SCHEDULINGWORKINGHOURS_PROFILEID_STARTDATETIME_ENDDATETIME_INCLUDES] ON [dbo].[SchedulingWorkingHours] ([ProfileId], [StartDateTime], [EndDateTime])  INCLUDE ([TotalMinutes])
GO


ALTER TABLE IDTable
ADD CONSTRAINT temp1 UNIQUE(Name);
GO
ALTER TABLE IDTable DROP CONSTRAINT PK_IDTABLE;
GO
EXEC QueryWithOnlineOrOffline 
	'ALTER TABLE IDTable 
	ADD CONSTRAINT PK_IDTABLE PRIMARY KEY CLUSTERED(Name) WITH (ONLINE=<@OnlineState>)'
GO
ALTER TABLE IDTable
DROP CONSTRAINT temp1;
GO


EXEC QueryWithOnlineOrOffline 
	'CREATE CLUSTERED INDEX [CX_MsgInfoExternals] ON [dbo].[MsgInfoExternals]
	(
		[Instance] ASC,
		[ObjId] ASC
	) WITH (ONLINE=<@OnlineState>)'
GO


CREATE CLUSTERED INDEX [CX_MailReadyToSend] ON [dbo].[MailReadyToSend]
(
	[mailboxid] ASC
)
GO


ALTER TABLE UserRoleOptions
ADD CONSTRAINT temp1 UNIQUE(DepartmentID, UserId);
GO
ALTER TABLE [dbo].[UserRoleOptions] DROP CONSTRAINT PK_USERROLEOPTIONS;
GO
EXEC QueryWithOnlineOrOffline 
	'ALTER TABLE [dbo].[UserRoleOptions] 
	ADD CONSTRAINT PK_USERROLEOPTIONS PRIMARY KEY CLUSTERED(DepartmentID, UserId) WITH (ONLINE=<@OnlineState>)'
GO
ALTER TABLE [dbo].[UserRoleOptions]
DROP CONSTRAINT temp1;
GO


ALTER TABLE UserServicelinePermissions
ADD CONSTRAINT temp1 UNIQUE(UserID, DepartmentID, ServiceLineID);
GO
ALTER TABLE UserServicelinePermissions DROP CONSTRAINT PK_USERSERVICELINEPERMISSIONS;
GO
EXEC QueryWithOnlineOrOffline 
	'ALTER TABLE UserServicelinePermissions
	ADD CONSTRAINT PK_USERSERVICELINEPERMISSIONS PRIMARY KEY CLUSTERED(UserID, DepartmentID, ServiceLineID) WITH (ONLINE=<@OnlineState>)'
GO
ALTER TABLE UserServicelinePermissions
DROP CONSTRAINT temp1;
GO


ALTER TABLE UserAllPermissions
ADD CONSTRAINT temp1 UNIQUE(UserID, DepartmentID);
GO
ALTER TABLE UserAllPermissions DROP CONSTRAINT PK_USERALLPERMISSIONS;
GO
EXEC QueryWithOnlineOrOffline 
	'ALTER TABLE UserAllPermissions
	ADD CONSTRAINT PK_USERALLPERMISSIONS PRIMARY KEY CLUSTERED(UserID, DepartmentID) WITH (ONLINE=<@OnlineState>)'
GO
ALTER TABLE UserAllPermissions
DROP CONSTRAINT temp1;
GO


ALTER TABLE MailsFailedInParse
ADD CONSTRAINT temp1 UNIQUE(FailedMsgID);
GO
ALTER TABLE MailsFailedInParse DROP CONSTRAINT PK_MAILFAILEDINPARSE;
GO
ALTER TABLE MailsFailedInParse
ADD CONSTRAINT PK_MAILFAILEDINPARSE PRIMARY KEY CLUSTERED(FailedMsgID);
GO
ALTER TABLE MailsFailedInParse
DROP CONSTRAINT temp1;
GO


ALTER TABLE UserMailboxPermissions
ADD CONSTRAINT temp1 UNIQUE(UserID, DepartmentID, MailboxID);
GO
ALTER TABLE UserMailboxPermissions DROP CONSTRAINT PK_USERMAILBOXPERMISSIONS;
GO
EXEC QueryWithOnlineOrOffline 
	'ALTER TABLE UserMailboxPermissions
	ADD CONSTRAINT PK_USERMAILBOXPERMISSIONS PRIMARY KEY CLUSTERED(UserID, DepartmentID, MailboxID) WITH (ONLINE=<@OnlineState>)'
GO
ALTER TABLE UserMailboxPermissions
DROP CONSTRAINT temp1;
GO


EXEC QueryWithOnlineOrOffline 
	'CREATE CLUSTERED INDEX [CX_WorkflowAction] ON [dbo].[WorkflowAction]
	(
		[workflowid] ASC,
		[filterid] ASC,
		[status] ASC
	) WITH (ONLINE=<@OnlineState>)'
GO


EXEC QueryWithOnlineOrOffline 
	'CREATE CLUSTERED INDEX [CX_SchedulingQueueCollection] ON [dbo].[SchedulingQueueCollection]
	(
		[queue_id] ASC,
		[ext] ASC
	) WITH (ONLINE=<@OnlineState>)'
GO


ALTER TABLE SchedulingShifts
ADD CONSTRAINT temp1 UNIQUE(id);
GO
ALTER TABLE SchedulingShifts DROP CONSTRAINT PK_SCHEDULINGSHIFTS;
GO
EXEC QueryWithOnlineOrOffline 
	'ALTER TABLE SchedulingShifts
	ADD CONSTRAINT PK_SCHEDULINGSHIFTS PRIMARY KEY CLUSTERED(id) WITH (ONLINE=<@OnlineState>)'
GO
ALTER TABLE SchedulingShifts
DROP CONSTRAINT temp1;
GO


EXEC QueryWithOnlineOrOffline 
	'CREATE CLUSTERED INDEX [CX_Services] ON [dbo].[Services]
	(
		[ServiceID] ASC,
		[ServiceType] ASC,
		[ServiceGroupID] ASC
	) WITH (ONLINE=<@OnlineState>)'
GO