• Another caveat about using GUIDs as keys:

    Beware of using non-sequential GUIDs as a non-clustered PRIMARY KEY on large historical data. If you want to delete a block of data (say all events before '2006-01-01'), even if your clustered index is on your eventdate column, it will have to remove nodes from your non-clustered index for all rows affected by the DELETE statement. If your non-clustered index is on a "random" GUID, then this will hit a high percentage of pages on your index (with large data = very slow!!!). This occurs even if you batch your DELETE (with SET ROWCOUNT and a loop). If the GUID was sequential, then the nodes will be together and the DELETE from index operation would be much faster. If you use the COMB technique described elsewhere in this discussion (I think there was a link earlier) or a sequential key (int, bigint, sequentialguid etc), then this problem is somewhat alleviated.

    Or drop and recreate the index, but that could be expensive too!