December 17, 2008 at 11:29 pm
SQLserver flags UDF that executes some selects as non-deterministic. Some other servers let developer control this flag, so I'd like to have soft-deterministic functions - functions that for the durations of the query return the same result for a set of input parameters. Such functions are not 100% deterministic, so would not be usable for indexes, but the engine can cache function results based on set of input parameters. This would speed up certain types of queries tremendously.
ASA has this feature for about a decade, I use it a lot and love it.
December 18, 2008 at 7:48 am
You should enter this as a Connect[/url] issue and then post a link to it here.
MS does respond to Connect issues. Probably not the way you would like, but they do check them and respond.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 18, 2008 at 4:22 pm
I tried at connect, but failed.
December 18, 2008 at 5:21 pm
Sorry, but what the @%$#%@ does that mean?
Did you enter your request in Connect, and the SQL Server team turned it down?
You couldn't enter anything at Connect?
You can't connect to Connect?
What did you try, and how did if 'not work'.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 18, 2008 at 6:09 pm
Hmm...I'm not sure I understand the problem.
You CAN make certain T-SQL UDF functions show up as deterministic, assuming that they actually ARE deterministic and you make them schemabinding. You just can't directly control the deterministic flag itself.
And - you can set the deterministic flag on CLR functions.
What kind of function is it that you cannot set to deterministic?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 19, 2008 at 1:30 am
Matt Miller (12/18/2008)
Hmm...I'm not sure I understand the problem.You CAN make certain T-SQL UDF functions show up as deterministic, assuming that they actually ARE deterministic and you make them schemabinding. You just can't directly control the deterministic flag itself.
And - you can set the deterministic flag on CLR functions.
What kind of function is it that you cannot set to deterministic?
An UDF that fetches data from some tables is by definition nondeterministic. However, for the duration of the query data in these tables does not change, so I'd call this soft-deterministic, as to the query it appears deterministic, but is not truly deterministic to be usable in an index.
Example:
Select A,B,C,D from ATable where func(A,B)=1
if func() is deterministic, it's result can be cached, so if ATable has million records, but only 10k of different sets of (A,B), the function is executed 10k times instead of million times.
In practice I have other criteria that limit resultset before even executing func(), so response time is in most cases under 1 sec, but if func() was deterministic, response time would be 10msec.
That's the idea.
December 19, 2008 at 1:51 am
Jeffrey Williams (12/18/2008)
Sorry, but what the @%$#%@ does that mean?What did you try, and how did if 'not work'.
Does it really matter?
Yesterday I went to connect, logged in, browsed, but could not post anything. It said something like "you need certain rights to post, to request them go to ... directory", tried that and failed to find anything relevant there.
I failed, either because I'm blind or it's something wrong with the site, so I came here and posted this poll to see potential interest.
Now, when I go to connect, I'm redirected to login.live.com/... and get this error:
Windows Live ID is unavailable from this site for one of the following reasons:
* This site may be experiencing a problem.
* The site may not be a member of Windows Live ID.
You can:
* You can sign in or sign up at other Windows Live ID sites and services, or try again later at this site.
:hehe:definitely something wrong with the site.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply