SQL Server – What is Scalar Function?

Hello Friends,

It’s been a while that I have posted any blog.  So here it is-

Well with the arrival of SQL Server 2000, Microsoft has introduced the concept of User Defined Function (UDF).

So there are basically three types of UDF’s:

• Scalar Functions
• Inline Table-Valued Functions
• Multi-Statement Table-Valued Functions

In this blog-post, I would like to go on for Scalar Functions, and will take the other types in the next blog-post 🙂

I have made some keynotes about the SQL Server What is Scalar function, so please see it carefully:

• It returns only a single specific value.
• It can accept multiple parameters, perform calculation, and then return a single value.
• The value is passed back through the function by means of RETURN command, and every possible code path within the UDF should conclude with the RETURN command.
• It can used inside any expressions in SQL Server, in fact inside the check constraint, but are not advisable to use their cause of performance matrix.

Now let see how to create a Scalar function:

Here we are creating a function to add the two temporary variables;

USE TEST;
GO
 
CREATE FUNCTION dbo.fAddition(@a INT = 10, @b INT)
RETURNS INT
AS
BEGIN;
    RETURN @a + @b;
END;
GO

If you will see the parameters above, the first parameter is a default parameter and its default value being set to 10.

Let’s call a function now, and you can also see the result set –

Instead of doing the code manually, SQL Server Management Studio also gives you an option of using the template for creating a new Scalar Functions. It is being built to give the ease to the user. So follow the steps:

1. Right-click the Scalar-valued functions and click on the new scalar-valued functions

2. The query window will opens up with a template code and some description like this-

3. Now to fill this press the Ctl+Shift+M, so the dialog box will open up and fill the input variable carefully:

Well this is all about creating a Scalar functions.

At the same time, there are some drawbacks of using Scalar Functions:

• The Non-deterministic functions such as newid () and rand () are not allowed inside the scalar function. It means only deterministic functions are allowed inside which has the fixed return values.
• They are not allowed to return BLOB (Binary Large OBject) data such as text, ntext, timestamp, and image data-type variables.
• They are also not allowed to return table variables or cursor data types.
• They are not allowed to use Try..Catch block and Raiserror for error handling.

Well I think this is enough for this blog-post.

In the next blog-post I would like to deal with the Inline Table-Valued Functions.

So be tuned 🙂

And also comments on this!!

Regards

Piyush Bajaj

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook