Creating Your Own System Functions

for RuBoard

You'll recall the discussion in Chapter 9 regarding creating our own system viewsviews that can be queried from any database and run within the context of that database. You can do something similar with system functions. You can create functions that reside in master, but that can be queried from any database without a database name prefix.

Here's how it works: SQL Server creates a number of system UDFs during installation (e.g., fn_varbintohexstr(), fn_chariswhitespace(), and so on). Some of these are owned by the system_function_schema, and some aren't. Those that are owned by system_function_schema can be accessed from any database using a one-part name. You can find out which ones these are by running the query in Listing 10-9:

Listing 10-9 SQL Server's system functions.

 USE master GO SELECT name FROM sysobjects WHERE uid=USER_ID('system_function_schema') AND   (OBJECTPROPERTY(id, 'IsScalarFunction')=1       OR OBJECTPROPERTY(id, 'IsTableFunction')=1       OR OBJECTPROPERTY(id, 'IsInlineFunction')=1) 

(Results)

 name -------------------------------------------------- fn_chariswhitespace fn_dblog fn_generateparameterpattern fn_getpersistedservernamecasevariation fn_helpcollations fn_listextendedproperty fn_removeparameterwithargument fn_replbitstringtoint fn_replcomposepublicationsnapshotfolder fn_replgenerateshorterfilenameprefix fn_replgetagentcommandlinefromjobid fn_replgetbinary8lodword fn_replinttobitstring fn_replmakestringliteral fn_replprepadbinary8 fn_replquotename fn_replrotr fn_repltrimleadingzerosinhexstr fn_repluniquename fn_serverid fn_servershareddrives fn_skipparameterargument fn_trace_geteventinfo fn_trace_getfilterinfo fn_trace_getinfo fn_trace_gettable fn_updateparameterwithargument fn_virtualfilestats fn_virtualservernodes 

Chapter 22 covers creating system functions as well as other types of system objects. For now, just understand that because they belong to system_function_schema, they can be called across databases using one-part names .

To create your own system UDF, follow these steps:

1. Enable updates to the system tables. You can do that with this code:

    sp_configure 'allow updates', 1 RECONFIGURE WITH OVERRIDE 

2. Create the function in master, being sure to owner-qualify it. The function name must begin with fn_ and must be entirely in lowercase.

3. Disable updates to the system tables (as a rule, you should leave the allow updates switch turned off, especially on production systems):

    sp_configure 'allow updates', 0 RECONFIGURE WITH OVERRIDE 

Here's an example of couple of very simple system UDFs (Listing 10-10):

Listing 10-10 Two user -defined system functions: fn_greatest() and fn_least().

 USE master GO exec sp_configure 'allow updates',1 GO reconfigure with override GO DROP FUNCTION system_function_schema.fn_greatest, system_function_schema.fn_least GO CREATE FUNCTION system_function_schema.fn_greatest(@x bigint, @y bigint) RETURNS bigint AS BEGIN   RETURN(CASE WHEN @x>@y THEN @x ELSE @y END) END GO CREATE FUNCTION system_function_schema.fn_least(@x bigint, @y bigint) RETURNS bigint AS BEGIN   RETURN(CASE WHEN @x<@y THEN @x ELSE @y END) END GO exec sp_configure 'allow updates',0 GO reconfigure with override GO use northwind GO SELECT fn_greatest(2156875324698752,2156875323698752), fn_least(989, 998) 

(Results)

 DBCC execution completed. If DBCC printed error messages, contact your system administrator. Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install. -------------------- -------------------- 2156875324698752     989 

Here we've created a couple of new system functions: fn_greatest() and fn_least(). These correspond to Oracle's GREATEST and LEAST functions. They return the larger or smaller of two 8-byte integers. You can create your own system UDFs by following the steps listed earlier. Note that table-valued system UDFs must be qualified with a double colon regardless of whether you created the function or it was created during the installation process.

for RuBoard