What is the effect of omitting size in nvarchar declaration

If you omit the size, it defaults to 30. See this:

http://msdn.microsoft.com/en-us/library/ms186939.aspx

To see this in action, try executing the following statements:

--outputs: 12345678901234567890.098765432 select cast (12345678901234567890.098765432 as nvarchar)--throws "Arithmetic overflow error converting expression to data type nvarchar."select cast (12345678901234567890.0987654321 as nvarchar) --outputs: 12345678901234567890.0987654321 select cast (12345678901234567890.0987654321 as nvarchar(31))

Per @krul's comment; 30 is the default length for CAST; however the default length for a data definition or variable declaration is 1.

NB: There's also an STR function which converts numeric fields to strings, for which the default length is 10.

--outputs: 1234567890select str(1234567890)--outputs: **********select str(12345678901)--outputs: 12345678901select str(12345678901,11)

It will set the size to the default size and truncate the rest. This will indeed likely come to bite you, unless the default size is appropriate.

Event then, I would suggest you always specify the size to make it clear what you think the size will be, instead of having the next person that reads to code have to come to SO to ask what the default size of an nvarchar is ;).