Menu
Why is the default size of nvarchar is 255 (MSSQL Server)?
I mean why not 256?
And should I use nvarchar(63) instead of nvarchar(64) and so on ... 127 instead 128?
I tried to Google this question and I didn't find any comprehensive answer.
Edited:
About default size: It is just my assumption. Hibernate and EclipseLink (java persistence frameworks) convert String to nvarchar(255) so I thought this is so kind of standard.
637
nvarchar [ ( n | max ) ] The storage size is two times n bytes + 2 bytes. For UCS-2 encoding, the storage size is two times n bytes + 2 bytes and the number of characters that can be stored is also n.
nvarchar(255) (in SQL Server) stores 255 Unicode characters (in 510 bytes plus overhead).
The minimum size of the NVARCHAR value is 1 byte. The total length of an NVARCHAR column cannot exceed 255 bytes. You can store character strings that are shorter - but not longer - than the specified value. You must always specify the max parameter.
You can't create a NVARCHAR(8000). If you need to go beyond 4,000 characters with the NVARCHAR data type, you need to use NVARCHAR(max).
The documentation is quite clear:
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.
ALWAYS use a length specification for these variables.
As for your second question. The types varchar and nvarchar encode the length of the value for each value. I am not sure if SQL Server uses 1 byte for lengths up to 255 and then 2 bytes for lengths up to 4000/8000. The max does use 4-bytes. (And all of them can now spill over to additional pages.) In general, for variable character strings, you should use a value that is amply large for whatever you want to include, so you don't have to change the length using alter table. Only the necessary storage for a particular value is used, so a slightly longer declaration does not affect performance.
Note that for char and nchar, you want the shortest possible length because the column will actually occupy that storage space.
199
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
