Sql-server – Indexes in a big table. (About 400 mil of rows)

index-tuningsql-server-2012table

I have a little deal. I have this table in a SQL Server 2012 Standard edition database. The table contains about 400 mil of rows.

CREATE TABLE [dbo].[prodeje]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [partner] [tinyint] NOT NULL,
    [id_paragon] [varchar](20) NOT NULL,
    [typ_pohybu] [varchar](2) NULL,
    [store] [varchar](20) NULL,
    [datetime] [datetime] NOT NULL,
    [date] [date] NULL,
    [id_prod] [varchar](50) NOT NULL,
    [customer] [varchar](50) NULL,
    [price_per_unit] [decimal](18, 2) NULL,
    [stock_price] [decimal](18, 2) NULL,
    [pc] [decimal](18, 2) NULL,
    [amount] [decimal](16, 4) NULL,
    [action] [varchar](50) NULL,
    [action_cupon] [varchar](50) NULL,
    [bonus_gratis] [varchar](50) NULL,
    [fin_sleva] [decimal](18, 2) NULL,
    [exclude] [tinyint] NULL
) ON [PRIMARY]

It's a table with sales. So most of the query will include column partner, store, date, id_prod, price per unit * amount and distinct count on column id_paragon for baskets. Column id_prod is foreign key for table with products. Column exclude is column with information if the row is good. All query will contains in where clause exclude=0.

And I am thinking about indexes. Because it's more usually use part of the year, so my first think was that i will do clustered index on column date. And in query i query first only data what i need. So I will query on few mil of rows and it will be quick. And index for id_prod. But I think that is not good way to do the indexes. So I have questions for you, what indexes will you do on this table.

If you need more info write a comment. I will add what you want for think about it.

Thanks

Best Answer

Since you are optimizing for reads you could simply add one optimal index per query. So if your query is

select price_per_unit from Sales where exclude = 0 and partner = 42

Create the index (exclude, partner) include (price_per_unit).

You say that writes and disk spaces are not of significant concern. So add the optimal indexes to support your reads.