Compare nvarchar(10) with nvarchar(1000)

I had this question for quite a long time.

It seems the latter one don't take any extra storage space than the previous one.

As long as the real string length is less than 10.

Is that mean the latter one not cost anything?

I once heard the different is when they are in memory. But not sure of it.

Can anyone explain it and provide some official reference on it?

Thank.

Yes. That’s what Var(rying)char means. It is not a fixed length value. Whatever your value length for the same length only the memory occupied.

That means there is no impact when you use nvarchar(1000) where you only have nvarchar(10) values.

|||

And there's a little note on it in Books Online

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

"The storage size, in bytes, is two times the number of characters entered..."

On a slight tangent though, it does also mention that a varying length column should add an extra 2 bytes to storage though when i store the value 'RICHARD' in a nvarchar(10) column, running DATALENGTH only shows 14bytes stored.

Where are these missing 2 bytes?

|||

NOTE:

Datalength and Storage Size are different. It need not to be same. Storage Size of NVarchar is

Char Length(value) * 2 + 2

or

Datalength(value) + 2

For varchar,

Char Length(value)+ 2

Or

Datalength(value) + 2

Why additional 2 bytes on storage?

To identity the length of the value. Bcs the values will be stored as binary(or bytes). It will first fetch the length of the value which will guide how many bytes to read for the current value.

|||Cheers Mani.

Should i be able to see these extra bytes if i delve into DBCC PAGE?
|||Hi Mani,

Is that mean there is no performance impact when use nvarchar(1000) vs. nvarchar(10), even in memory?

If this is the case, then why should we choose N carefully?

Thanks
|||

While retriving the Varchar definition size wont affect your performance.