The application I am writing must run on either SQL Server or Oracle.
I want to know what the best approach is for generation of unique id's/primary key values in this case. One that is reliable and will scale.
Currently I am generating my own 30 length varchar uuid.
The problem I have with surrogate keys is that SQL Server does not migrate them very well and oracle requires different sql statement for sequences. I want to avoid writing db specific queries.
I have been restricted from using stored procs or minimizing their use for migration and installation purposes. I think that if I have a valid enough argument, I could use a store proc to handle this.
Any suggestions are appreciated.
Will 30 varchar length field as a primary key cause performance problems on large tables?