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