When working with SQL Server, it’s important to understand the difference between Line Feed (\n) and Carriage Return (\r). These characters are commonly used to represent a new line in various programming languages, including SQL Server.

Line Feed, also known as LF, is represented by the character \n or the hexadecimal value 0x0a. Carriage Return, also known as CR, is represented by the character \r or the hexadecimal value 0x0D.

The reason we have two different options for a new line is because different operating systems have different ways of interpreting it. For example, Mac only understands \r as a new line character, while Unix and Linux understand \n as a new line character. Windows, on the other hand, requires both \r and \n together to interpret as a new line, which is represented as \r\n.

This difference in interpretation can cause issues when working with files created in one operating system and opened in another. The file may not display properly and can become messy.

In SQL Server, we can create a new line using a simple script. Let’s take a look at two examples:

Example 1: No new line feed char

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
PRINT('SELECT FirstLine AS FL SELECT SecondLine AS SL')

In this example, there are no new line characters inserted, so everything is displayed in a single line.

Example 2: With new line feed char

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
PRINT('SELECT FirstLine AS FL' + @NewLineChar + 'SELECT SecondLine AS SL')

In this example, a new line character is inserted using the @NewLineChar variable, and the lines are separated with a new line.

These examples demonstrate how to create a new line in SQL Server, which can be useful when printing something or generating scripts.

I hope these examples have helped clarify the difference between Line Feed and Carriage Return in SQL Server. If you have any feedback or questions, please let me know.