June 13, 2003 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dPriyankara/udfascomputedcolumn.asp
June 18, 2003 at 2:39 am
great article...
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
June 18, 2003 at 3:47 am
Excellent method of placing an index on a UDF.
However the 32 level limit of UDF's is annoying when we have a recursive relationship.
I have recently been looking at the nested set model and this seems to have a lot to offer if the recursive level goes beyond 32.
Takes a little bit of thought though 🙂
cheers
martin
June 18, 2003 at 5:24 am
Can you tell me why the first example isnt deterministic? I can see that the parent relationship can change over time but what rule is exactly violed that are mentioned in BOL? :
Whether a user-defined function is deterministic or nondeterministic depends on how the function is coded. User-defined functions are deterministic if:
The function is schema-bound.
All built-in or user-defined functions called by the user-defined function are deterministic.
The body of the function references no database objects outside the scope of the function. For example, a deterministic function cannot reference tables other than table variables that are local to the function.
The function does not call any extended stored procedures.
User-defined functions that do not meet these criteria are marked as nondeterministic. Built-in nondeterministic functions are not allowed in the body of user-defined functions.
Edited by - cooper on 06/18/2003 05:25:17 AM
June 19, 2003 at 6:01 am
I Really wonder when i could use this.
Would a indexed UDF really be faster than a join.....
( I tested it and it isn't )
Furthermore is a UDF the right way for the second example.
Although it is a deterministic function a query on the integer
500 would be faster than a query on the string '000500'
so i really wonder if it is really that usefull.
( I liked the Examples don''t get me wrong but i am not that fond
of a UDF because the profiler and the Query Execution Plan
hide the underlying queries. kind of a black box)
June 19, 2003 at 9:37 pm
Hi Cooper,
Ok, let me explain why the first example is not deterministic. As per BOL, given four criteria should be met in order to mark the function as deterministic. My first example violates the first criteria that is function is schema-bound.
Further, Why can't I create the function with SCHEMABINDING? Because I have not used two-part names and this function reference itself.
mcp mcse mcsd mcdba
June 19, 2003 at 11:36 pm
Hi 549,
You are correct. You can have a faster query by searching integer rather than string. I agreed. But What I wanted to show is the way.
And in my example, if '000500' is really need, then of course you have to use it. Or else you have to cut all leading zeros for searching and use replicate for querying. Can agree?
MCP MCSE MCSD MCDBA
June 20, 2003 at 12:26 am
Hi Martin,
I am also having a problem with this 32 cascading level. looking forward for a solution.
MCP MCSE MCSD MCDBA
June 18, 2004 at 7:52 am
I have a commment on the definition of the PK. Why do DB designers make artificial keys as PKs? You know you can just make it a constraint or a unique index and still be able to use it in a Parent-Child relationship. ProductName is the PK, ProductID is a surrogate key used in the FK-SK (PK) relationship. This allows some one to put multiple products with same name, and does not really tell what is the entity.
One does not have to define a PK in every table, dont define one rather than define a wrong key a PK just because it is a uniqueID not known to the business process.
June 18, 2004 at 3:16 pm
Ok Anup,
A table must have at least one Key that is meaningful to the business.
An "Artificial" key ( Identity, guid, rownumber ) is probably not a meaningful business key,
and if a table has such Artificial key it must also contain at least 1 UC ( Unique Constraint )
that IS meaningful to the business.
So if You have any DB designers at your place with "bad" habits ( using only meaningless keys ),
please ask them to review the relational model and learn some fundamental DB design concepts.
"Artificial" or "Natural" keys?
Well IMO both work, and there are situations where one works better than the other.
It depends on the database and the business area(s) it is supporting.
A table without a key is not a table at all, more like a heap where You can store a bunch of rows.
The article was OK, interesting. Describing one technique to accomplish a "comfy" select from (self)related tables.
You could do similar things with views ( and you don't have to add computed columns! )
/rockmoose
You must unlearn what You have learnt
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply