Creating Models › Target Servers for Physical Models › Database Property Editors › SQL Server Property Editors › SQL Server Property Editors › Define SQL Server Functions

Define SQL Server Functions

Use the SQL Server Function Editor to define a function for a database or table in a SQL Server physical model.

To define a SQL Server function

1. Click Functions on the Model menu.

   The SQL Server Function Editor opens.

2. Select the function in the Navigation Grid that you want to define and work with the following options:

   Note: Click New on the toolbar to create a new function. Use the Enter filter text box to filter a very large list of functions to quickly locate the one that you want to define.

   Name

   Specifies the name of the function. Change the name of the function in this field.

   Schema

   Specifies the schema of the database to which the function belongs. Select the schema from the drop-down list.

   Type

   Specifies whether the function type is model-level or table-level. Select the type from the drop-down list.

   Enabled

   Specifies to enable the function in the database.

   Attach To New

   Specifies to attach the selected function to each new table that you create. Clear the check box if you want to manually attach the function to new tables.

   Generate

   Generates SQL during forward engineering. Clear the check box if you do not want to generate SQL.

   Generate As Pre-Script

   Specifies whether the function is generated as part of a pre-script. Select an option from the drop-down list.

3. Click the General tab and work with the following options:

   'Function_Name' Function Type

   Specifies the type of function and its associated values. Select one of the following function types from the drop-down list:

   Scalar Function

   Specifies the scalar function type, which returns a single data value. When you select Scalar Function, you can use the Null Input, Return Data Type, Encryption, and Schema Binding options that populate the grid to further define the function.

   Inline Table Function

   Specifies the inline table-valued function type, which returns a result set of single SELECT statement. When you select Inline Table Function, you can use the Null Input, Encryption, and Schema Binding options that populate the grid to further define the function.

   Multistatement Table Function

   Specifies the multi-statement table-valued function type, which returns a result set in table form, which was built from multiple statements. When you select Multistatement Table Function, you can use the Null Input, Encryption, Schema Binding, Return Table Definition, and Return Variable options that populate the grid to further define the function.

   Scalar Return Assembly Function

   Specifies the scalar return assembly function type, which returns a single value by executing a method from an assembly. When you select Scalar Return Assembly Function, you can use the Null Input, Return Data Type, Assembly, Class Name, and Method Name options that populate the grid to further define the function.

   Table Return Assembly Function

   Specifies the table return assembly function type, which returns a table of values by executing a method from an assembly. When you select Table Return Assembly Function, you can use the Null Input, Return Table Definition, Assembly, Class Name, and Method Name options that populate the grid to further define the function. Use the Return Order tab to define the order in which results should be returned.

   Natively Compiled Scalar Function

   Specifies the natively compiled scalar function type, which are scalar user-defined functions. Natively compiled scalar functions are available SQL Server 2016 onward. When you select Natively Compiled Scalar Function, you can use the Null Input, Return Data Type, Transaction Isolation Level, Language, Date First, Date Format, and Delayed Durability options that populate the grid to further define the function.

   Type

   Specifies the permission level in effect when the function is executed.

   Select User

   Specifies the user whose permissions are in effect for the execution of the function.

   Note: This option is only available if you have selected the Execute As User option in the Type drop-down list.

   'Function_Name' Parameter_Order_List

   Specifies the parameters for the function and lets you select the order in which the parameters execute and the properties of each one.

   Note: Click New on the toolbar to add a new parameter.

4. Work with the other tabs in the editor to access additional design features, for example:
   • Specify the order in which results should be returned from a Table Return Assembly Function, in the Return Order tab. This tab appears only for SQL Server 2008.
   • Define the code type in the Code tab.
   • View expanded code in the Expanded tab.
   • Define function permissions using the Permission tab.
   • Specify the order of DDL generation in the Object Creation Order tab.
   • View where the function is used in the Where Used tab
5. (Optional) Click the Comment tab and enter any comments that you want to associate with the object.
6. (Optional) Click the UDP tab to work with user-defined properties for the object.
7. (Optional) Click the Notes tab to view and edit user notes.
8. Click Close.

   The function is defined and the SQL Server Function Editor closes.