Meta-data

for RuBoard

Transact-SQL provides a handful of OBJECTPROPERTY() options for retrieving info about UDFs. They allow you to determine the specific type of UDF as well as whether a function is deterministic and/or schema bound. Table 10-1 summarizes these options:

Here's a query that demonstrates how these are used (Listing 10-6):

Listing 10-6 OBJECTPROPERTY() returns meta-data about UDFs.
 SELECT LEFT(name,20) AS [Function], OBJECTPROPERTY(id,'IsScalarFunction') AS Scalar, OBJECTPROPERTY(id,'IsTableFunction') AS [Table], OBJECTPROPERTY(id,'IsInlineFunction') AS Inline, OBJECTPROPERTY(id,'IsDeterministic') AS Determ, OBJECTPROPERTY(id,'IsSchemaBound') AS SchemaBound FROM sysobjects WHERE type in ('IF','TF','FN') ORDER BY name 

(Results)

 Function           Scalar      Table       Inline      Determ SchemaBound ------------------ ----------- ----------- ----------- ------ -------- OrgTable           0           1           0           0      0 PaulRodgersSingles 0           0           1           0      0 Proper             1           0           0           0      0 Sprintf            1           0           0           0      0 

Notice that none of these is deterministic. Recall the earlier discussion on determinism. What is it about the Proper() function, for example, that makes it nondeterministic? For a given input, it will always return the same result. The same is true of Sprintf(). Given a specific input, it will always return the same string. Why then aren't these two scalar functions deterministic?

The reason is simple: schema binding. SQL Server flags a function as nondeterministic in these situations:

  • The function is not schema-bound.

  • At least one function called by the function is nondeterministic.

  • The function references database objects outside its scope.

  • The function calls an extended stored procedure.

So, in the case of the Proper() function, it's flagged as nondeterministic because it was not created with schema binding. For its part, the Sprintf() function breaks two of the rules: It's not schema-bound, and it calls an extended procedure.

What exactly is schema binding? Schema binding links a function (or a view) to the objects it references in such a way that they cannot be changed in a manner that affects it structurally. Take, for example, an inline function with a return type that is entirely dependent on the data types and columns of the tables it references. What happens if one of those tables is changed? What happens if a column's data type is changed so that the SELECT statement that makes up the inline function causes an error when run? Your code breaks, that's what. The SCHEMABINDING option of the CREATE FUNCTION and CREATE VIEW statements was designed to address this inevitability . It prevents changes that affect the structure of other objects. In some respects, it's analogous to SQL Server's referential integrity constraint facility: It ensures the structural integrity of dependent objects.

Before you can specify CREATE FUNCTION's SCHEMABINDING option, these conditions must be met:

  • If the function references any view or UDFs, they must also be schema-bound.

  • The function must spell out any column lists it references. It may not use * in place of a column list.

  • If the function references outside objects, they must be in the same database and cannot be referenced with three- or four-part names .

  • The function may not define local variables .

  • If the function references outside objects, you must have REFERENCES permission on those objects.

See any problems? Proper() can't be deterministic because it's not schema-bound, and it can't be schema-bound because it defines local variables. Because we can't make Proper() a deterministic function, let's try the inline function we created earlier. Here it is again with the SCHEMABINDING option added (Listing 10-7):

Listing 10-7 A schema-bound inline function.
 CREATE FUNCTION PaulRodgersSingles(@title varchar(50)='%') RETURNS TABLE WITH SCHEMABINDING AS RETURN(SELECT Free=MIN(CASE band WHEN 0 THEN CAST(title AS char(18))                        ELSE NULL END),              BadCompany=MIN(CASE band WHEN 1 THEN CAST(title AS char(21))                             ELSE NULL END),              TheFirm=MIN(CASE band WHEN 2 THEN CAST(title AS char(23))                          ELSE NULL END),              Solo=MIN(CASE band WHEN 3 THEN title ELSE NULL END)       FROM dbo.singles       WHERE title LIKE @title       GROUP BY single) 

There are two differences between this version of the function and the previous one. First, of course, we added the SCHEMABINDING option to the function header. Second, we changed the reference to the singles table to use a two-part name. Previously, it used a three-part name. Even though both ver sions referred to the same table, three- and four-part names are not permitted in deterministic functions, as I mentioned earlier.

Now that the function is schema-bound, is it deterministic? Let's check:

 Function           Scalar      Table       Inline      Determ SchemaBound ------------------ ----------- ----------- ----------- ------ -------- OrgTable           0           1           0           0      0 PaulRodgersSingles 0           0           1           0      1 Proper             1           0           0           0      0 Sprintf            1           0           0           0      0 

It's still not deterministic. Why not? Because inline table-valued functions cannot be deterministic. By definition, what they return depends entirely on the data in the tables they referencethey cannot be assumed to be deterministic at any point in time. This is why OBJECTPROPERTY's IsDeterministic option applies only to scalar and table-valued functions. Just for kicks (or, just for pig iron, as an Irish friend of mine likes to say), let's create a deterministic function so that we can see what it looks like (Listing 10-8):

Listing 10-8 A function that's both deterministic and schema-bound.
 CREATE FUNCTION dbo.FortuneCookie(@Date datetime='20010101') RETURNS varchar(255) WITH SCHEMABINDING AS BEGIN   RETURN(CASE DATEPART(mm,@Date)    WHEN 1 THEN 'Where ever you go, there you are.'    WHEN 2 THEN 'Ask not what I can do for you, ask what you can do for me.'    WHEN 3 THEN 'Why do we drive on a parkway and park on a driveway?'    WHEN 4 THEN 'I see lots of cobwebs in my basement. What is a cob?'    WHEN 5 THEN 'To see in the dark, you must turn on the light.'    WHEN 6 THEN 'That's one giant leap for man, one small step on a landmine.'    WHEN 7 THEN 'In the words of the immoral Monica, "Close, but no cigar."'    WHEN 8 THEN 'If a tree falls in the woods, does anyone feel sad for the       grass it pulverizes?'    WHEN 9 THEN 'Four score and seven years ago, the Rangers actually had a       baseball team.'    WHEN 10 THEN 'The paperless office is about as likely as the paperless       bathroom. -- Joe Celko'    WHEN 11 THEN 'An ounce of technique is with worth a pound of technology.'    WHEN 12 THEN 'If you think education is expensive, try ignorance. -- Derek Bok'   END) END 

This function takes a date as a parameter, and, based on its month, returns a one-liner. It references no external objects and declares no internal variables. It consistently returns the same value for the same input. It was created with SCHEMABINDING, but is it deterministic? Let's check:

 Function           Scalar      Table       Inline      Determ SchemaBound ------------------ ----------- ----------- ----------- ------ -------- FortuneCookie      1           0           0           1      1 OrgTable           0           1           0           0      0 PaulRodgersSingles 0           0           1           0      1 Proper             1           0           0           0      0 Sprintf            1           0           0           0      0 

So now we've got a function that is both schema-bound and deterministic. If we wanted, FortuneCookie could serve as an index key or participate in an indexed view. This isn't true of the other functions.

for RuBoard


The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
ISBN: 201700468
EAN: N/A
Year: 2005
Pages: 223

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net