title: SQL Server index design where PK would be same size as row data
body: <p>I am trying to confirm that my table needs a primary key, even though it would double the row size, or figure out what an appropriate indexing strategy would be.  We are using SQL Server 2008 R2.</p>
  
  <p>I have a <code>Testscores</code> table with just over 2 billion rows, and each row only contains 10 bytes of data of the following form: </p>
  
  <pre><code>(ItemID INT, ProjectID SMALLINT, DepartmentID SMALLINT, Score REAL).
  </code></pre>
  
  <p>No column is unique, but we have approximately 100 million ItemIDs, 500 ProjectIDs and 300 DepartmentIDs.</p>
  
  <p>I have a lookup table of <code>Projects</code> with ~500 rows in the following form </p>
  
  <pre><code>(ID SMALLINT, ProjectName varchar, State Char(2), year INT)
  </code></pre>
  
  <p>Originally this table was denormalized and approximately 600gb. My goal is to be able to query the projects table on either <code>ProjectName</code>, <code>State</code>, or year (sometimes one of those, sometimes two, sometimes all three). I would then join the <code>Testscores</code> table on <code>ProjectsID</code> to return all test scores from matching projects (somewhere between 5 million and 20 million results)</p>
  
  <p>After rebuilding the tables (stupid should have figured this out first), I come to learn that without a clustered index, every query will have to use a table scan even if I build a nonclustered index on <code>ProjectsID</code>.  </p>
  
  <p>My current row size is 10 bytes, and adding a <code>BigInt</code> (needed, already at 2 billion and adding more) would add 8 bytes to each row, essentially doubling my database. Building a nonclustered index on <code>ProjectsID</code> would essentially require 8 bytes for the uniqueifier (4 for the value, 4 because its the first varchar).</p>
  
  <p>Any ideas? Did I screw something up in my database design? I don't mind rebuilding it again, I just want to do it right.</p>
  
  <p>PS, I've haunted for about a decade, and this is the first question I've had that I couldn't answer through searches. You all rock!</p>
  
  <p>Edit: When I loaded the data into the table, it was presorted on <code>ProjectID ASC, ItemID ASC</code>, if that makes any difference.</p>