title: SQL Server partition and index
body: <p>I have a requirement to design a table that is going to have around 80 million records. I created a partition for every month using persisted column (if its wrong suggest me the best way). Please find below scripts that I used to create tables and partition and the query that's going to be used often. Only Insertion and deletion will be done on this table.</p>
  
  <pre><code>-- Create the Partition Function 
     CREATE PARTITION FUNCTION PF_Invoice_item (int)
     AS RANGE LEFT FOR VALUES (1,2,3,4,5,6,7,8,9,10,11,12);
  
  -- Create the Partition Scheme
     CREATE PARTITION SCHEME PS_Invoice_item
     AS PARTITION PF_Invoice_item ALL TO ([Primary]);
  
  
  
     CREATE TABLE [Invoice]
     (
     [invoice_id] [bigint] NOT NULL,
     [Invoice_Number] [varchar](255) NULL,
     [Invoice_Date] [date] NULL,
     [Invoice_Total] [numeric](18, 2) NULL,
     [Outstanding_Balance] [decimal](18, 2) NULL,
     CONSTRAINT [PK_Invoice_id] PRIMARY KEY CLUSTERED([invoice_id] ASC)
     )
  
  
  
    CREATE TABLE [InvoiceItem](
    [invoice_item_id] [bigint] NOT NULL,
    [invoice_id] [bigint] NOT NULL,
    [invoice_Date] [date] NULL,
    [make] [varchar](255) NULL,
    [serial_number] [varchar](255) NULL,
    [asset_id] [varchar](100) NULL,
    [application] [varchar](255) NULL,
    [customer] [varchar](255) NULL,
    [ucid] [varchar](255) NULL,
    [dcn] [varchar](255) NULL,
    [dcn_name] [varchar](255) NULL,
    [device_serial_number] [varchar](255) NULL,
    [subscription_name] [varchar](255) NULL,
    [product_name] [varchar](255) NULL,
    [subscription_start_date] [date] NULL,
    [subscription_end_date] [date] NULL,
    [duration] [varchar](50) NULL,
    [promo_name] [varchar](255) NULL,
    [promo_end_date] [date] NULL,
    [discount] [decimal](18, 2) NULL,
    [tax] [decimal](18, 2) NULL,
    [line_item_total] [decimal](18, 2) NULL,
    [mth]  AS (datepart(month,[invoice_date])) PERSISTED NOT NULL,**
    [RELATED_PRODUCT_RATEPLAN_NAME] [varchar](250) NULL,
    [SUB_TOTAL] [decimal](18, 2) NULL,
    [BILLING_START_DATE] [date] NULL,`enter code here`
    [BILLING_END_DATE] [date] NULL,
    [SUBSCRIPTION_ID] [varchar](200) NULL,
    [DEVICE_TYPE] [varchar](200) NULL,
    [BASE_OR_PROMO] [varchar](200) NULL,
    CONSTRAINT [PK_InvoiceItem_ID] PRIMARY KEY CLUSTERED ([invoice_item_id] 
    ASC,[mth] ASC))
    ON PS_Invoice_item(mth);
    GO
  
  
  
   ALTER TABLE [InvoiceItem]  WITH CHECK ADD  CONSTRAINT [FK_Invoice_ID] 
   FOREIGN KEY([invoice_id])
   REFERENCES [Invoice] ([invoice_id])
  </code></pre>
  
  <p>GO</p>
  
  <p>I will be using below queries</p>
  
  <pre><code>select subscription_name,duration,start_date,end_date,promotion_name,
  promotion_end_date,sub_total,discount,tax,line_item_total from InvoiceItem 
  lt inner join Invoice on lt.invoice_id=invoice.invoice_id where 
  invoice.invoice_number='' and lt.customer='' and lt.ucid='' lt.make='' and 
  lt.SERIAL_NUMBER='' and lt.dcn='' and lt.application=''
  
  select customer,make,application from billing.AssetApplicationTotals 
  lineItem inner join billing.Invoice invoice on 
  lineItem.invoice_id=invoice.invoice_id where invoice.invoice_number='';
  
  SELECT  [invoice_Date],[make],[serial_number],[application],[customer],
  [ucid],[dcn],[dcn_name],[device_serial_number]
  ,[subscription_name],[product_name],[subscription_start_date],
  [subscription_end_date],[duration],[promo_name],[promo_end_date]
  FROM [InvoiceItem] where [application]=''
  
  SELECT  [invoice_Date],[make],[serial_number],[application],[customer],
  [ucid],[dcn],[dcn_name],[device_serial_number]
  ,[subscription_name],[product_name],[subscription_start_date],
  [subscription_end_date],[duration],[promo_name],[promo_end_date]
  FROM [InvoiceItem] where [customer]=''
  </code></pre>
  
  <p>What is the best way to create index? Shall I create separate non clustered index for each filter, or shall I have Composite index and shall I have covering index to avoid key lookup?</p>