Is DB_NAME() a deterministic function?

Question

I have several databases that each have a table called, say, products.

One of these databases (the "master") contains products that should be visible from all databases.

I do this by having an allproducts view as follows:

CREATE VIEW allproducts AS
   SELECT col[...] FROM products
   UNION ALL SELECT col[...] FROM master.dbo.products;

This works well, except that the master database's allproducts view needs a definition that doesn't bring in its own products:

CREATE VIEW allproducts AS
   SELECT col[...] FROM products;

To simplify things a little, I'd like to have a common view definition, something like the following:

CREATE VIEW allproducts AS
   SELECT col[...] FROM products
   UNION ALL SELECT col[...] FROM master.dbo.products
     WHERE (DB_NAME() <> 'master');

This works, but I'm concerned about performance. So, is the DB_NAME() function deterministic, and if so, will SQL Server shortcut the entire right side of the UNION ALL based on the comparison?

If DB_NAME() is evaluated for each row of master.dbo.products, is there a cleaner way to do this, or must I maintain separate view definitions?

UPDATE: "All metadata functions are nondeterministic. This means these functions do not always return the same results every time they are called, even with the same set of input values." - http://msdn.microsoft.com/en-us/library/ms187812.aspx

I found another question where someone had a similar situation and found a reasonable workaround:

Is there any way to make this UDF deterministic?

So the main part of the question has been answered. Even though DB_NAME() is nondeterministic, the execution plans are the same for calling a deterministic UDF versus calling DB_NAME(), and performance indicates that the DB_NAME() result does short-cut the other branches of the query. Neither are quite as fast as a scalar comparison like "0=1" in the WHERE clause, so even deterministic functions don't fully optimize the plan. I can live with a UDF, so I'm going to go that direction for this situation.

It's not clear from your question if you really have a problem or not. First, you're "concerned" about performance, but do you have an observable performance problem or not? If so, the first thing to look at would be the execution plan of your queries. Second, you want a "cleaner" way to do this, so is deployment or code management a problem? If so, what part of this configuration are you not able to automate or script? You might also want to look at synonyms as a way of simplifying the code, but you haven't mentioned your SQL Server version so they may not be available to you. — Pondlife, Mar 08 '12 at 15:18
The answer should be subjective--either SQL Server is smart enough to short-cut as I hope it can, or not. I don't have an observable performance problem yet because I don't have this in production yet. The views are a bit more complicated than the examples presented, so I'd rather not spend the time doing this and *then* find out I've made an egregious error in logic. I'd usually be right there with you on automation, but that won't work here. Code management is the issue long-term, not deployment -- I'd rather not maintain two versions of each view. Synonyms won't combine the tables. — richardtallent, Mar 08 '12 at 15:29

score 0 · Answer 1 · answered Mar 08 '12 at 15:38

I would strongly recommend against relying on SQL Server short-circuiting, even in cases where you have validated that it works today, on your current build, with your current data, on your current hardware, etc. The only place where SQL Server guarantees short-circuiting, AFAIK, is inside the CASE expression. And even there the optimizer has more important rules to follow (for example short circuiting can break when use an aggregate).

Do you really think your major optimization problem here is going to be whether DB_NAME() is evaluated for every row? I am fairly certain that it won't, but if this is the straw that kills your performance, there are certainly more important problems to tackle.

score 0 · Accepted Answer · answered Mar 15 '12 at 20:31

"All metadata functions are nondeterministic. This means these functions do not always return the same results every time they are called, even with the same set of input values." - http://msdn.microsoft.com/en-us/library/ms187812.aspx

I ended up not switching on DB_NAME() and just maintaining two versions of each DDL. Annoying, but even a UDF returning a constant bit value wasn't as efficient as I hoped.

Is DB_NAME() a deterministic function?

2 Answers2