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 –

1_SQL_Server_What_is_Scalar_Function

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_SQL_Server_What_is_Scalar_Function

   

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

3_SQL_Server_What_is_Scalar_Function

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

4_SQL_Server_What_is_Scalar_Function

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

   

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

3 Comments on “SQL Server – What is Scalar Function?”

  1. Nice post bro. I especially enjoyed your quote “If you have an interest and passion, experience might become a very small thing”… Great thought and encouraging lines. Thanks

Leave a Reply

Your email address will not be published.