UniqueIdentifier Type

  • Hi

    I have a table with a column "id" and it's type is uniqueidentifier and this field is clustered Index,the fragmentation on this table is high and the counter reads and cpu is also high .

    I want to set the default newsequentialid() for this column.

    Can I do this change and is it Ok ? What about the old data in id column?Does The new data have conflict with the old?

  • It's the same data type. No idea if it'll even work. If you specify a default and the app explicitly inserts a value, the default is ignored.

    Better option would be to leave that column as-is, change the primary key to nonclustered and put the clustered index somewhere else.

    http://www.sqlservercentral.com/articles/Indexing/68439/

    http://www.sqlservercentral.com/articles/Indexing/68563/

    http://www.sqlservercentral.com/articles/Indexing/68636/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Why should it ignore the default?You mean again it inserts a random data not bigger one?

    My table just has 2 fields : id that is uniqueidentifier and a nvarchar() it is about 1G and has 525400 rows.

    how can I put the clustered index somewhere else?

    As I searched I read these links for bad performance with uniqueidentifier as a clustered Index:

    http://msdn.microsoft.com/en-us/library/ms189786.aspx

    http://sqlblog.com/blogs/denis_gobo/archive/2009/02/05/11743.aspx

  • mah_j (11/24/2013)


    Why should it ignore the default?You mean again it inserts a random data not bigger one?

    If an insert explicitly specifies a value for a column that has a default, then the explicitly specified value will be inserted not the default.

    If you're replacing a default of NEWID() with a default of NEWSEQUENTIALID(), your plan will probably work. If the value for the uniqueidentifier is set in the procedure/application that inserts, then adding a default will do nothing.

    My table just has 2 fields : id that is uniqueidentifier and a nvarchar() it is about 1G and has 525400 rows.

    how can I put the clustered index somewhere else?

    Well, you didn't say that in your initial post and I wouldn't have guessed it was a two column table

    As I searched I read these links for bad performance with uniqueidentifier as a clustered Index:

    http://msdn.microsoft.com/en-us/library/ms189786.aspx

    http://sqlblog.com/blogs/denis_gobo/archive/2009/02/05/11743.aspx%5B/quote%5D

    Yes, I agree, uniqueidentifiers are terrible choices for a clustered index.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • mah_j (11/24/2013)


    My table just has 2 fields : id that is uniqueidentifier and a nvarchar() it is about 1G and has 525400 rows.

    how can I put the clustered index somewhere else?

    Add an identity column and set it as the clustered index. You can leave the uniqueidentifier as the primary key, if you want, but make it nonclustered.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • GilaMonster (11/24/2013)


    If an insert explicitly specifies a value for a column that has a default, then the explicitly specified value will be inserted not the default.

    If you're replacing a default of NEWID() with a default of NEWSEQUENTIALID(), your plan will probably work. If the value for the uniqueidentifier is set in the procedure/application that inserts, then adding a default will do nothing.

    Now this field does not have a default of NEWID(),and the value for this column is generated automatically,not in the application,so as I understood from your reply, if I set the default to NEWSEQUENTIALID(),the value that is generated will be bigger and unique In this case.

  • Robert Davis (11/24/2013)


    Add an identity column and set it as the clustered index. You can leave the uniqueidentifier as the primary key, if you want, but make it nonclustered.

    thanks Yes this is another way to get rid of this problem,but it requires changing the the structure of the table,I would do this as a last solution.

  • mah_j (11/24/2013)


    Now this field does not have a default of NEWID(),and the value for this column is generated automatically,not in the application,so as I understood from your reply, if I set the default to NEWSEQUENTIALID(),the value that is generated will be bigger and unique In this case.

    No, I said exactly the opposite.

    If the column DID have a default of NewID and you just replaced that existing default with NewSequentialID, then this plan would probably work.

    Since the column does not have a default of NewID, the uniqueidentifier will be generated either in the app or in the insert statement. As such, the default will be ignored and the uniqueidentifier inserted will NOT be larger and you will still have all your performance problems

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply