With every new release of SQL Server, there are often changes to default behaviors that can surprise users who have become accustomed to the previous versions. These changes can sometimes lead to confusion and frustration when trying to understand why certain behaviors have changed.

Recently, while working with SQL Server 2016 Management Studio, I came across a behavior that seemed unusual. I wanted to share this experience with you and demonstrate the behavior so that you can try it for yourself.

Let’s start by running a simple query in SQL Server 2016 Management Studio:

SELECT 'Pinal Dave
is an owner
of SQLAuthority.com'

As you can see, this query is written in three lines, with a carriage return at the end of each line. When we run this query in grid mode, we would expect to see the same output in both SQL Server 2014 Management Studio and SQL Server 2016 Management Studio.

However, when we copy the results from the grid and paste them into a text editor like Notepad or Word, we notice a difference in behavior. In SQL Server 2016 Management Studio, the carriage returns are removed and the complete text appears in a single line.

This behavior may not be desirable for some users who prefer to retain the original formatting of the text. Fortunately, there is a setting in SQL Server 2016 Management Studio that allows you to change this behavior.

To access this setting, go to Tools > Options in the SSMS menu bar. In the options window, navigate to the “Query Results” section and check the box that says “Retain CR/LF on copy or save”. By default, this option is unchecked, which is why the carriage returns are not retained when copying the results.

Once you check this box and apply the changes, you will be able to get the earlier behavior where the carriage returns are preserved when copying the results.

It’s important to note that this behavior may vary depending on the version of SQL Server Management Studio you are using. Therefore, it’s always a good idea to check the documentation or explore the settings to understand and customize the behavior according to your requirements.

If you have encountered similar requirements in your system or have any other questions or concerns, please let us know. We are here to help!