Docstoc

An Introduction to SQL Server Scalar UDFs

Document Sample
An Introduction to SQL Server Scalar UDFs Powered By Docstoc
					An Introduction to SQL Server Scalar
UDFs
By : Navneeth Diwaker Naik
May 17, 2004


One of the best features of SQL Server 2000 is its user-defined functions (UDFs). UDFs
offer many of the benefits of both views and stored procedures, and more.

Unlike views, UDFs can take zero or more input parameters and return a scalar value, or
can even return a table. Unlike stored procedures, the output of an UDF can be used in a
SELECT statement, which is really beneficial. UDFs are compiled and optimized in the
same way as a stored procedure.

SQL SERVER 2000 supports three different types of UDFs. They are scalar, inline table-
valued, and multi-statement table-valued functions. In this article, I will be discussing
scalar user-defined functions in detail.

Scalar user-defined functions must be deterministic, e.g., they cannot use functions, such
as getdate(), whose return value differs with time to time. A scalar function must always
return the same value if the input is same.

Take a look at, or execute the following T-SQL:

CREATE FUNCTION fnGetDate()

RETURNS varchar(100)

AS

BEGIN

     DECLARE @test varchar(100)

     SELECT @test = cast(getdate()as varchar(100))

     RETURN @testst

END

Output:

Server: Msg 443, Level 16, State 1, Procedure fnGetDate, Line 6
Invalid use of 'getdate' within a function.

The GetDate() function is not deterministic so we cannot use this in the Scalar UDF. In
addition, ccalar functions cannot be used to INSERT/UPDATE/DELETE the data from a
table. To demonstrate this, you can view or run the following scripts to create a sample
database and tables, and populate sample data in the table.

CREATE DATABASE DemoFunction

USE DemoFunction

CREATE TABLE [FunctionTest]

(

     [FTID] [int] NOT NULL,

     [FT_FName] [varchar](50) NOT NULL,

     [FT_MName] [varchar](50) NULL,

     [FT_LName] [varchar](50) NOT NULL,

     [FT_EMail] [varchar](255) NULL,

        CONSTRAINT [PK_FT] PRIMARY KEY NONCLUSTERED

        (

             [FTID]

        )     ON [PRIMARY]



) ON [PRIMARY]

GO

INSERT INTO FunctionTest([FTID],[FT_FName],[FT_LName],[FT_EMail])

     SELECT 1,'Navneeth','Naik','navneeth_naik@yahoo.com' UNION

     SELECT 2,'Anil','Bahirat','anil_bahirat@yahoo.com' UNION

     SELECT 3,'Amol','Kulkarni','amol_kulkarni@yahoo.com'
          SELECT 4,'Satya','Murthy','murthy@yahoo.com'

GO

CREATE FUNCTION fnInsertTest

(

     @FName varchar(50),

     @LName varchar(50),

     @MName varchar(50),

     @EmailId varchar(255)

)

RETURNS int

AS

BEGIN

INSERT INTO FunctionTest([FTID],[FT_FName],[FT_LName],[FT_EMail])

SELECT 1,'Navneeth','Naik','navneeth_naik@yahoo.com' UNION

SELECT 2,'Anil','Bahirat','anil_bahirat@yahoo.com' UNION

SELECT 3,'Amol','Kulkarni','amol_kulkarni@yahoo.com'

SELECT 4,'Satya','Murthy','murthy@yahoo.com'

     RETURN 1

END

Output:

Server: Msg 443, Level 16, State 2, Procedure fnInsert, Line 11

Invalid use of 'INSERT' within a function.

As you can see, the INSERT statement cannot be used in a scalar UDF.
CREATE FUNCTION fnUpdateTest

(

     @FName varchar(50),

     @LName varchar(50),

     @EmailId varchar(255)

)

RETURNS int

AS

BEGIN

     UPDATE [dbo.FunctionTest]

     SET [FT_FName] = @FName, [FT_LName] = @LName

     WHERE [FT_EMail] = @EmailId

     RETURN 1

END

Output:

Server: Msg 443, Level 16, State 2, Procedure fnDMLTest, Line 10

Invalid use of 'UPDATE' within a function.

As you can see here, the UPDATE Statements cannot be used in a scalar UDF.

CREATE FUNCTION fnDeleteTest

(

          @EmailId varchar(255)

)

