In my database designs my default choice for all tables is for them to have a surrogate key, a TIMESTAMP togethe with Last Modified UserID. For the surrogate key I mostly use IDENTITY, but in one project, with offline datasets, I have used GUIDs.

This surrogate key is used as the Clustered Primary Key. I name those fields ID (identity), TS (time stamp) and US (user stamp) in all tables. The data access layer of my application uses this for some low-level plumbing in managing the data on behalf of the business layer. Besides those fields tables containing top-level business objects (customer, order, etc) will have a unique key (mostly a human-friendly code, either textual or numeric, with appropriate index and constraints).

I don't even think about primary/foreign key methods when adding new functionality to the business layer: the underlying tables will get the above mentioned fields. The users will never see these values and their unique keys could be updated without breaking any relations in the schema (I learned the hard way that the cost to build functionality (coding a cascading update over the foreign keys, having to maintain this code when foreign keys are added, performance if a very large set is updated) for the user being able to occasionally make changes to their customer codes etc, is far outweighed by the small overhead of having a fairly static separate index on this separate key-field.

I consider identifying and tracking (auditing) as part of the data-storage solution and any implementation of features I no longer have to worry about in my application developments are welcome to me (in the end I am an application architect/developer).

So, if the database-server provides these functions (ROWID provides just one of them, but rudimentary auditing using timestamps and last-modified-by-user-account as well) I would seriously considering updating my schema and data-access layer of the application to make use of them sooner rather than later.

Only very occasionally I create a table that deviates from this pattern, mostly when it doesn't contain business-data, but some other arbitrary data and only when those fields would actually hamper some other application-requirement (performance, space-requirments, etc). So it would be nice if these featured could be turned off when I wanted to, but would be there by default.