SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UDF as Computed Column


UDF as Computed Column

Author
Message
Dinesh Priyankara
Dinesh Priyankara
Mr or Mrs. 500
Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)

Group: General Forum Members
Points: 557 Visits: 115
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dPriyankara/udfascomputedcolumn.asp
ckempste
ckempste
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5335 Visits: 1
great article...


Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Martin Catherall
Martin Catherall
Mr or Mrs. 500
Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)

Group: General Forum Members
Points: 542 Visits: 353
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 Smile

cheers

martin



cooper
cooper
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 1
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



well0549
well0549
SSC Eights!
SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)

Group: General Forum Members
Points: 871 Visits: 2
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)



Dinesh Priyankara
Dinesh Priyankara
Mr or Mrs. 500
Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)

Group: General Forum Members
Points: 557 Visits: 115

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
Dinesh Priyankara
Dinesh Priyankara
Mr or Mrs. 500
Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)

Group: General Forum Members
Points: 557 Visits: 115

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
Dinesh Priyankara
Dinesh Priyankara
Mr or Mrs. 500
Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)Mr or Mrs. 500 (557 reputation)

Group: General Forum Members
Points: 557 Visits: 115

Hi Martin,



I am also having a problem with this 32 cascading level. looking forward for a solution.


MCP MCSE MCSD MCDBA
Anup pandey
Anup pandey
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 1

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.


rockmoose
rockmoose
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1761 Visits: 183

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search