Newbie questions on User-defined functions in SQL server 
Author Message
 Newbie questions on User-defined functions in SQL server
Hi,

Does SQL server support any arbitrary functions for user-defined functions?
Can index structure be added for user-defined functions? Which version is
the first one that supports user-defined function? I'd appreciate your
answer very much.



Tue, 09 Mar 2004 13:53:45 GMT
 Newbie questions on User-defined functions in SQL server

Seung-won,

Not sure I understand your questions  :-(

Quote:
> Does SQL server support any arbitrary functions for user-defined

functions?
The restrictions on the definition of user-defined functions is documented
in the section "CREATE FUNCTION" (tsqlref.chm::/ts_create_7r1l.htm) in the
SQL Server 2000 Books Online. Basically, SQL Server requires that (a) all
user-defined functions be deterministic, and (b) all user-defined functions
do not have "side effects" on the state of the database ... in other words,
a function just returns a "value" and cannot modify data in the database
(for example, via an INSERT, UPDATE or DELETE).

Quote:
> Can index structure be added for user-defined functions?

Functions return data, so indexes cannot be applied to user-defined
functions. It's a bit like asking can an index structure be added to a
SELECT.

Quote:
> Which version

User-defined functions were introduced in SQL Server 2000.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> Hi,

> Does SQL server support any arbitrary functions for user-defined
functions?
> Can index structure be added for user-defined functions? Which version is
> the first one that supports user-defined function? I'd appreciate your
> answer very much.



Tue, 09 Mar 2004 12:16:35 GMT
 Newbie questions on User-defined functions in SQL server
BPs answer below caught my attention... that is interesting.
Quote:
> Can index structure be added for user-defined functions?

Functions return data, so indexes cannot be applied to user-defined
functions. It's a bit like asking can an index structure be added to a
SELECT.

I would not have considered this, except for BPs last sentence... Perhaps
you could create a view which selects using the UDF, then create an index on
the view.

--
Wayne Snyder SQL Server MVP
IKON Education Services, C{*filter*}te, NC
(Please Respond to newsgroups)
I proudly support the PASS SQL Server user community and its upcoming user
event,
PASS 2001 North America.
For details, visit < http://www.***.com/ > www.sqlpass.org.)



Tue, 09 Mar 2004 19:23:40 GMT
 Newbie questions on User-defined functions in SQL server
Wayne,

Good suggestion! ... and here all this time I thought no one paid any
attention to my posts   ;-)

BPM


Quote:
> BPs answer below caught my attention... that is interesting.
> > Can index structure be added for user-defined functions?
> Functions return data, so indexes cannot be applied to user-defined
> functions. It's a bit like asking can an index structure be added to a
> SELECT.

> I would not have considered this, except for BPs last sentence... Perhaps
> you could create a view which selects using the UDF, then create an index
on
> the view.

> --
> Wayne Snyder SQL Server MVP
> IKON Education Services, C{*filter*}te, NC
> (Please Respond to newsgroups)
> I proudly support the PASS SQL Server user community and its upcoming user
> event,
> PASS 2001 North America.
> For details, visit < http://www.***.com/ > www.sqlpass.org.)



Wed, 10 Mar 2004 09:11:37 GMT
 Newbie questions on User-defined functions in SQL server
    Actually, you can just use the function in a computed column & index it
in SQL 2000. It should be deterministic & schema bound. BTW - Oracle 8i &
upwards also supports indexing functions itself directly using a CREATE
INDEX.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )



Fri, 12 Mar 2004 03:46:56 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. call a user defined function from another user-defined function

2. Question on user defined function (SQL Server 2000)

3. Question on user defined function (SQL Server 2000)

4. user-defined function client server question

5. SQL Anywhere conversion to SQL Server (user defined functions)

6. Newbie - Calling a user-defined function

7. newbie: user defined function params

8. Newbie! User-defined Functions

9. How to call User Defined Function in SQL Server through VB

10. Equivalent User Defined Function in SQL Server 7.0 or 6.5

11. SQL SERVER USER DEFINED FUNCTIONS

12. Creating a user defined function in SQL server 7.0


 
Powered by phpBB® Forum Software