SQL Server Database Design – Using Column Size Larger Than Necessary

database-designsql servervarchar

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?

Best Answer

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.

Related Question