With every new release some of the default behavior can surprise because you are so much used to them. But when they don’t behave the same, it baffles to when some of these changes were introduced. In pursuit of finding such behavior, while I was working with SQL Server 2016 Management Studio, I was surprised to see a behavior which didn’t seem to be right. You can also try it too.
Here is the query which would demonstrate the behavior.
SELECT 'Pinal Dave is an owner of SQLAuthority.com'
This command is in three line and there is a carriage return at the end of each line. Once we run above query in grid mode, we would see below in SQL Server 2014 Management Studio and SQL Server 2016 Management Studio.
Yes, you are right! There is no difference in output. Now, here is the fun part – Copy the results of the grid and paste it in any text editor: SSMS or Notepad or Word or anywhere you want.
Did you see the difference in behavior? Here it is in Notepad.
When we copy paste from SSMS 2016, it removed carriage return and complete text came in single line. I think that’s something which few people may not like. I searched further on documentation and found setting to change this behavior in SQL Server 2016 Management Studio. It didn’t take much time to find below setting (Can be reached by Tools > Options in SSMS menu bar)
By default, its unchecked and that why it’s not retaining CR/LF (carriage return / Line Feed). Once I checked the box, I was able to get earlier behavior. Do have such requirements in your system? Do let us know.
Reference: Pinal Dave (https://blog.sqlauthority.com)
40 Comments. Leave new
This post was a life and sanity saver. Just one thing to add. For me anyway, I had to close SSMS and reopen before the checkbox setting took effect.
Good point Jeff.
Hi Jeff if you can just open a new query, no restart needed
What about when doing dynamic sql and reporting the sql to grid results? Much like your example, the dynamic sql was a multi-line value put into a variable, then the variable was used in a select. The sql itself is an update statement that allows a dba to Copy, paste and f5 any recrod that they choose have a default update performed. (this is quick and dirty hack approach, nothing production here)
As my style is to always include a –select top 10 * before the FROM in the update for quick verification/accuracy, not having the breaks causes the statement to be unusable.
I know I could force the line breaks. I’m curious how you might approach this.
DECLARE @sql nvarchar(500) = N’UPDATE a1
SET IsProcessed = 0
–SELECT TOP 1 *
FROM ATable a1
WHERE myCriteria Is Not Null AND OtherStuff = SomeValue’;
SELECT @sql;
Hi. Strange SSMS 17.9.1 behaviour with NewLine
select ‘1’ AS A
UNION ALL SELECT CHAR(13)+CHAR(10)+’2′
UNION ALL SELECT CHAR(13)+CHAR(10)+’3′
Run this with “Result as text”
Result will be
A
—-
1
2
3
(3 rows affected)
SSMS interprets CHAR(13) and CHAR(10) as 2 independent characters and as such show an extra line in the result.
This only affect SSMS result.
When running this query from an application ans writing the result to a csv file, the duplicate row is not there as the 2 characters are interpreted as one.
Anyone can confirm that ?
Will these settings is extended for .net data adapters which uses select query with char(13) in it ?
Late to the party, I know, but I like the default for SSMS 2016.
More often than not, you want to copy your findings to Excel or something similar. A carriage return/newline in there will break everything, with the rows going out of sync
It doesn’t seem to work in Management Studio v18.9.2. The following SQL works fine when rendering results to text, but if you render in grid mode, then copy/paste to notepad or notepad++ you lose your newlines (with the option you mention checked). Sample SQL: select ‘hey’ + char(13) + char(10) + ‘test’