What is the difference between ROUTINE_NAME and SPECIFIC_NAME?

Question

In INFORMATION_SCHEMA.ROUTINES view, ROUTINE_NAME and SPECIFIC_NAME are present. As per MSDN they are the same. Are they there just for backward compatibility of some sorts? If not why is redundant fields being included in the view?

Answer 1

The ANSI SQL Standard does not prohibit overloading, i.e. two objects with the same name. The SPECIFIC_NAME is a unique name for an overloaded module, while the ROUTINE_NAME is not guaranteed to be unique. The same is true wherever you see SPECIFIC, such as SPECIFIC_CATALOG, and SPECIFIC_SCHEMA.

T-SQL does not have this capability, so SPECIFIC_NAME and ROUTINE_NAME are always the same.

The only SQL implementation that I know of that supports this feature is PostgreSQL (see here: http://www.postgresql.org/docs/current/interactive/infoschema-routines.html), but it's possible that I'm mistaken. My Oracle is rusty.

I'm not totally sure how SPECIFIC_CATALOG would work, although I suppose it's possible it could include some path or server information, to distinguish between two different databases with the same name.