Often SQL Server will have to allocate working memory to run a query. The amount of memory granted depends on many things including column data type and length. For (n)varchar it guesses actual values are, on average, half the declared length and calculates required memory accordingly.
So overly-large columns can result in overly-large memory reservations, reduced throughput and poorer system performance.
To illustrate this I've created a series of tables. All are of the form
create table dbo.t10(c varchar(10) null);
create table dbo.t2000(c varchar(2000) null);
The column's length changes from table to table. The shortest will be 10 (as shown) and the longest will be 2,000.
Each table is populated with one million rows. Each row consists of the single letter 'a'. So all tables hold the identical amount of data and occupy the same amount of space on disk (
exec sp_spaceused 'dbo.t10';).
I use a very simple query
select c from dbo.t10 order by c option(maxdop 1);
The sort means a memory grant will be requested. Using maxdop 1 avoids the risk of some plan going parallel and complicating the comparison.
I run this for each table t10 through t2000, capture the actual execution plan and record the MemoryGrantInfo. Here's the plot of how desired memory (in KB) varies by column length. I think it is quite convincing.
There is no meaning in the size range 10 to 2,000. They are just arbitrary, chosen for convenience, not to illustrate any particular point.
Similarly the letter 'a' was the first thing my fingers typed.
My system has ample memory. Desired, Requested and Granted are identical in every test. I chose to plot Desired memory as it would be the largest if there were a difference.