What's the best practice for handling primary keys using an ORM over Oracle or SQL Server?
Oracle - Should I use a sequence and a trigger or let the ORM handle this? Or is there some other way ?
SQL Server - Should I use the identifier data type or somehow else ?
If you are using any kind of ORM, I would suggest you to let it handle your primary keys generation. In SQL Server and Oracle.
Sometimes, there is a natural, unique identifier for a table. For instance, each row in a User table can be uniquely identified by the UserName column. In that case, it may be best to use UserName as the primary key.
Also, consider tables used to form a many to many relationship. A UserGroupMembership table will contain UserId and GroupId columns, which should be the primary key, as the combination uniquely identifies the fact that a particular user is a member of a particular group.
With either database, I would use a client-generated Guid for the primary key (which would map to uniqueidentifier in SQL Server, or RAW(20) in Oracle). Despite the performance penalty on JOINs when using a Guid foreign key, I tend to work with disconnected clients and replicated databases, so being able to generate unique IDs on the client is a must. Guid IDs also have advantages when working with an ORM, as they simplify your life considerably.
It is a good idea to remember that databases tend to have a life independent from a front end application. Records can be inserted by batch processes, web services, data exchange with other databases, heck, even different applications sharing the same database.
Consequently it is useful if a database table is in charge of its own identify, or at least has that capability. For instance, in Oracle a BEFORE INSERT trigger can check whether a value has been provided for its primary key, and if not generate its own.
Both Oracle and SQL Server can generate GUIDs, so that is not a sufficient reason for delegating identity generation to the client.