title: Clustered Index improvemens for particular queries
body: <p>I found a table that looks like this:</p>
  
  <pre><code> CREATE TABLE [dbo].Table1 (  
    id INT primary key IDENTITY (1, 1),  
    [idUser] INT  NOT NULL ,  
    [Amount] INT NOT NULL ,  
    [Attempts] INT NOT NULL ,  
    [date] [datetime] NOT NULL ,  
    [SUM_Amount] INT NOT NULL   
   ) ON [PRIMARY]  
  </code></pre>
  
  <p>This table is created and populated with aggregated data for a particular period by a job.</p>
  
  <p>Particularities:</p>
  
  <ul>
  <li>This table will hold up to a million rows </li>
  <li>idUser is unique</li>
  <li>sum_Amount is running total of previous rows of amount.</li>
  </ul>
  
  <p>This table will last as is, no update or delete or insert operation.
  Just this type of queries:</p>
  
  <pre><code> select top (@n) * from table1
   order by [SUM_Amount] desc, [Attempts] desc
  
   select top (@n) * from table1
   where [SUM_Amount] &gt;=@m order by [SUM_Amount] asc 
  </code></pre>
  
  <p>I think it will improve preformance to change to a clustered index like this:</p>
  
  <pre><code> CREATE TABLE [dbo].Table2 (  
    id INT IDENTITY (1, 1),  
    [idUser] INT  NOT NULL ,  
    [Amount] INT NOT NULL ,  
    [Attempts] INT NOT NULL ,  
    [date] [datetime] NOT NULL ,  
    [SUM_Amount] INT NOT NULL  
  
   CONSTRAINT [PK_Nueva]
     PRIMARY KEY CLUSTERED ([SUM_Amount] desc, [Attempts] desc, id asc)   
  
   ) ON [PRIMARY]  
  </code></pre>
  
  <p>I read that using a no unique clustered index will add a 4 bytes hidden identifier column (<a href="http://msdn.microsoft.com/en-us/library/ms190639(v=sql.90).aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/ms190639(v=sql.90).aspx</a>), so I decide to add Identity (id) to cluster index (not sure if it is the right approach)</p>
  
  <p>I want to ask (at the risk of sound ridiculous, but need to be sure):   </p>
  
  <ul>
  <li>How could it be improved?   </li>
  <li>Will I have an impact on disk size?   </li>
  <li>Should I rebuild the index once all data have been inserted?</li>
  </ul>
  
  <p><strong>EDIT:</strong></p>
  
  <p>About id, I think is there just as a bad habit. I'll kept it, not sure how previous job calculate running total (I've no access to it) </p>
  
  <p>There are a lot of tables like this, like hundreds for each day(don't ask me why). That is why DBA team ask me to not create a new index because of size issues. That is why I thinking of rearrange table structure via clustered index. Also changing data types which exceeds normal ranges.  </p>