RETURNS int
AS

BEGIN

          DELETE FROM [dbo.FunctionTest]

          WHERE [FT_EMail] = @EmailId

          RETURN 1

END

Output:

Server: Msg 443, Level 16, State 2, Procedure fnDMLTest, Line 8

Invalid use of 'DELETE' within a function.

And last of all, the DELETE statement cannot be used in a scalar UDF, as you can see
here.

A scalar function's output can be used in the SELECT statements of T-SQL, something
that you cannot with a stored procedure. For example:

CREATE FUNCTION fngetEmail

(

     @FName varchar(50),

     @LName varchar(50)

)

RETURNS varchar(255)

AS

BEGIN

     DECLARE @EmailId varchar(255)

     SELECT @EmailId = [FT_EMail]

       FROM [FunctionTest]
     WHERE [FT_FName] = @FName AND [FT_LName] = @LName

     RETURN @EmailId

END

SELECT dbo.fngetEmail([FT_FName],[FT_LName]) as EMAIL FROM FunctionTest

Output:

Email

--------

navneeth_naik@yahoo.com

anil_bahirat@yahoo.com

amol_kulkarni@yahoo.com

murthy@yahoo.com

Note in the above SELECT statement that the function is directly used in the SELECT
statement.

Unlike stored procedures, scalar functions can also be created using the option SCHEMA
BINDING and ENCRYPTION, as shown below:

CREATE FUNCTION fnSchemaTest

(

           @EmailId varchar(255)

)

RETURNS varchar(100)

WITH SCHEMABINDING

AS

BEGIN

     DECLARE @FullName varchar(100)
    SELECT @FullName = [FT_FName] + ' ' + [FT_LName]

       FROM dbo.FunctionTest

    WHERE [FT_EMail] = @EmailID

    RETURN @FullName

END

The addition of columns is allowed, as shown below:

ALTER TABLE dbo.FunctionTest

ADD [Active] bit

The deletion of a newly added column is allowed, as shown below:

ALTER TABLE dbo.FunctionTest

DROP COLUMN [Active]

The deletion of column being referenced in the function with SCHEMA BINDING is not
allowed, as shown below:

ALTER TABLE dbo.FunctionTest

DROP COLUMN [FT_FName]

Output:

Server: Msg 5074, Level 16, State 3, Line 1

The object 'fnSchemaTest' is dependent on column 'FT_FName'.

Server: Msg 4922, Level 16, State 1, Line 1

ALTER TABLE DROP COLUMN FT_FName failed because one or more objects access
this column.

Since the columns FT_FName is being used in Function with SCEHEMA binding we
cannot DROP it.

CREATE FUNCTION fnEncrypTest

(
          @EmailId varchar(255)

)

RETURNS varchar(100)

WITH ENCRYPTION

AS

BEGIN

     DECLARE @FullName varchar(100)

     SELECT @FullName = [FT_FName] + ' ' + [FT_LName]

           FROM dbo.FunctionTest

           WHERE [FT_EMail] = @EmailID

     RETURN @FullName

END

Now that we have encrypted the function, there is no way we can decrypt it again. So be
careful while you encrypt a function. Review or execute the following script and note the
output:

SELECT

     sysobjects.name AS [Function Name],

     syscomments.text AS [Function Definition]

FROM

     sysobjects INNER JOIN

     syscomments ON sysobjects.id = syscomments.id

WHERE

     (sysobjects.xtype = 'FN')

Output:
Function Name Function Definition

--------------------------------------------------

fnSchemaTest        Create function fnSchemaTest ….

fnEncrypTest        ????????????????????????????…

fngetEmail          Create function fngetEmail (…

As you can see, you cannot view the definition of fnEncrypTest, since it is encrypted.

I personally feel that we should use scalar UDFs under the conditions where we need the
single output with 0 or more inputs. Views cannot perform this operation, since they
cannot accept input parameters. While stored procedure can in such situations, the main
disadvantage of a stored procedure is that the output of the stored procedure cannot be
used in SELECT statements, whereas the output of scalar user-defined functions can be
used in SELECT statements. Performance-wise, SPs and scalar UDFs are same because
both are compiled and optimized in the same way. An important point I would like to
highlight is that UDFs are not in SQL 92 standards, while they are included in SQL 99
standards.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:6
posted:5/20/2010
language:English
pages:9