<!--- Bug 6646 Add indices on the FormResponse table to improve query execution --->
<cfquery datasource="#application.config.DSN#">
-- Create New Index IX_FormResponseField_FormResponseIDFormFieldID
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_FormResponseField_FormResponseIDFormFieldID' AND object_id = OBJECT_ID('FormResponseField'))
BEGIN
CREATE NONCLUSTERED INDEX [IX_FormResponseField_FormResponseIDFormFieldID] ON [FormResponseField]
(
	[FormResponseID] ASC,
	[FormFieldID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
END

-- Create New Index IX_FormResponse_UserID
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_FormResponse_UserID' AND object_id = OBJECT_ID('FormResponse'))
BEGIN
CREATE NONCLUSTERED INDEX [IX_FormResponse_UserID] ON [FormResponse]
(
    [UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
END

-- Create New Index IX_FormResponse_WorkflowSetIDInstance
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_FormResponse_WorkflowSetIDInstance' AND object_id = OBJECT_ID('FormResponse'))
BEGIN
CREATE NONCLUSTERED INDEX [IX_FormResponse_WorkflowSetIDInstance] ON [FormResponse]
(
    [WorkflowSetIDInstance] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
END

-- Change Primary Key on FormField to be a clustered index
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'PK_FormField' and  object_id = OBJECT_ID('FormField') and type_desc = 'CLUSTERED')
BEGIN
	-- Drop Foreign Key Contraints so we can delete PK
	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_FormTrigger_FormField' AND TABLE_NAME = 'FormTrigger')
	BEGIN
		ALTER TABLE [FormTrigger] DROP CONSTRAINT [FK_FormTrigger_FormField]
	END

	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_FormTrigger_FormField1' AND TABLE_NAME = 'FormTrigger')
	BEGIN
		ALTER TABLE [FormTrigger] DROP CONSTRAINT [FK_FormTrigger_FormField1]
	END

	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_FormFieldOption_FormField' AND TABLE_NAME = 'FormFieldOption')
	BEGIN
		ALTER TABLE [FormFieldOption] DROP CONSTRAINT [FK_FormFieldOption_FormField]
	END

	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_FormResponseField_FormField' AND TABLE_NAME = 'FormResponseField')
	BEGIN
		ALTER TABLE [FormResponseField] DROP CONSTRAINT [FK_FormResponseField_FormField]
	END

	-- Delete Primary Key
	IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'PK_FormField' and  object_id = OBJECT_ID('FormField'))
	BEGIN
		ALTER TABLE [FormField] DROP CONSTRAINT [PK_FormField]
	END
	
	-- Add new clustered primary key
	ALTER TABLE [FormField] ADD  CONSTRAINT [PK_FormField] PRIMARY KEY CLUSTERED 
	(
		[FormID] ASC,
		[FormFieldID] ASC
	) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
	

	-- Re-add constraints now PK has been added
	ALTER TABLE [FormTrigger]  WITH CHECK ADD  CONSTRAINT [FK_FormTrigger_FormField] FOREIGN KEY([FormID], [FormFieldID])
	REFERENCES [FormField] ([FormID], [FormFieldID])
	ALTER TABLE [FormTrigger] CHECK CONSTRAINT [FK_FormTrigger_FormField]

	ALTER TABLE [FormTrigger]  WITH CHECK ADD  CONSTRAINT [FK_FormTrigger_FormField1] FOREIGN KEY([FormID], [TriggerFieldID])
	REFERENCES [FormField] ([FormID], [FormFieldID])
	ALTER TABLE [FormTrigger] CHECK CONSTRAINT [FK_FormTrigger_FormField1]

	ALTER TABLE [FormFieldOption]  WITH CHECK ADD  CONSTRAINT [FK_FormFieldOption_FormField] FOREIGN KEY([FormID], [FormFieldID])
	REFERENCES [FormField] ([FormID], [FormFieldID])
	ALTER TABLE [FormFieldOption] CHECK CONSTRAINT [FK_FormFieldOption_FormField]

	ALTER TABLE [FormResponseField]  WITH CHECK ADD  CONSTRAINT [FK_FormResponseField_FormField] FOREIGN KEY([FormID], [FormFieldID])
	REFERENCES [FormField] ([FormID], [FormFieldID])
	ALTER TABLE [FormResponseField] CHECK CONSTRAINT [FK_FormResponseField_FormField]
END
</cfquery>