Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

UDF as Computed Column Expand / Collapse
Author
Message
Posted Friday, June 13, 2003 12:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 9:21 PM
Points: 175, Visits: 107
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dPriyankara/udfascomputedcolumn.asp
Post #13192
Posted Wednesday, June 18, 2003 2:39 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, 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"
Post #66506
Posted Wednesday, June 18, 2003 3:47 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, May 25, 2014 6:40 PM
Points: 384, Visits: 316
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




Post #66507
Posted Wednesday, June 18, 2003 5:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 3, 2003 12:00 AM
Points: 2, 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



Post #66508
Posted Thursday, June 19, 2003 6:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 31, 2008 12:56 PM
Points: 281, 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)




Post #66509
Posted Thursday, June 19, 2003 9:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 9:21 PM
Points: 175, Visits: 107

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
Post #66510
Posted Thursday, June 19, 2003 11:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 9:21 PM
Points: 175, Visits: 107

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
Post #66511
Posted Friday, June 20, 2003 12:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 9:21 PM
Points: 175, Visits: 107

Hi Martin,



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


MCP MCSE MCSD MCDBA
Post #66512
Posted Friday, June 18, 2004 7:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 8, 2007 10:11 AM
Points: 5, 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.

Post #121842
Posted Friday, June 18, 2004 3:16 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:45 AM
Points: 271, Visits: 147

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
Post #121946
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse