Why prefix sql function names?

Question

What is a scenario that exemplifies a good reason to use prefixes, such as fn_GetName, on function names in SQL Server? It would seem that it would be unnecessary since usually the context of its usage would make it clear that it's a function. I have not used any other language that has ever needed prefixes on functions, and I can't think of a good scenario that would show why SQL is any different.

My only thinking is that perhaps in older IDE's it was useful for grouping functions together when the database objects were all listed together, but modern IDE's already make it clear what is a function.

Answer 1

You are correct about older IDEs

As a DBA, trying to fix permissions using SQL Server Enterprise Manager (SQL Server 2000 and 7.0), it was complete bugger trying to view permissions. If you had ufn or usp or vw it became easier to group things together because of how the GUI presented the data.

Saying that, if you have SELECT * FROM Thing, what is Thing? A view or a table? It may work for you as a developer but it will crash when deployed because you don't grant permissions on the table itself: only views or procs. Surely a vwThing will keep your blood pressure down...?

If you use schemas, it becomes irrelevant. You can "namespace" your objects. For example, tables in "Data" and other objects per client in other schemas eg WebGUI

Edit:

Function. You have table valued and scalar functions. If you stick with the code "VerbNoun" concept, how do you know which is which without some other clue. (of course, this can't happen because object names are unique)

SELECT dbo.GetName() FROM MyTable
SELECT * FROM dbo.GetName()

If you use a plural to signify a table valued function, this is arguably worse

SELECT dbo.GetName() FROM MyTable
SELECT * FROM dbo.GetNames()

Whereas this is less ambiguous, albeit offensive to some folk ;-)

SELECT dbo.sfnGetName() FROM MyTable
SELECT * FROM dbo.tfnGetName()

With schemas. And no name ambiguity.

SELECT ScalarFN.GetName() FROM MyTable
SELECT * FROM TableFN.GetName()

Your "any other language" comment doesn't apply. SQL isn't structured like c#, Java, f#, Ada (OK, PL/SQL might be), VBA, whatever: there is no object or namespace hierarchy. No Object.DoStuff method stuff.

A prefix may be just the thing to keep you sane...

Answer 2

There's no need to prefix function names with fn_ any more than there's a need to prefix table names with t_ (a convention I have seen). This sort of systematic prefix tends to be used by people who are not yet comfortable with the language and who need the convention as an extra help to understanding the code.

Answer 3

Like all naming conventions, it hardly matters what the convention actually is. What really matter is to be consistent. So even if the convention is wrong, it is still important to stick to it for consistency. Yes, it may be argued that if the naming convention is wrong then it should be changed, but the effort implies a lot: rename all objects, change source code, all maintenance procedures, get the dev team committed to follow the new convention, have all the support and ops personnel follow the new rules etc etc. On a large org, the effort to change a well established naming convention is just simply overwhelming.

I don't know what your situation is, but you should consider carefully before proposing a naming convention change just for sake of 'pretty'. No matter how bad the existing naming convention in your org is, is far better to stick to it and keep the naming consistency than to ignore it and start your own.

Of course, more often than not, the naming convention is not only bad, is also not followed and names are inconsistent. In that case, sucks to be you...

Answer 4

What is a scenario that exemplifies a good reason to use prefixes

THere is none. People do all kind of stuff because they always did so, and quite a number of bad habits are explained with ancient knowledge that is wrong for many years.

Answer 5

I'm not a fan of prefixes, but perhaps one advantage could be that these fn_ prefixes might make it easier to identify that a particular function is user-defined, rather than in-built.