Slim but long SQL Server 2005 Table

Question

Hi,

I have an SQL table with the following structure:

Code1 - int
Code2 - int
Val1  - real
Val2  - real
Val3  - real

There is only one index (clustered) were Code1 is the first indexed column and Code2 is the second. The size of a single record is 20bytes.

I need to be able to store around 150,000,000 records and the biggest select operation would be on 500,000 records. I assume that the size of the table will be around 3GB

I would like to know if this design will work or there might be 'unexplained' problems or slowdowns when dealing with such a big table.

thank you for your answer

Gilad.

Answer 1

There is a very complete question about SQL performance and large tables: Very large tables in SQL Server

Answer 2

Basically, a table with 150 million rows is nothing for SQL Server - won't even break a real sweat :-)

The point really is: how do you access the data? What kind of queries will you have? E.g. if you have queries that have a WHERE clause with just the "col2" column, then you don't have a good setup with a clustered index on (col1,col2).

Also: how is the data distributed in your fields? Which are selective, which are more uniform? If col1 or col2 are highly selective (e.g. a single value selects significantly less than 2% of the data), then use that field for your selects, if ever possible. Indexing something like a "gender" field which might have two, three different values won't really help, since any select using that field as a WHERE clause will always return way too much data to be effective.