Large number of columns

Giganews Newsgroups
Subject: Large number of columns
Posted by:  simon (zupan.n…
Date: Tue, 2 Aug 2011

I have table which has about 100 columns, and many of them are
nvarchar(4000). The database is SQL2005 (so, no sparsed columns).
The table is normalized (maybe the table still could be splited into
more tables but it's not my design).

In SQL2000 there is limit of row size : 8060 bytes, so the size of one

In SQL2005 the row size can be greater, because if row exceed the
available page space, it is pushed into row-owrflow page with pointer
to the original page.

I would like to know, which is better type for string columns, using
nvarchar(MAX) types or using nvarchar(4000) - if string is always less
than 4000.

I think if I use nvarchar(max), the table scan in query would be much
faster, because there is less pages to scan, because I have only
pointers to that data somewhere on disk.

Your opinions, what is best in this case and why?

Thank you,