1

I had a table which has its primary key column of type nchar.

I was about to replace that type, I added a new uniqueidentifier column as id and set it as primary key and then I reset the relations to other tables.

I did this because I thought that a uniqueidentifier is a better choice than a string for id, because this string id was typed by users. It is kind of a name attribute used as primary key.

But it seems to be slowed down when I execute queries, and I don't understand why.

dvjanm
  • 2,351
  • 1
  • 28
  • 42
  • confirm whether you table's `cluster index` made on `uniqueidentifier column` .. and did you `droped` the `nchar` column – Moumit Dec 18 '13 at 10:01
  • When you changed the primary key, the index on the original PK may have been removed. If you are still using the original PK in your queries, then it would not be taking advantage of the index. Could you provide some more db object definitions and an example query? – laylarenee Dec 18 '13 at 10:49
  • I do not deleted the the old column, but I modified my queries too. I have index every column that is in any where clause, where I use uniqueidentifier key I don't use clustered index on it. But the performance issue somehow gone away, but I do not do any changes. – dvjanm Dec 18 '13 at 12:49
  • Duplicate?: [Unique identifier (guid) as primary key in database design](http://stackoverflow.com/q/9727090/456814). –  Aug 15 '14 at 21:25

1 Answers1

1

Uniqueidentifier are bad choice for primary key in general. This is a radom unordered string and PK are clustered indexes (by default) it means if you insert that id the engine ill try to insert that in the middle of that table and to do it the engine ill split the table taking a lot of processing.

Try to use a int identity for you PK, that original strign column can remain as a non clustered unique index if you need it for historical.

And read this (very small) article. SQL Server central

PS:If you are not already signed up for this forum, you can do it for free.

jean
  • 4,159
  • 4
  • 31
  • 52
  • But clustered index is not problem, I never will have a lot data in this table. There are some selects for aggregated reports and those have slowed down. This field is only in the where clause tested for equality. – dvjanm Dec 18 '13 at 10:23
  • It depends a lot of of you table size but there are other factors. Example if you are selecting while a insert is made that table split ill possible lock all table blocking even a simple select (it is a possible scenario and also depends on isolation level and a lot of other factors). Anyway create a SQL fiddle with some example data and you ill get more accurat answers. – jean Dec 18 '13 at 10:37