title: Types of tables in SQL DW and when to opt - what type
body: <p>I have a question related to the types of tables in SQL DW and how to create them.</p>
  
  <p>My understanding - there are 3 types
  1 . HEAP STORE  [ This is row store]
  2. CLUSTERED INDEX [ This is row store with indexed on one column ]
  3. COLUMN CLUSTERED INDEX [ COLUMN store with Indexed on one column]</p>
  
  <p>Heap store is ideal for Staging tables.
  Clustered Index are for dimension tables which are HUGE in volume [ > 10K to 20M+ ]
  Column Clustered Index is for HUGE transaction tables(>60M records) .</p>
  
  <p>Now- I have a transaction table with 25M records , table contains 255 columns.
  This table has columns like StartDateKey, EndDateKey, LoadDateKey, CustId, ProdId, SourceId and so on... Transaction table is joined with 40+ columns in SSAS Data model.</p>
  
  <p>In this case, what should be my type of the table ? 
  1. Clustered Index because its &lt; 60M
  2. Column Clustered Index because it has lot of joins happening ? </p>
  
  <p>is my basics on deriving the type of the table with # of records is correct ? or should I consider # of columns, # of joins happening, usage pattern, ? </p>
  
  <p>I expect my transaction table to me queried multiple times a day , 3 times getting loaded .. </p>
  
  <p>Thanks,
  Aravind</p>