Scalar-valued functions

A scalar-valued function is a function that returns a single value for each row where it’s called. Unlike aggregate functions, a scalar-valued function operates on individual input values and produces one result at a time.

In Database Explorer, you can perform operations on scalar-valued functions by using the shortcut menu. Right-click a function in Database Explorer to access options for tasks such as creating, managing, or debugging functions. You can also generate scripts for them.

Create a scalar-valued function

1. In Database Explorer, right-click a scalar-valued function or Scalar-valued Functions node and select New Scalar Function.

2. In a SQL document, fill in the CREATE FUNCTION statement template.

3. At the bottom of the SQL document, click Apply Changes.

Tip

To create a scalar-valued function, right-click the Functions > Scalar-valued Functions node and select New Scalar Function.

Edit a scalar-valued function

You can edit a scalar-valued function in one of these ways.

Edit using Modify via SQL

1. In Database Explorer, right-click the function you want to edit and select Modify via SQL.

2. In a SQL document, edit the function.

3. In the top menu, select File > Save or press Ctrl+S.

Edit using Open Editor

1. In Database Explorer, right-click the function you want to edit and select Open Editor.

2. In a SQL document, edit the function.

3. At the bottom of the SQL document, click Apply Changes.

Alternatively, in the top menu, select File > Save or press Ctrl+S.

Delete a scalar-valued function

1. In Database Explorer, right-click the scalar-valued function you want to delete and select Delete, or press Delete.

2. Click Yes to confirm the deletion.

Note

You cannot undo the drop function operation, as this action permanently deletes the scalar-valued function. To avoid data loss, back up the database before deleting the scalar-valued function.

Execute a scalar-valued function

1. In Database Explorer, right-click the scalar-valued function you want to execute and select Execute.

2. In the View output parameters dialog, enter the parameters.

3. Click Close.

Debug a scalar-valued function

To start debugging a function, in Database Explorer, select Step Into from the shortcut menu and follow the steps from How to debug a function.

Duplicate a scalar-valued function

1. In Database Explorer, right-click a required scalar-valued function and select Duplicate Object.

2. In the Duplicate Object dialog, configure the object settings:

In Destination connection, select the target server.
In Destination database, select the target database.
In Destination schema, select the target schema.
In New object name, specify the object name.
Select Drop destination object to drop the destination object if it already exists.
Optional: Select Script Changes, then select To New SQL Window to open the generated script in a new SQL document, or To Clipboard to copy it to the clipboard.

3. Click OK.

Generate a script for a scalar-valued function

1. In Database Explorer, right-click a required scalar-valued function and select Generate Script As, then select the statement type.

2. Select one of the following output options:

To New SQL Window – Opens the script in a new SQL document.
To File – Generates the script to a .sql file.
To Clipboard – Copies the script to the clipboard.

For more information, see Generate the DDL and DML statements for database objects.

Add a scalar-valued function to the database diagram

To add a scalar-valued function to the database diagram, in Database Explorer, right-click a required function and select Send To > Database Diagram.

View properties of a scalar-valued function

To view function properties, in Database Explorer, right-click the function or Scalar-valued Functions node and select Show Details.

Object Viewer opens, displaying detailed information about the function.

Refresh a scalar-valued function

To update the function to reflect the latest changes, in Database Explorer, right-click the function or Scalar-valued Functions node and select Refresh, or press F5.

Open the Properties window

To open the Properties window, in Database Explorer, right-click the scalar-valued function and select Properties, or press F4.

Filter scalar-valued functions

1. In Database Explorer, right-click the Scalar-valued Functions node and select Filter.

2. In the Filter Settings dialog, configure the filter parameters.

3. Click OK to apply the filter.

Note

When you open the Scalar-valued Functions node, the number of objects it contains appears in parentheses next to the node name. This number changes based on your filter settings.

The following table describes the shortcut menu options, which appear when you right-click a specific scalar-valued function in Database Explorer.

Name	Description
New Scalar Function	Opens a `CREATE FUNCTION` template in a new SQL document to create a scalar function.
Source Control > Source Control Manager	Opens Source Control Manager, where you can track and manage changes to database schema and static data. When Source Control Manager is already open, this option refreshes it.
Source Control > Commit	Opens Source Control Manager with all local changes (if any) selected and ready to commit. When Source Control Manager is already open, this option refreshes it and selects all local database changes.
Source Control > Get Latest	Opens Source Control Manager with all remote changes (if any) selected, ready to pull using Get Latest. When Source Control Manager is already open, this option refreshes it and selects all remote database changes.
Modify via SQL	Opens the existing function in a new SQL document containing an `ALTER FUNCTION` statement to update it.
Open Editor	Opens the selected function in a new SQL document containing a `CREATE FUNCTION` statement to create the function or change its code.
Execute	Executes the function.
Step Into	Starts debugging the function. Shortcut: F11
Duplicate Object	Opens the Duplicate Object dialog to duplicate the function.
Generate Script As	Generates a script of the selected object using the `CREATE`, `CREATE OR ALTER`, `DROP`, `DROP and CREATE`, `ALTER`, `SELECT`, and `EXECUTE` statements. The script can be saved to a new SQL document or to a file, or copied to the clipboard.
Send to > Database Diagram	Adds the selected function to a database diagram.
Show Details	Opens Object Viewer that displays object details.
Delete	Drops the selected function from the database. Shortcut: Delete
Refresh	Updates the object to reflect the latest changes. Shortcut: F5
Properties	Opens the Properties pane to view the object information. Shortcut: F4

The following table describes the shortcut menu options, which appear when you right-click the Scalar-valued Functions node in Database Explorer.

Name	Description
New Scalar-valued Function	Opens a new SQL document containing the `CREATE FUNCTION` statement.
Filter	Opens the Filter Settings dialog to control which scalar-valued functions should appear in Database Explorer.
Show Details	Opens Object Viewer that displays object details.
Refresh	Updates the object to reflect the latest changes. Shortcut: F5

Note

The Source Control shortcut menu is available only after you link the corresponding database to a source-control system.

Bulk operations

In Database Explorer, hold down Ctrl and select multiple scalar-valued functions. Then right-click the selection and select the required option.

The following table describes the shortcut menu options available for multiple scalar-valued functions.

Name	Description
Open Editor	Opens the Scalar-valued Function designer for each selected function.
Source Control > Source Control Manager	Opens Source Control Manager, where you can track and manage changes to database schema and static data. When Source Control Manager is already open, this option refreshes it.
Source Control > Commit	Opens Source Control Manager with all local changes (if any) selected and ready to commit. When Source Control Manager is already open, this option refreshes it and selects all local database changes.
Source Control > Get Latest	Opens Source Control Manager with all remote changes (if any) selected, ready to pull using Get Latest. When Source Control Manager is already open, this option refreshes it and selects all remote database changes.
Source Control > Link/Unlink Static Data	Opens the Source Control Link Static Data dialog that displays the tables whose data you want to link.
Generate Script As	Generates scripts of the selected objects using the `CREATE`, `CREATE OR ALTER`, `DROP`, `DROP and CREATE`, `ALTER`, `SELECT`, and `EXECUTE` statements. The scripts can be generated to a new SQL document or to a file, or copied to the clipboard.
Send to > Database Diagram	Adds the selected functions to a database diagram.
Delete	Drops the selected functions from the database. Shortcut: Delete

Bulk operations for multiple scalar-valued functions