3

I have a series of constant functions to make programming and reading some of my SQL queries easier. For instance,

dbo.CONST_DogBreed(NVARCHAR(MAX)) will return an INT for a given dog breed. E.g., dbo.CONST_DogBreed('Labrador') may return 12.

The question I have is, if I call this function with a constant in a query, will it evaluate it repeatedly or will it realize that it's a constant and only evaluate it once? So, if I were to do:

SELECT * FROM Dogs
WHERE [DogType] = dbo.CONST_DogBreed('Labrador')

Is it going to evaluate dbo.CONST_DogBreed('Labrador') every single time? I realize the solution here would be to declare the value that I want in a variable above, but if it's unnecessary I'd prefer not to.

I.e. having to do this at the top of each of the queries:

DECLARE @LabradorBreed INT = dbo.CONST_DogBreed('Labrador');
SELECT * FROM Dogs
WHERE [DogBreed] = @LabradorBreed

After some research while writing this question, it seems that it's likely based on whether the function is Deterministic or not. If so, how do I make sure my function is deterministic? Will it do it for me? There are a couple of sql questions that specify Deterministic and a couple of others that use things like Schema Binding. Since my const functions don't use any schema, I'd think schema binding was unnecessary.

So I guess after this research it comes down to: How do I ensure that my function is deterministic?

Nate Diamond
  • 5,525
  • 2
  • 31
  • 57

1 Answers1

5

The Database Engine automatically analyzes the body of Transact-SQL functions and evaluates whether the function is deterministic. For example, if the function calls other functions that are non-deterministic, or if the function calls extended stored procedures, then the Database Engine marks the function as non-deterministic. For common language runtime (CLR) functions, the Database Engine relies on the author of the function to mark the function as deterministic or not using the SqlFunction custom attribute.

...

you need to add WITH SCHEMABINDING to the header of a user defined function in order to make it applicable for a "Deterministic" label.

https://technet.microsoft.com/en-us/library/ms187440(v=sql.105).aspx

Mikhail Lobanov
  • 2,976
  • 9
  • 24