DbSchema Database Designer

DbSchema | SQL Server - How to Create User-Defined Functions (UDFs)?

Publish on DbSchema Blog >>>

SQL Server: How to Create User-Defined Functions in sqlcmd and DbSchema

SQL Server alt >

Table of Contents

  1. Introduction
  2. Prerequisites
  3. What is a User-Defined Function?
  4. Purpose of Using User-Defined Functions
  5. Restrictions and Permissions Required
  6. Advantages and Limitations
  7. Types of User-Defined Functions
  8. Creating User-Defined Functions
  9. Conclusion
  10. References


This comprehensive guide will delve into the process of creating user-defined functions in SQL Server using two methods: sqlcmd and DbSchema. We will cover what user-defined functions are, their purpose, the types of user-defined functions, and their advantages and limitations. By the end of this article, you’ll be able to create and utilize your own user-defined functions effectively.


Before proceeding, ensure you have:

  • SQL Server installed
  • Basic knowledge of SQL Server operations
  • sqlcmd utility and DbSchema tool installed

For installation and establishing connection you can read our article SQL Server-How to create a database?

What is a User-Defined Function?

A User-Defined Function (UDF) is a function provided by the user of a program or an environment, in a context where the usual assumption is that functions are built into the program or environment. In SQL Server, UDFs allow you to define your own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.

Purpose of Using User-Defined Functions

User-Defined Functions serve a variety of purposes:

  1. Code reuse and simplification: User-defined functions encapsulate frequently performed logic that can be reused in multiple SQL statements.
  2. Expression simplification: Complex expressions can be encapsulated within a function to simplify SQL queries.
  3. Additional abstraction: UDFs provide an additional level of abstraction, hiding details of complex calculations or operations.

Restrictions and Permissions Required

There are certain permissions and restrictions to keep in mind:

  • The user must have CREATE FUNCTION permission in the first place.
  • EXECUTE permission defaults to public. If the function specifies a user-defined type, EXECUTE permission defaults to the owner of the type.
  • ALTER FUNCTION permission is required to alter the function. The new code and the old must have the same owner.

Advantages and Limitations


  1. Encapsulation: Hide complex logic under simple function calls.
  2. Re-usability: Complex code can be written once and reused.
  3. Maintainability: Isolate changes to complex logic.


  1. Performance overhead: UDFs add a slight overhead compared to inlined SQL.
  2. Transactional limitations: You cannot use a UDF to modify database state.

Types of User-Defined Functions

There are three types of UDFs in SQL Server:

Type _Description_
Scalar Functions Return a single data value of the type defined in the return clause.
Table-Valued Functions Return a table data type.
System Functions Provided by SQL Server and return information about the SQL Server environment.

Creating User-Defined Functions

The general syntax for creating UDF is as follows:

CREATE FUNCTION [schema_name.]function_name 
( [ { @parameter_name [ type_schema_name. ] parameter_type 
    [ = default ] [READONLY] } 
    [ ,...n ]
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
        RETURN scalar_expression
[ ; ]

In sqlcmd

Here is an example of creating a simple scalar function in sqlcmd:

  1. Open command prompt and type sqlcmd.
  1. Log in to your SQL Server instance.
sqlcmd -S <server_name> -U <username> -P <password>

Replace <server_name>, <username>, and <password> with your specific server details and login credentials.

  1. Type the following command to create a new function:
(@Num1 INT, @Num2 INT)
    RETURN @Num1 + @Num2;

This code creates a scalar User-Defined Function (UDF) named AddNumbers in SQL Server.

The AddNumbers function is a SQL Server User-Defined Function (UDF) that accepts two integer parameters, adds them together, and returns the sum. It’s a reusable piece of SQL logic that can be invoked in multiple SQL statements.

  1. You can now use this function in your SQL queries as shown below:
SELECT dbo.AddNumbers(5, 10);

The above query will return 15.

This SQL statement is calling the user-defined function AddNumbers, passing in 5 and 10 as arguments, and it will return the sum of these two numbers, which is 15.

In DbSchema

Creating UDFs in DbSchema is a little bit different but still straightforward:

  1. Open DbSchema and connect to your SQL Server instance.
  2. Navigate to Schema > Create > Function.
  3. A new dialog will open where you can enter your function definition.
(@Num1 INT, @Num2 INT)
    RETURN @Num1 + @Num2;
  1. Click Save and your new function will be created.
  2. You can use this function in your SQL queries just as you would in sqlcmd.


In this article, we’ve introduced user-defined functions, their purpose, types, advantages, and limitations. We’ve also provided a comprehensive guide on creating UDFs in SQL Server via sqlcmd and DbSchema. With this knowledge, you can now write more reusable and maintainable SQL code.


  1. SQL Server User-Defined Functions
  3. DbSchema Documentation
  4. sqlcmd Utility

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.

Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.

Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.

Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.

SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.

Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.

Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.

Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.

DbSchema can be downloaded for free. No registration is required.