title: Is adding an INT to a table who's PRIMARY KEY is a UNIQUEIDENTIFIER for the purpose of a JOIN table worth while?
body: <p>I've got two tables in my SQL Server 2008 database, Users and Items</p>
  
  <pre><code>tblUser 
  --------------------------
  UserID    uniqueidentifier
  Name      nvarchar(50)
  etc..
  
  
  tblItem 
  --------------------------
  ItemID    uniqueidentifier
  ItemName      nvarchar(50)
  etc..
  
  tlmUserUserItem
  ----------------------------
  ItemID      uniqueidentifier
  UserID_A    uniqueidentifier
  UserID_B    uniqueidentifier
  </code></pre>
  
  <p>I want to join these together in a many to many join table that will get huge (potentially more than a billion rows as the application logic requires stats over shared user --> item joins)</p>
  
  <p>The join table needs to be indexed on the UserID_A and UserID_B columns since the lookups are based on a user against their peers.  </p>
  
  <p>My question is this:</p>
  
  <p>Is it worth adding an auto increment INT on the user table to use as a non primary key then use that in the join table?  So the User table looks like:</p>
  
  <pre><code>tblUser 
  ---------------------------------
  UserID         uniqueidentifier
  Name           nvarchar(50)
  UserIDJoinKey  int  identity(1,1)
  etc..
  </code></pre>
  
  <p>Doing that, will it be faster to do something like:</p>
  
  <pre><code>declare @ID int
  select * from tblJoin where UserIDJoinKey_A = @ID or UserIDJoinKey_B = @ID
  </code></pre>
  
  <p>when the join table looks like this:</p>
  
  <pre><code>tlmUserUserItem
  -----------------------------------
  ItemID             uniqueidentifier
  UserIDJoinKey_A    int
  UserIDJoinKey_B    int
  </code></pre>
  
  <p>rather than this:</p>
  
  <pre><code>tlmUserUserItem
  ----------------------------
  ItemID      uniqueidentifier
  UserID_A    uniqueidentifier
  UserID_B    uniqueidentifier
  </code></pre>
  
  <p>Thanks in advance.</p>