In SQL Server, concatenation refers to the process of combining two or more strings into a single string. This can be achieved using the '+' operator or the CONCAT function. Here's how to concatenate in SQL Server using both methods:

+ operator

To concatenate two strings using the '+' operator, simply place the operator between the two strings, like this:

SELECT 'SQL' + ' ' + 'tutorial';

This will result in the output: SQL tutorial.

Note that the '+' operator can only be used to concatenate two strings at a time. If you need to concatenate more than two strings, you'll need to use multiple '+' operators.

CONCAT function

To concatenate two or more strings using the CONCAT function, simply list the strings as arguments to the function, like this:

SELECT CONCAT('SQL', ' ', 'tutorial')

This will result in the same output as the previous example: SQL tutorial.

The CONCAT function can accept any number of arguments, and will concatenate them in the order they're listed. Note that if any of the arguments are NULL, the entire result will be NULL unless the CONCAT_NULL_YIELDS_NULL setting is turned off.

Examples

SELECT 'abc'+'123';

SELECT CONCAT('abc','123');

SELECT ID, NAME + ' - ' + JOB FROM EMPLOYEES;

DECLARE @var_string varchar(100);
SET @var_string = 'String A.';
SET @var_string += ' String B.';
PRINT @var_string;

In summary, concatenation in SQL Server can be accomplished using the '+' operator or the CONCAT function. Choose the method that best fits your needs and coding style.