nvarchar Storage

[ibwebn65]

This is probably going to be an elementary question, but I just want to see if I understand the storage of nvarchar correctly.

The Microsoft web site has the following to say about nvarchar.

"Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length."

I have the following two question about nvarchar. I just want to verify it is not the length attribute of the column that dictates the storage being used, it's the number of characters entered in the column's field that dictates the storage being used.

Question 1
If I create an nvarchar column with a length of 4000, and someone puts data in the column field containing only one character, then the storage size will be two bytes? (1 character * 2 = number of bytes)

Question 2
If I create an nvarchar column with a length of 4, and someone puts data in the column field containing only one character, then the storage size will still be two bytes? (1 character * 2 = number of bytes)

 

[mrdenny]

Question 1: Correct.
Question 2: Correct.

NVARCHAR is a variable length field, so any the space needed is used.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[JamesLean]

Having said that, it is not best practice just to create all your varchar fields with the full length if you are never going to use it. You should set the length to the maximum that you are going to need in that column.

Also, if you are not actually going to need to store unicode data (ie only standard alpha-numerics) then use varchar - this only uses 1 byte per character.

--James

 

[ibwebn65]

Thank you for your responses. They were extreamly helpful.

 

[mrdenny]

You should not set the nvarchar or varchar fields to the maxium. If you do this, and start putting more data than your origionally thought some data could get truncated.

You can only put 8060 bytes per record. If you have 3 varchar fields of 8000, and each one get 3000 bytes of data, your data will get truncated and SQL will return an error.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)