19

I'm using MS SQL 2005 and when I create a function I need to put the schema name to call it:

select dbo.MyFunc

All my tables are also in "dbo" schema and I don't need the schema name to reference it, so I'd like to know if I'm missing some configuration that could do the same to functions.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
Erick Sasse
  • 2,779
  • 3
  • 24
  • 30
  • 1
    Possible duplicate of [Is there a way to use a function on a Microsoft SQL Server Query without using "dbo." before the function?](https://stackoverflow.com/questions/5207036/is-there-a-way-to-use-a-function-on-a-microsoft-sql-server-query-without-using) – Heinzi Jul 23 '18 at 14:38

3 Answers3

18

Short answer, no it isn't.

You should consider to prefix all your database objects with the schema owner to avoid having sql server to "look it up".

It makes your statements more readable and gives a slight increase in performance (although you'd probably won't notice it).

Regards, Lieven

Lieven Keersmaekers
  • 57,207
  • 13
  • 112
  • 146
9

Unlike all the other database objects (tables, views and stored procedures), user defined functions always need the schema name when they are referenced. It's a quirk of SQL Server.

Anthony Faull
  • 17,549
  • 5
  • 55
  • 73
9

*Scalar-valued functions must be invoked by using at least the two-part name of the function

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

+1 Parent

Borik

Borik
  • 438
  • 3
  • 9