# 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.

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

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