title: NewSequentialId on UniqueIdentifier Clustered Index
body: <p>I am working on database standards for a new database my company is starting.  One of the things we are trying to define is Primary Key and Clustered Index rules in relation to UniqueIdentifiers.</p>
  
  <p>(NOTE: I do not want a discussion on the pros and cons of using a UniqueIdentifier as a primary key or clustered index.  There is a ton of info on the web about that.  This is <strong>not</strong> that discussion.)</p>
  
  <p>So here is the scenario that has me worried:</p>
  
  <p>Say I have a table with a UniqueIdentifier as the clustered index and primary key.  Lets call it ColA.  I set the default value for ColA to be NewSequentialId().</p>
  
  <p>Using that NewSequentialId() I insert three sequential rows:</p>
  
  <p>{72586AA4-D2C3-440D-A9FE-CC7988DDF065}<br>
  {72586AA4-D2C3-440D-A9FE-CC7988DDF066}<br>
  {72586AA4-D2C3-440D-A9FE-CC7988DDF067}</p>
  
  <p>Then I reboot my server.  The <a href="http://msdn.microsoft.com/en-us/library/ms189786.aspx" rel="nofollow">docs for NewSequentialId</a> say that "After restarting Windows, the GUID can start again from a lower range, but is still globally unique."</p>
  
  <p>So the next starting point can be lower than the previous range.</p>
  
  <p>So after the restart, I insert 3 more values:</p>
  
  <p>{35729A0C-F016-4645-ABA9-B098D2003E64}<br>
  {35729A0C-F016-4645-ABA9-B098D2003E65}<br>
  {35729A0C-F016-4645-ABA9-B098D2003E66}</p>
  
  <p>(I am not sure exactly how the guid is represented in the database, but lets assume since this one starts with 3 and the previous ones started with 7 that the 3 ones are "smaller" than the 7 ones.)</p>
  
  <p>When you do an insert that is in the middle of a clustered index, a remapping of the index has to happen.  (At least so my DBA has told me.)  And every time I reboot I run the risk of having my new UniqueIdentifier range be right in the middle of other previous ranges.</p>
  
  <p>So my question is: Since the next set of UniqueIdentifiers will be smaller than the last set, will every insert cause my clustered index to shuffle?  </p>
  
  <p>And if not, why?  Does SQL Server know that I am using NewSequentialId?  Does it some how compensate for that?</p>
  
  <p>If not, then how does it know what I will insert next?  Maybe the next million inserts will start with 3.  Or maybe they will start with 7.  How does it know?</p>
  
  <p>Or does it not know and just keeps everything in order.  If that is the case then one reboot could massively affect performance.  (Which makes me think I need my own custom NewSequentialId that is not affected by reboots.)  Is that correct?  Or is there some magic I am not aware of?</p>
  
  <p><strong>EDIT:</strong> GUID as a clustered index is strongly discouraged in my standard.  As I said above, there are many reasons that this is a bad idea.  I am trying to find out if this is another reason why.</p>