An Introduction to SQL Server Scalar UDFs

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