Number of columns and performance

[Nacho]

Hi,

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?

Thanks in advance.

Ignacio

[Greg D. Moore \(Strider\)]

"Nacho" <na*********@gmail.comwrote in message
news:11**********************@s33g2000prh.googlegr oups.com...

Hi,

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.

Umm... I don't see why you have to do this now.

Do it later.

I mean will you even know the type or size of the columns now?

And how will you even give them meaningful names when they have none now.

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?

A tad (since there's a small metadate overhead on the leave nodes).

But more importantly, you'll have a messy schema on your hands. I'd wait to
add them.

>
Thanks in advance.

Ignacio

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

[Erland Sommarskog]

Nacho (na*********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx