All posts by Basavaraj Biradar

CONCAT() STRING FUNCTION IN SQL Server 2012

Functions, Sql Server, Sql Server 2012, ,

This is one of the new string functions introduced in SQL Server 2012. This function provides an easy way for the developers to concatenate the string values. Let us first see the syntax of this method:

Syntax:

CONCAT( string1, string2 [,stringN])

Here the parameters: string1, string2, … ,stringN are the string values which will be concatenated. This method requires minimum two arguments and max 254 arguments.

Let us understand this new CONCAT function  with a simple example:

SELECT CONCAT('Basavaraj',' ','Biradar') Name

RESULT:
Name
—————–
Basavaraj Biradar

Some of the important features about this function are:
1) All arguments are implicitly converted to string types before concatenating them.

Let us see this with an example:

DECLARE @EmployeeId INT = 1
SELECT CONCAT(@EmployeeId,' ','Basavaraj') EmployeeIDAndName

RESULT:
EmployeeIDAndName
———————-
1 Basavaraj

In previous versions of SQL Server where if we are using ‘+’ to concatenate the values then we were explicitly need to convert them to string and then concatenate. To achieve the result as in the above SQL 2012  example we were needed to write a Sql Statement like below with CAST/CONVERT function:

DECLARE @EmployeeId INT = 1
SELECT CAST(@EmployeeId AS VARCHAR) + ' ' 
             + 'Basavaraj' EmployeeIDAndName

2) Null values are implicitly converted to an empty string.

Let us see this with an example:

DECLARE @FirstName VARCHAR(20) = 'Basavaraj', 
        @LastName VARCHAR(20)
SELECT CONCAT(@FirstName, ' ',@LastName) Name

RESULT:
Name
—————————————–
Basavaraj

In previous versions of SQL Server where if we are using ‘+’ to concatenate the values then we know that if one of the values is NULL then concatenated value will be NULL. To avoid this we were needed to use ISNULL function to check if the value is null then consider it as empty string. To achieve the result as in the above SQL 2012  example we were needed to write a Sql Statement like below with ISNULL function:

DECLARE @FirstName VARCHAR(20) = 'Basavaraj', 
        @LastName VARCHAR(20)
SELECT ISNULL(@FirstName,'') + ' ' 
       + ISNULL(@LastName,'') Name

QUIZ:
Whether the below query will return all the 8000 characters ‘B’ and 8000 characters ‘C’ in the result?

DECLARE @string1 AS VARCHAR(8000) = REPLICATE('B',8000),
 @string2 AS VARCHAR(8000) = REPLICATE('C',8000)
SELECT CONCAT(@string1,@string2)

Difference between Stored Procedure and User Defined Function in Sql Server

Differences, Sql Server, , , , , , , , ,

Below are the some of the major differences between User Defined Function and Stored Procedure in Sql Server.

To know more on the User-Defined functions with examples please visit the article: User-Defined function.
To know more on the Stored Procedure with examples please visit the article: Stored Procedure.

Sl. No. User Defined function Stored Procedure
1 Function must return a value. Stored procedure may or not return values.
2 Will allow only Select statement, it will not allow us to use DML statements. Can have select statements as well as DML statements such as insert, update, delete
etc
3 It will allow only input parameters, doesn’t support output parameters. It can have both input and output parameters.
4 It will not allow us to use try-catch blocks. For exception handling we can use try catch blocks.
5 Transactions are not allowed within functions. Can use transactions within Stored procefures.
6 We can use only table variables, it will not allow using temporary tables. Can use both table variables aswell as temporary table in it.
7 Stored procedures can’t be called from function. Stored Procedures can call functions.
8 Functions can be called from select statement. Procedures can’t be called from Select/Where/Having etc statements. Execute/Exec
statement can be used to call/execute stored procedure.
9 UDF can be used in join clause as a result set. Procedures can’t be used in Join clause

[ALSO READ] You may like to read below other popular articles on differences
Varchar Vs NVarchar
Varchar Vs Varchar(MAX)
Char Vs Varchar
Union Vs Union All
DateTime Vs DateTime2
SET QUOTED_IDENTIFIER ON Vs OFF
Primary Key Vs Unique Key
RAISERROR Vs THROW
Temporary Table Vs Table Variable
LEN() Vs DATALENGTH()
Sequence Vs Identity

SET Options with their setting values required while working with filtered index

Sql Server, ,

While dealing with filtered index following Six SET option’s value should be set to ON

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON

And the one SET option whose setting value should be off is:

SET NUMERIC_ROUNDABORT OFF

If these settings are not set properly then filtered index creation may fail, query execution may not use the index or DML operation on the table may fail. The DML operation may fail with a error message like below:

INSERT/Update failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Click on the error message link to see a detailed example demonstrating when we get this error and how to resolve it.

To know more on the difference between SET ANSI_NULLS ON and SET ANSI_NULLS OFF Setting please visit the article: SET ANSI_NULLS ON/OFF Setting in Sql Server

To know more on the difference between SET QUOTED_IDENTIFIER ON and SET QUOTED_IDENTIFIER OFF Setting please visit the article: Difference Between SET QUOTED_IDENTIFIER ON and OFF setting in SQL Server

Please correct me, if my understanding is wrong. Comments are always welcome.