I have a table that is used heavily. This table has 370,370 records.

When I run the following select:

select [ImageSize] , the_num = count(*) from [dbo].[ProductImages] group by [ImageSize] order by [ImageSize] 

I get this result:

Basically there are only 5 different imageSize objects in that table.

This is the current table definition:

IF OBJECT_ID('[dbo].[ProductImages]') IS NOT NULL DROP TABLE [dbo].[ProductImages] GO CREATE TABLE [dbo].[ProductImages] ( [ProductImageID] INT IDENTITY(1,1) NOT NULL, [SeasonItemID] VARCHAR(5) NOT NULL, [Language] SMALLINT NOT NULL, [Tier1] VARCHAR(10) NOT NULL, [Tier2] VARCHAR(10) NOT NULL, [Gender] CHAR(1) NOT NULL, [SortOrder] SMALLINT NOT NULL, [ImageFormat] VARCHAR(20) NOT NULL, [ImageSize] VARCHAR(10) NOT NULL, [ImageWidth] SMALLINT NOT NULL, [ImageHeight] SMALLINT NOT NULL, [BodenDomainURL] NVARCHAR(40) NOT NULL, [BodenImageURL] NVARCHAR(210) NOT NULL, CONSTRAINT [PK_ProductImages] PRIMARY KEY NONCLUSTERED ([SeasonItemID] asc, [Language] asc, [Tier1] asc, [Tier2] asc, [Gender] asc, [SortOrder] asc, [ImageFormat] asc, [ImageSize] asc)) GO 

And here are the definitions of the current inexes

• 1 clustered index

• 4 non clustered indexes

• 1 filtered index

• and the primary key non clustered defined above

   CREATE CLUSTERED INDEX [CIX_ProductImages__ProductImageID] ON [dbo].[ProductImages] ([ProductImageID] asc) CREATE NONCLUSTERED INDEX [IDX_SeasonItemID_Tier1_Tier2_ImageSizeBodenImageURL_INC_BodenDomainURL] ON [dbo].[ProductImages] ([SeasonItemID] asc, [Tier1] asc, [Tier2] asc, [ImageSize] asc, [ImageFormat] asc, [BodenImageURL] asc) INCLUDE ([BodenDomainURL]) CREATE NONCLUSTERED INDEX [IX_ProductImages_ImageFormat_ImageSize_INCL] ON [dbo].[ProductImages] ([ImageFormat] asc, [ImageSize] asc) INCLUDE ([SeasonItemID], [Language], [Tier1], [Tier2], [BodenDomainURL], [BodenImageURL]) CREATE NONCLUSTERED INDEX [IX_ProductImages_Language_Tier1_Tier2_ImageFormat_INCL] ON [dbo].[ProductImages] ([Language] asc, [ImageSize] asc, [Tier1] asc, [Tier2] asc, [ImageFormat] asc) INCLUDE ([ProductImageID], [BodenImageURL]) 

This is a filtered index:

 CREATE NONCLUSTERED INDEX [IDX_ProdImages_GetProductListingPageDenormalisedData] ON [dbo].[ProductImages] ([SeasonItemID] asc, [Tier1] asc, [Tier2] asc, [ImageFormat] asc, [ImageWidth] asc, [ImageHeight] asc) INCLUDE ([BodenImageURL]) WHERE ([ImageWidth]=(195) AND [ImageHeight]=(252) AND [ImageFormat]='ProductMain') 

Can I expect significant gains by changing the 4 non clustered indexes above to 20 FILTERED INDEXES according to the imageSize?

Most queries use the ImageSize as one of the parameters. It is even in the clustered index.

I could modify the indexes and table structure.

This table is mostly used for reading. There is only 1 write a day, and that would not normally affect many records.