Using column size much larger than necessary

Question

I'm creating an SQL Server database with someone else. One of the tables is small (6 rows) with data that will probably remain constant. There is a remote possibility that a new row will be added. The table looks something like this:

CREATE TABLE someTable (
    id int primary key identity(1,1) not null,
    name varchar(128) not null unique
    );
INSERT INTO someTable values ('alice', 'bob something', 'charles can dance', 'dugan was here');

I'm looking at the char length of that name column, and I think that its values are probably never going to be larger than, say, 32 characters, maybe not even larger than 24. Is there any advantage to my changing this column to, for example, varchar(32)?

Also, is there any advantage to keeping default column sizes to multiples of 4, 8, 32, etc?

Answer 1

SQL Server uses column lengths when allocating memory for query processing. So, yes, in short, you should always size columns appropriately for the data.

Memory allocations are based on the number of rows returned by the query multiplied by half the declared length of the column.

Having said that, in this case where you've got 6 rows you probably don't want to over optimize prematurely. Unless you JOIN this table to another with millions of rows, there won't be a massive difference between a varchar(24) and a varchar(32), or even a varchar(128).

Your second question asks about aligning column lengths on binary multiples. That's not required at all since SQL Server stores all data in 8KB pages, regardless of the length of each column.

Answer 2

With 6 rows, no, there will be no observable benefit. That entire table will fit on a single page so lowering the maximum potential space you’ll use on that page while still occupying that entire page is really no different in all practical sense.

On larger tables, though, right-sizing is crucial. The reason is that memory estimates will be based on the assumption that every value will be 50% populated. So if you have varchar(128), every value will expect to occupy 64 bytes, regardless of the actual data, therefore memory grants will be 64b * number of rows. If all the values will be 32 characters or less, making it a varchar(64) or even varchar(32) is probably a better choice. If a large percentage of values are close to or at the cap, you could even argue for char to take volatility out of it.

As for benefits of having string lengths capped at powers of 2, I don’t think on today’s hardware anyone could demonstrate any obvious advantages.