Michael Daly - 496 - Jan 30, 2014

If I want to use a Guid as my primary key, SS will create a clustered index on it, which is a performance issue if I am generating Guids outside the DB. 

I don’t see any built in functionality for generating (serial) Guids via the database, e.g. db.Save(row) doesn’t work for Guids.

Is there any way SS will get the db to create a Guid for insertion, or, create a clustered index on a different column than the PK?

Tks

OrmLite doesn’t create any index on it? it just marks it as the Primary Key, e.g:

public class TableName
{
    public Guid Id { get; set; }
    public long Long { get; set; }
}

using (var db = OpenDbConnection())
{
    db.DropAndCreateTable<TableName>();

    db.GetLastSql().Print();
}

//Prints out:
CREATE TABLE “TableName” 
(
  “Id” UniqueIdentifier PRIMARY KEY, 
  “Long” BIGINT NOT NULL 
); 

Michael Daly:

Ok, SS doesn’t explicitly create an index, SQL server will however create an index for the PK and make it clustered.

You can create Indexes on different properties by marking it with [Index] attribute.

Michael Daly:

I can’t create a clustered index though, using the [Index] attribute?

The best option I see is for db.Save(row) to use the DB’s internal guid generator for sequential guids, and populate the object back with the generated guid.

Other options I see right now are:
1) get SS to create an AutoIncrement PK so performance is retained, but it’s not going to be the “real” PK
2) run some manual SQL after creating tables with SS

Neither are as nice as the “best option”, do you think it could be included in OrmLite? MySQL has the same behaviour as SQL Server - it uses the PK as a clustered index, probably the same for other DB’s.

I’ve added NonClustered and Clustered options on the IndexAttribute and including them on the Create Index statement here:
https://github.com/ServiceStack/ServiceStack.OrmLite/commit/245a8226ad1ea4a484e1ef0199efcf6d7e8fcebd

If you want something more, propose a feature request on:
http://servicestack.uservoice.com/forums/176786-feature-requests

Michael Daly:

Excellent, thank you.

Ok this has now been published to MyGet:
https://github.com/ServiceStack/ServiceStack/wiki/MyGet

Dan Barua:

You can copy and paste a sequential guid generator into your project. It’s <10 lines of code.

Michael Daly:

I assume that guid generator will be per machine? So, if I move a service onto a different machine, the guid generator will not be able to guarantee sequential guid’s after any existing guids generated on the first machine, it also won’t work if the same service is running on two or more servers.

Dan Barua:

That depends on your needs. It’s been a while since I’ve done it, but you can convince SQL server to give you a new SequentialGUID on insert - you would need to tweak the table creation scripts as it’s not currently a feature of SS.OrmLite. That’s the route you’ll have to take if you want guaranteed sequential GUIDs in your table, which would make you dependent on the database to generate the PKs for you. Otherwise if you can relax the ordering guarantee constraint and accept a ‘good enough’ solution and generate the GUIDs in your application layer. It’s still going to perform better than using pure pseudo-random GUIDs.

Fredrick Lackey:

Has this changed?  I have an app that I’d like to move to ServiceStack and OrmLite however SQL Server will cause the PK to be clustered by default.  I cannot alter the settings on the DB server and need a mechanism for code first OrmLite to instruct the PK to NOT be clustered so that I may set another column to BE clustered (in this scenario, a date column “AuditDateCreated”).

Michael Daly:

Don’t think it has, we are using http://www.codeproject.com/Articles/388157/GUIDs-as-fast-primary-keys-under-multiple-database