Going back thou here is an older article I wrote on the whole "sp_" thing
What is the search order for Procedures prefixed sp_?[/url]
But anyway, I have have to say without regard to what you choose as a Naming Convention it should be documented and understood as to the convention or eventually the whole thing will fall into chaos. If you choose to prefix or postfix then make sure everyone follows the same rules.
Now as for the comment about naming things so they can be readily identified, the idea that code (or in this case a database) should be self documenting is a poor practice. Documentation should always exist and be maintaned to ensure one persons meaning is not lost on another. But there are simple ways to look at every system to determine exactly what an object is without a prefix/postfix of any kind.
And then the last arguement about changes to the server by new reserved keywords and other types of changes. Well I choose to adopt a style of the
ISO/IEC 11179 Information technology — Metadata
Part 5: Naming and identification principles
this naming convention style works very well especially becaue I have yet to see many reserved keywords which are plural in format. But even if you prefix/postfix you table name you can still conflict with reserved keywords elsewhere. It is very common for people to use Name instead of breaking into component pieces such as Given_Name/First_Name and Surname/Last_Name.
But again, even thou you will see many differences of opinion on this and many people will have potentially valid reasons for thier choice, it is your choice. Set a naming convention and stick with it across the board, document throughly the method and the system, and above all else let noone stray unless they can provide a ligitimate reason other than they feel it is the better way. I gave my reasons for the name in managing things, I don't prefix any objects (but I used to) and I feel my designs have been far more flexible and easier to understand becuase of it. And when I find an issue in the design I can alter many things without breaking the app side until I either have a chance to or a specific need to, usually can wait until the next release cycle thou.