6

At my company, we have a legacy database with various tables and therefore many, many fields.

A lot of the fields seem to have large limits (ex: NVARCHAR(MAX)) that are never reached.

Does arbitrarily making the fields their maximum width or 2 to 3 times larger than what is normally inputted negatively affect performance?

How should one balance performance with field lengths? Is there a balance?

gotqn
  • 42,737
  • 46
  • 157
  • 243
dotnetN00b
  • 5,021
  • 13
  • 62
  • 95
  • Not by much - but you cannot index a column of type `VARCHAR(MAX)` - that might be a big drawback... (any index can have a maximum of 900 bytes - a (MAX) column *could* be up to 2 GB - so it cannot be part of in any index) – marc_s Mar 18 '11 at 14:27
  • 1
    Related/Duplicate http://stackoverflow.com/questions/4378795/sql-performance-is-there-any-performance-hit-using-nvarcharmax-instead-of-nvar – Martin Smith Mar 18 '11 at 14:31
  • possible duplicate of [varchar(max) everywhere?](http://stackoverflow.com/questions/2091284/varcharmax-everywhere) – Justin Aug 29 '11 at 23:42

2 Answers2

7

There's two parts to this question:

Does using NVARCHAR over VARCHAR hurt performance? Yes, storing data in unicode fields doubles the storage requirements. Your data stored in those fields is 2x the size it needs to be (until SQL Server 2008 R2 came out, which includes unicode compression. Your table scans will take twice as long and only half as much data can be stored in memory in the buffer cache.

Does using MAX hurt performance? Not directly, but when you use VARCHAR(MAX), NVARCHAR(MAX), and those kinds of fields, and if you need to index the table, you won't be able to rebuild those indexes online in SQL Server 2005/2008/R2. (Denali brings some improvements around tables with MAX fields so some indexes can be rebuilt online.)

Brent Ozar
  • 13,174
  • 14
  • 68
  • 91
  • You can't index `VARCHAR(MAX)` and related fields. – Justin Aug 29 '11 at 23:40
  • 1
    Sorry, I should be more specific: if you need to index those TABLES, you won't be able to rebuild those indexes online. A table with those fields can't be reindexed online in SQL Server 2005/2008/2008R2. – Brent Ozar Aug 30 '11 at 01:12
2

Yes, the query optimizer can guess how many rows fit in a page, if you have a lot of varchar fields that are larger than necessary, SQL Server can internally guess the wrong number of rows.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Aaron Kempf
  • 580
  • 2
  • 11