Improving performance of cluster index GUID primary key

by

A clustered index on GUID is not a good design. The very nature of GUID is that it’s random, while a clustered index physically orders the records by the key. The two things are completely at odds. For every insert SQL has to reorder the records on disk! Remove clustering from this index!

The time to use clustering is when you have a “natural” order to the data: time inserted, account number, etc. For time fields, clustering is almost free. For account number, it might be free or cheap (when account numbers are assigned sequentially).

While there may be technical ways around the GUID issue, the best idea is to understand when to use clustering.

More Related Contents:

  1. SQL server command help and tutorials for specific commands?
  2. How do I obtain a Query Execution Plan in SQL Server?
  3. Count(*) vs Count(1) – SQL Server
  4. select * vs select column
  5. Inner join vs Where
  6. Auto Increment after delete in MySQL
  7. INNER JOIN vs LEFT JOIN performance in SQL Server
  8. Equals(=) vs. LIKE
  9. Is a view faster than a simple query?
  10. Multiple INSERT statements vs. single INSERT with multiple VALUES
  11. Why would an IN condition be slower than “=” in sql?
  12. How can I define a composite primary key in SQL?
  13. Does the order of where clauses matter in SQL?
  14. Normalize array subscripts so they start with 1
  15. Does Foreign Key improve query performance?
  16. Performance of inner join compared to cross join
  17. Counting DISTINCT over multiple columns
  18. SCOPE_IDENTITY() for GUIDs?
  19. Favourite performance tuning tricks [closed]
  20. Is there AUTO INCREMENT in SQLite?
  21. SQL primary key: integer vs varchar
  22. Can an INNER JOIN offer better performance than EXISTS
  23. How to check which locks are held on a table
  24. Huge performance difference when using GROUP BY vs DISTINCT
  25. Do clustered indexes have to be unique?
  26. SQL Server 2008 paging methods?
  27. IN vs. JOIN with large rowsets
  28. What is the fastest way to truncate timestamps to 5 minutes in Postgres?
  29. sql swap primary key values
  30. Retrieve Oracle last inserted IDENTITY

Leave a Comment