Posted by Erland Sommarskog on 04/25/07 22:08

Nacho (nacho.jorge@gmail.com) writes:
> I'm designing a new database and I have a doubt in which surely you
> can help me.
> I'm storing in this database historical data of some measurements and
> the system in constantly growing, new measurements are added every
> day.
> So, I have to set some extra columns in advance, so space is available
> whenever is needed and the client doesn't have to modify the structure
> in SQL server.
> The question is: the more columns I add "just in case", the slower the
> SQL reads the table?
> Of course the "empty" columns are not included in any query until they
> have some valid data inside.
> Will I have better performance if I configure only the columns being
> used at the moment, without any empty columns?

As always with performance questions, there are several "it depends".

If these just-in-case columns are varchar (or nvarchar or varbinary),
they only take up two bytes each extra, which may not be cause for
alarm. On the other hand, if they are char(50) or some other fixed
length, they take up the full space, NULL or not.

Next thing that matters is how the access against the tables are. If
there plenty of table scans, or for that matter range seeks in the clustered
index, like OrderDate BETWEEN @fromdate AND @todate, there is certainly
a performance cost for adding more bytes to every rows. More bytes per
row, fewer rows per page, and more pages to read for the same number
of rows. On the other hand, if access mainly is by non-clustered index
and key lookup, then the extra bytes do not have the same cost.

But this latter pattern is rarely the only access pattern for a table,
so the conclusion is that, yes, there is a cost. But how big the cost
is, is more difficult to tell.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]