What is the effect of omitting size in nvarchar declaration

Question

I usually define size when declaring parameters in my SP, like :

@myParam nvarchar(size) 

or when I casting or converting:

CAST(@myParam AS nvarchar(size)) 

Recently I've removed size from my CAST functions like:

CAST(@myParam AS nvarchar) 

and I'm bit worried if that is going to come and bite me when least expected :-(, since I noticed truncation on nvarchar variables when using recursive CTE and casting nvarchar without specifying size.

Any comments?

Answer 1

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: 1234567890 select str(1234567890)  --outputs: ********** select str(12345678901)  --outputs: 12345678901 select str(12345678901,11) 

Answer 2

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 ;).