Rashmi, I like using UUIDs as PKs in complex enterprise scenarios. For example, you have 5 enterprise systems scattered around the country and these systems are integrated using EAI. Two of these systems are able to create employee objects. How do you guarantee that each system generates unique primary keys for the employee objects that they create? Answer: UUID. UUIDs are very handy in this way.. and remember they are always unique across space and time.
UUIDs generated in the application allow faster row creation in that it is not necessary to hit the database twice in order to insert a record.
There are caveates however. What if you want to run a script to insert records into the database (such as in data migrations).. how do you create the UUID dynamically in your db script? Now you have to duplicate the logic because the application only knows how to create the UUID. There are ways to handle this, like in some DBs (ie Oracle), support java in the database so you could hook into your UUID generator class from your db script (see: http://otn.oracle.com/tech/java/htdocs/9idb2_java.html).
Frankly, I don't agree that PK generation must stay in the database. DBAs will probably argue with you on this (so be prepared to justify your position
I have implemented several enterprise packaged solutions that use UUIDs and I can't say I saw any database performance issues due to their use. Also, FYI these were VERY high transaction volume systems.
Patrick J. Nolan, Jr.