05 - Functions (DDL)

Hi,

 

Here we'll cover below –

 

-          Table

-          Constraints

-          View

-          SP

-          Functions

-          Index

-          Triggers

 

 

User-Defined Functions

 

·          Were introduced in SQL Server 2000.

·          Types of UDFs

 

Scalar UDFs	Return one value, and cannot return table data types.
Inline UDFs	Return a table data type, and do not explicitly define the returned table, but rather use a single SELECT statement for defining the returned rows and columns
Multistatement UDFs	Allow you to generate a table result set, based on Transact-SQL operations within the UDF definition.   Multistatement UDFs allow assignment, flow-of-control, DECLARE and SELECT variable assignments, extended stored procedure executions, and local table variable DML statements

 

 

·          UDFs can be Deterministic or Non-Deterministic. A deterministic UDF always returns the same value given the same parameters. Non-deterministic UDFs (e.g. GetDate()) return a different value or values when executed with the same parameters across multiple executions.

 

·          UDFs can also use Schemabinding. Like schemabinding with views, UDF schemabinding prevents you from changing the underlying objects referenced by the UDF, thus preventing changes that could hurt your user-defined function.

 

·          Syntax – For all three types

 

CREATE FUNCTION <ownername.scalar_function_name> (<@param1>, <@paramN...>) RETURNS <function_data_type> WITH [ENCRYPTION | SCHEMABINDING] AS   BEGIN <function_body> <RETURN value> END GO   When SCHEMABINDING is chosen, underlying and referenced database objects cannot undergo certain changes.	CREATE FUNCTION taxRate (@State nvarchar(2)) RETURNS numeric (2,2) AS BEGIN DECLARE @TaxRate numeric (2,2) SELECT @TaxRate = case @State WHEN 'MN' Then .07 WHEN 'WI' Then .05 WHEN 'WA' Then 0 WHEN 'NY' Then .11 ELSE 0 END RETURN @TaxRate END
CREATE FUNCTION <owner.inline_function_name> (<@param1,<@paramN>) RETURNS TABLE WITH [ENCRYPTION | SCHEMABINDING] AS RETURN <select statement> GO	CREATE FUNCTION BooksByAuthor (@AuthorId int) RETURNS TABLE AS RETURN SELECT vchBookName FROM Books WHERE iAuthorId = @Authorid GO
CREATE FUNCTION <owner.multistatement_function_name> (<@param1>,<@paramN>) RETURNS <@table_variable_name> TABLE (<column_name_1> <data_type_for_column1>, <column_name_N> <data_type_for_columnN>)   WITH [ENCRYPTION | SCHEMABINDING] AS BEGIN <Transact-SQL batches> RETURN END GO	CREATE FUNCTION dbo.AddRowHeader (@BookId int) RETURNS @KeyValues TABLE (vchBookName varchar(500)) AS BEGIN INSERT @KeyValues SELECT vchBookName FROM Books WHERE iBookId = @BookId INSERT @KeyValues VALUES (' Books Header ------------') RETURN END GO

 

  

UDF vs SP -

 

 

Stored Procedures	User-Defined Functions
Accept parameters	Same
Can contain output parameters	No
Optional	-      Must always return a scalar value or a table. -      UDFs can return all data types, with the exception of the CURSOR, TIMESTAMP, TEXT, NTEXT, and IMAGE data types
No	-      Can be used within your queries (Select, Where & From)  and DML statements. -      Used as a substitute for a sub-query.
	A function call can also be joined with other tables.

 

 

Hope this helps.

 

Thanks & Regards,

Arun Manglick || Tech Lead