0

I've read a lot of articles that mention that using a guid as a clustered indexed (or primary key) in SQL Server could cause performance issues.

However, due to some design decisions, I really do need a field with GUID in it for each line of one table.

If I define the primary key of that table as an autoincrement integer, and add the GUID as a normal column which is indexed, will I have performance issues similar to having the GUID as the primary key?

Edit: As a side note, I know that GUID as a primary key is a bad idea, I am asking if I will have performance issues if I set it as an indexed (non clustered) field

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Cruces
  • 3,029
  • 1
  • 26
  • 55
  • 3
    Possible duplicate of [What are the best practices for using a GUID as a primary key, specifically regarding performance?](http://stackoverflow.com/questions/11938044/what-are-the-best-practices-for-using-a-guid-as-a-primary-key-specifically-rega) – Ken White Aug 08 '16 at 12:46
  • I also read many articles like that. But then i tried to figure out myself by creating a t-sql loop and leaving overnight. To my surprise, the page load time was almost same as before except for the first req after build. To be precise there were 4.5 lakhs entries in the db – It's a trap Aug 08 '16 at 12:46
  • when you say as before, you mean that when you changed the primary key to autoincrement int, and left a field as indexed guid, you got the same results as when you had guid as a primary key? – Cruces Aug 08 '16 at 12:48
  • The GUID is a potential performance issue ***only and only when*** used as the **clustering index** on the table – marc_s Aug 08 '16 at 12:49
  • no. I never changed the table structure. Before means that table had around 10 entries. After means that table had 4.5 lakh entries. Both the times guid was pk as well as clustering key. – It's a trap Aug 08 '16 at 12:50
  • 4
    @RachitGupta: what's important is the question whether that columns is the **clustered index** on that table (whether or not it's the PK is irrelevant) – marc_s Aug 08 '16 at 12:51
  • If you use a guid as a primary key, your data file will be more "fragemented", because a guid is a random value. When you add a new row in your table, the row is added randomly in the file. In your clustered index, you could add other fields, and add your guid at last. – Rom Eh Aug 08 '16 at 12:52
  • Another SO question that might be relevant: http://stackoverflow.com/questions/33655010/sql-server-int-primary-key-and-guid – Tab Alleman Aug 08 '16 at 13:19
  • so if I use a guid field but I just have it as indexed, not a cluster index, I shouldn't have performance problems? – Cruces Aug 08 '16 at 13:42
  • @RomEh that isn't quite correct. You can have a guid as a primary key with no issue. The fragmentation is when the guid is your clustered index. – Sean Lange Aug 08 '16 at 13:47

1 Answers1

0

What type of index you want to use for GUID field? Clustered? Unique? If just unique, then GUID will not be differ from any other unique field. You can use it without doubt. In case of clustered index it will affect performance. You can use Sequential GUID. It can solve some performance issues. Generating of GUID on the client-side also can improve performance.

Vladimir Liubimov
  • 964
  • 1
  • 11
  • 16
  • The guid is not created by the sql server. Basically the guid is created by an android application, it is sent along with the other data to the sql server, and then requests back the id that the sql server assigned to it, I was thinking of making it indexed to speed up the sql server search to locate the id that it assigned to it – Cruces Aug 11 '16 at 07:49