Day 20

Quiz

1:User-defined functions can return scalar values as well as tables and can be used as part of the SELECT statement or in the FROM clause of a query. True or False?
A1: True
2:What are the three different types of user-defined functions?
A2: The three types of UDFs are

  • Scalar—Returns a single value

  • Inline single table value—Returns a table of data from a single table

  • Multi-statement table value—Uses joins to return data in a table from multiple tables in the database>

3:Can I use a user-defined function instead of a view?
A3: Yes! In fact, by using a UDF instead of a view, performance will actually increase because the database does not have to maintain the view.

Exercise

1:Using the Northwind database, create a function that accepts a ZIP Code and returns a table of customer names and addresses.
A1: This exercise needs only an inline table function as shown here: 
CREATE FUNCTION Address_List (@zip nvarchar(10))
RETURNS Table AS
Return (Select CompanyName,
       ContactName,
       Address,
       City,
       Region,
       PostalCode,
       Country
From Customers
Where PostalCode = @zip)

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset