How to type a new line character in SQL Server Management Studio

You can't. (Edit: other good answers have been posted with some workable methods.)

My preferred method to do this is via a direct SQL update.

Two general techniques:

--Literal returns inside strings
UPDATE mytable
SET textvalue = 
'This text
can include
line breaks'
WHERE rowid = 1234

--Concatenating CHAR codes
UPDATE mytable
SET textvalue = 'This text' + CHAR(10) + CHAR(13) 
   + 'can include' + CHAR(10) + CHAR(13)
   + 'line breaks'
WHERE rowid = 1234

The former is a little easier to work with, but could give inconsistent results if you paste in text from outside sources with unknown line-ending codes.

The latter is somewhat harder to work with but is more likely to give you consistent and reliable results.

You can prepare the text in notepad, and paste it into SSMS. SSMS will not display the newlines, but they are there, as you can verify with a select:

select *
from YourTable
where Col1 like '%' + char(10) + '%'

In SSMS, you can't print new line with select, just using PRINT instead

DECLARE @text NVARCHAR(100)
SET @text = concat(N'This is line 1.', CHAR(10), N'This is line 2.')
PRINT @text

This is possible if you have an existing Newline character in the row or another row.

Select the square-box that represents the existing Newline character, copy it (control-C), and then paste it (control-V) where you want it to be.

This is slightly cheesy, but I actually did get it to work in SSMS 2008 and I was not able to get any of the other suggestions (control-enter, alt-13, or any alt-##) to work.

Ronnie,

The data grid appears to be swallowing any attempts to paste a newline character, including the use of ALT+010. Microsoft does not list any shortcut keys that would help. The usual suspects

<ctrl>enter, 
<alt> enter, 
<ctrl><alt> enter, 
<shift> enter, etc

don't work, as you pointed out.

For the record, if you are doing web development, the linefeeds will not show up in the browser anyway (they are interpreted as extra whitespace, and are ignored). I had to replace line feed with

<br> 

everywhere I wanted a line break to show up in the browser (text areas will accept line feeds as usable input).

Even if you could insert a line break, I don't think the data grid view in SSMS is capable of displaying it.

I use INSERT 'a' + Char(10) + 'b' INTO wherever WHERE whatever

You're talking about right-clicking on a table and selecting "Edit Top 50 Rows", right?

I tried [Ctl][Enter] and [Alt][Enter], but neither of those works.

Even when I insert data with CR/LF (using a standard INSERT statement), it shows up here in a single line with a rectangle representing the control codes.

If you are trying to enter data directly into the table in grid view (presumably Right Click TableName and Select Open Table), then you can enter your unicode text string and wherever you want a carriage return just type 13 with the alt key pressed in the numeric keypad.

That would be Alt+13. This works only from the numeric keypad and does not work with the number keys on the top of the keyboard. The carriage return will be stored as a square