Index creation script to be included in an SP

I want to include the following index creation script in my stored proc (SP) . Is it appropriate if I add it at the beginning of the SP where I declare variables..etc...Please comment on this. This script is the following one.

CREATE NONCLUSTERED INDEX [IX_ActivitiesAdditionalFieldsValues_AdditionalFields] ON [dbo].[ActivitiesAdditionalFieldsValues]
(
	[activityIncId] ASC,
	[activitySqlId] ASC,
	[activityAdditionalFieldIncId] ASC,
	[activityAdditionalFieldSqlId] ASC
)
INCLUDE ( [cboRecordIncId],
	[cboRecordSqlId],
	[txtValue]) 
WHERE ([isDeleted]=0x00)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
GO

The structure of my SP is as follows. I am posting a sample code of my SP here.

CREATE PROCEDURE [dbo].[eusp_e5_AS01_Report_perf]( @SiteKey			NVARCHAR(15),
											  @SponsorCode 		VARCHAR(100),
											  @StudyDirectorCode	VARCHAR(100),
											  @Archived			VARCHAR(5),
											  @ArchivedStartDate DATE ,
											  @ArchivedEndDate DATE,
											  @Year	 NVARCHAR(MAX),
											  @StudiesStatuses    NVARCHAR(100)	
										       )


AS
BEGIN

SET NOCOUNT ON
											  
DECLARE @siteSqlId SMALLINT
DECLARE @siteIncId INT
DECLARE @studaystatus TABLE (StatusCode NVARCHAR(50)) 


<Code continues Here.>

END

I want to know whether I can add the index creation script after the 'SET NOCOUNT ON'

Sure, you can put a CREATE INDEX statement there. Be sure to put a check for it already existing first, or you'll get an error when it tries to create an index name that already exists.

But you need to drastically increase the FILLFACTOR; I can't imagine that you need anything close to as low as 70%, especially for a filtered index. Try 96% instead.

But why would you do that in a stored procedure? If the expectation is to improve performance for the rest of the code - then just create the index and let the code use the new index.

The only reason to put an index creation script into a stored procedure is if you are building the table as part of a larger process. If you were doing that then I would recommend a separate stored procedure that just creates the necessary indexes.

You are going to lose processing time creating the index that you probably will not recover from using that index in the following code. I see this all the time now - people creating indexes on temp tables they just created - hoping it will improve the performance of their code later on...except they end up reading all rows from the temp table anyways...the indexes are useless and just take time to create.

True enough on temporary tables (whether #tables or tables with "real" names used just temporarily).

A clustered index can often be most useful on a temp table, and often even costs nothing to build, but it should be defined on the table before the data is loaded to it.

Blimey ... you scrolled? That's very thorough :toast:

Didn't there use to be (and is there still?) some special circumstances around filtered indexes? The query had to have identically matching criteria, in order for SQL to spot that the index could be used, or something like that?

I know we've been careful to do that, based on "habit", but maybe that's a think of the past?and SQL optimiser has been "improved" :slight_smile:

Please help me on the 'index already exists' check script

IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE name = 'IX_ActivitiesAdditionalFieldsValues_AdditionalFields' AND
object_id = OBJECT_ID('dbo.ActivitiesAdditionalFieldsValues'))
CREATE NONCLUSTERED INDEX [IX_ActivitiesAdditionalFieldsValues_AdditionalFields] ON [dbo].[ActivitiesAdditionalFieldsValues]
(
[activityIncId] ASC,
[activitySqlId] ASC,
[activityAdditionalFieldIncId] ASC,
[activityAdditionalFieldSqlId] ASC
)
INCLUDE ( [cboRecordIncId],
[cboRecordSqlId],
[txtValue])
WHERE ([isDeleted]=0x00)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]