The autoincrement feature in SQLServer tables is one that is really very handy. I’ve always like Oracle databases but it’s sequences are really much more complicated to manage than the autoincrement columns in SQLServer. If you are doing a project that is only going to run on Oralce or SQLServer either approach is fine and you won’t have any trouble.
The project I’m working on however has a requirement to work just the same on Oralce or SQLServer and the possibility that other databases may be added in the future. From day one my first worry was about if we were going to use Autoincrement and Sequences or we had to find another way out. The way out in this case would be to use GUID (Globally Unique Identifiers). In out case we chose to use Autoincrement for SQLServer and Sequences in Oracle. Mostly this approach was chosen because the guys that hired me thought that it would be much easier to search database for a record with a primary key like 1 or 15604 then something like 3F2504E0-4F89-11D3-9A0C-0305E82C3301. And I agreed with them on this, it is really simpler to say to the guy next to you to check if the person with id 2350 exists in the database the to tell him a GUID.
Of course we had a price to pay for this decision. Our DAL (Data Access Layer) was much more complicated because we had to deal with the Autroincrement vs Sequences problem. Imagine that you have a simple table called person with two columns only: ID and Name. Look at the difference in the insert clauses for each database:
Oracle
INSERT INTO PERSON (ID, NAME) VALUES (SEQ_PERSON.NEXTVAL, 'GABRIEL');
SQLServer
INSERT INTO PERSON (NAME) VALUES ('GABRIEL');
Observe that with Oracle I have to specify the PK column and in SQLServer I don’t and even worst I have to call the sequence SQL_PERSON to get the next value for the insert. If I want to return the value of the ID of the record we just inserted I’d have to do this:
Oracle
INSERT INTO PERSON (ID, NAME) VALUES (SEQ_PERSON.NEXTVAL, 'GABRIEL') RETURNING ID INTO :ID;
SQLServer
INSERT INTO PERSON (NAME) VALUES ('GABRIEL');
SELECT CAST(SCOPE_IDENTITY() AS INT);
I think you get the point right? There are a lot of differences in both databases! If we had choosen to use GUID id’s our life in the DAL would be much simpler. The GUID would be generated in the DAL which would make the insert statements very similar if not equal most of the time. Returning the generated GUID would also be a piece of cake. It’s true that using numeric PK’s creates better indexes and would consume less space but I find that these improvements would’ve been superseded by the ease in development.
So to sum up, when developing a system that should be database independent I would recommend using the table’s primary key’s as GUID in order to avoid using Autoincrement and Sequences. Your DAL layer would be more uniform and you could use ANSI SQL more often.
Just in case you are wondering we did when we couldn’t use ANSI SQL we created a SQL translator that would convert all the commands create for SQLServer to Oracle and this translator is part of our DAL generator so when I create a DAO for SQLServer the generator will create its’ structure and also Oracle’s all at once.