CODEWITHSUNDEEP
sqlsql-server-2005naming-conventions
adopilot
adopilot

Reputation: 4500

Does naming function in MSSQL like fn_myFuction take additional performace

Someone here mentioned that We should avoid naming stored procedures in MS SQL server like sp_XXX
Because that taking addition time to SQL server while check does exist system sotred procedure named like that. Because all system stored procs are starting with sp_.

Now I wondering is that a case with Functions in MSSQL, Does naming functions like fn_ take additional time to SQL while looking for system functions ?

Upvotes: 2

Views: 302

Answers (2)

Chris Baxter
Chris Baxter

Reputation: 16353

For functions it does not matter, however it is recommended to NOT use a prefix of sp_ for stored procedures as it defines a system stored procedure and may cause an extra lookup in the MASTER database.

As sp_ prefix is reserved for system stored procedure and any stored procedure which has sp_ prefix will cause an extra lookup in MASTER database. There is another point to note that if a stored procedure uses same name, in user database as system stored procedure in master database, the stored procedure in user database will never get executed as SQL Server will always look first in master database and will execute that one rather one in user database.

http://furrukhbaig.wordpress.com/2007/08/22/stored-procedures-factssheet/

http://msdn.microsoft.com/en-us/library/dd172115.aspx

Upvotes: 3

Mutation Person
Mutation Person

Reputation: 30498

No, I don't think so.

Found the following thread:

http://bytes.com/topic/sql-server/answers/78872-udf-starting-fn_

No. To call a system-supplied [User Defined Function], you need to use ::, so that is what SQL Server looks for. All system-supplied UDFs are table functions, as scalar system functions are not UDFs at all.

Hope this helps

Upvotes: 6

Related Questions