A friend of mine claims that in a typical database, using (for example) nvarchar[256] will give marginally better performance than nvarchar[200] or nvarchar[250] because of the granularity of page allocations.
Is there any truth to this whatsoever?
Thanks!
If anything it is probably worse because of allocation overhead. When you allocate nvarchar(256) the database likely includes a couple of bytes for the length, so the storage requirement may actually be 258.
There are so many levels of abstraction at play here that you are not going to see any benefit by trying to optimize at the top for something that only matters at the very bottom, and you might just make things worse!
This is not true. Tables are allocated on disk in 8k pages. When a table is read from disk, the entire page is read in one IO operation and stored in memory. Therefore, the length of a column will not affect memory alignment at all. In fact, with non-variable length data types, shorter is definitely better: an nchar(200) column will allow more rows per page than an nchar(256) column. This allows more rows to be read per single physical IO, which can have a dramatic affect on database performance.
I wonder if the friend of yours somehow arrived at his conclusion on his/her own or if this was a case of myth-propagation.
There's a great presentation by Tom Kyte on "Things you know" that pretty much everyone should watch before making claims like the one above: Things you know
Think of it this way: If nvarchar[256] performs better than nvarchar[200], wouldn't the DBMS just make an nvarchar[256] when you ask for an nvarchar[200]? (But still make it look like nvarchar[200])
Good DBMS's have some extremely advanced optimizations. I'm pretty sure they have all the simple ones too.