title: For autoincrement fields: MAX(ID) vs TOP 1 ID ORDER BY ID DESC
body: <p>I want to find the highest AutoIncremented value from a field. (its not being fetched after an insert where I can use <code>@@SCOPE_IDENTITY</code> etc)
  Which of these two queries would run faster or gives better performance.
  <code>Id</code> is the primary key and <code>autoincrement</code> field for <code>Table1</code>. And this is for Sql Server 2005. </p>
  
  <pre><code>SELECT MAX(Id) FROM Table1
  
  SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC
  </code></pre>
  
  <p><strong>[Edit]</strong><br/>
  Yes in this case <code>Id</code> is the field on which I have defined the clustered index.<br/>
  If the index is <code>ID DESC</code> then what..<br/>
  And yes it would be nice to know how the performance would be affected if <br/>
  1. Id is a clustered index + primary key.<br/>
  2. Id is a clustered index and not primary key.<br/>
  3. Id is a non clustered index ASC + primary key.<br/>
  4. Id is a non clustered index ASC  and not primary key.<br/>
  5. Id is a non clustered index DESC + primary key.<br/>
  6. Id is a non clustered index DESC and not primary key.<br/>
  7. Id is just <code>AutoIncrement</code></p>
  
  <p>Hope its not a tall order!</p>