title: Should MultiTenant Tables Include TenantID in Primary and Foreign Keys?
body: <p>For a multi-tenant single shared database should the tenantid field be included in the primary key and clustered index? Or is adding an additional index on tenantid just as performant?</p>
  
  <p>We’re running into performance issues on a production system whose only index is the clustered index on the primary key.</p>
  
  <p>ALL sql select statements start with tenantid in their linq to entities statements such as</p>
  
  <pre><code>invoiceitems.tenantid = thecurrenttenantid order by invoicedate
  </code></pre>
  
  <h2>CURRENT SCHEMA</h2>
  
  <p>Tenants (tenantid uniqueidentifier primary key, tenantname)
  Foreign Keys (tenantid)
  Indexes(Clustered on tenantid)</p>
  
  <p>Customers (tenantid uniqueidentifier, <strong>customerid uniqueidentifier primary key</strong>, customername varchar(50))
  Foreign Keys (tenantid, customerid)
  Indexes (<strong>clustered on customerid</strong>)</p>
  
  <p>Invoices (tenantid uniqueidentifier, <strong>invoiceid uniqueidentifier primary key</strong>, billcustomerid uniqueidentifier, shipcustomerid uniqueidentifier, invoicedate datetime)
  Foreign Keys (tenantid, billcustomerid, shipcustomerid)
  Indexes (<strong>clustered on invoiceid</strong>)</p>
  
  <p>InvoiceItems (tenantid uniqueidentifier, <strong>invoiceitemid uniqueidentifier primarykey</strong>, invoiceid uniqueidentifier, lineitemorder int)
  Foreign Keys (tenantid, invoiceid)
  Indexes (<strong>clustered on invoiceitemid</strong>)</p>
  
  <p>SqlAzure requires that each table has a clustered index so it's currently just on primarykeyid since that’s the default. Right now that is the only index on each table. There are various foreign keys in the tables throughout the system and none of the foreign key table fields are indexed.</p>
  
  <p>We're trying to resolve some performance issues right now and were wondering what would be the best clustered index and if any other indexes might be helpful. We're hoping we don't have to change the existing clustered index unless we absolutely have to but we are willing to do so. In SqlAzure AFAIK you cannot simply adjust the clustered index in an existing table - you have to create a new table with the desired clustered index and insert all records from the old table to the new table (and handle all of the foreign key constraints and other table dependencies).</p>
  
  <p><strong>ALL sql select statements start with tenantid</strong> in their linq to entities statements. </p>
  
  <pre><code>invoiceitems.tenantid = thecurrenttenantid order by invoicedate
  </code></pre>
  
  <p>Some sql select statements just have an order - some have other join condition values when bringing in child tables like</p>
  
  <pre><code>invoiceitems.tenantid = thecurrenttenantid and invoice.invoiceid = invoiceitems.invoiceid order by invoicedate
  </code></pre>
  
  <p>Here are a few ideas (we're open to others besides this) - <strong>which of these would be best and why</strong>?</p>
  
  <h2>PRIMARY KEY INDEX OPTIONS</h2>
  
  <p><em>To speed up access to the tenant's records</em></p>
  
  <p><strong>Option 1 - Add a non-clustered index on tenantid</strong></p>
  
  <p>Invoices (tenantid uniqueidentifier, invoiceid uniqueidentifier primary key, billcustomerid uniqueidentifier, shipcustomerid uniqueidentifier, invoicedate datetime)
  Foreign Keys (tenantid, billcustomerid, shipcustomerid)
  Indexes (clustered on invoiceid, <strong>non-clustered on tenantid</strong>)</p>
  
  <p><strong>Option 2 - Change the primary key from primaryid to tenantid + primaryid and change the clustered index to tenantid + primaryid.</strong></p>
  
  <p>Invoices (<strong>tenantid uniqueidentifier primary key, invoiceid uniqueidentifier primary key</strong>, billcustomerid uniqueidentifier, shipcustomerid uniqueidentifier, invoicedate datetime)
  Foreign Keys (tenantid, billcustomerid, shipcustomerid)
  Indexes (<strong>clustered on tenantid + invoiceid</strong>)</p>
  
  <h2>FOREIGN KEY INDEX OPTIONS</h2>
  
  <p><em>To speed up joins</em></p>
  
  <p><strong>Option 3 - Add non-clustered indexes on all foreign key fields only on foreignkeyid.</strong></p>
  
  <p>Invoices (tenantid uniqueidentifier, invoiceid uniqueidentifier primary key, billcustomerid uniqueidentifier, shipcustomerid uniqueidentifier, invoicedate datetime)
  Foreign Keys (tenantid, billcustomerid, shipcustomerid)
  Indexes (clustered on invoiceid, <strong>non-clustered on billcustomerid, non-clustered on shipcustomerid</strong>)</p>
  
  <p><strong>Option 4 - Change all foreign keys from foreignkeyid to tenantid + foreignkeyid and add an index on tenantid + foreignkeyid</strong></p>
  
  <p>Invoices (tenantid uniqueidentifier, invoiceid uniqueidentifier primary key, billcustomerid uniqueidentifier, shipcustomerid uniqueidentifier, invoicedate datetime)
  Foreign Keys (tenantid, <strong>tenantid + billcustomerid, tenantid + shipcustomerid</strong>)
  Indexes (clustered on invoiceid, <strong>non-clustered on tenantid + billcustomerid, non-clustered on tenantid + shipcustomerid</strong>)</p>
  
  <h2>SQL SELECT OPTIMIZATION INDEX OPTIONS</h2>
  
  <p><em>To speed up often used queries like select fields from invoices where tenantid = value order by invoicedate</em></p>
  
  <p><strong>Option 5 - add indexes on most often used sort order fields within each table besides tenantid.</strong> </p>
  
  <p>Invoices (tenantid uniqueidentifier, invoiceid uniqueidentifier primary key, billcustomerid uniqueidentifier, shipcustomerid uniqueidentifier, invoicedate datetime)
  Foreign Keys (tenantid, billcustomerid, shipcustomerid)
  Indexes (clustered on invoiceid, <strong>non-clustered on invoicedate</strong>)</p>
  
  <p><strong>Option 6 - add indexes on tenantid + “most often used sort order field” within each table and add non-clustered index on tenantid + “most often used sort order field”</strong></p>
  
  <p>Invoices (tenantid uniqueidentifier, invoiceid uniqueidentifier primary key, billcustomerid uniqueidentifier, shipcustomerid uniqueidentifier, invoicedate datetime)
  Foreign Keys (tenantid, billcustomerid, shipcustomerid)
  Indexes (clustered on invoiceid, <strong>non-clustered on tenantid + invoicedate</strong>)</p>