Processing 100 of 1,000,000 rows in T-SQL2005

Question

I have a couple of databases containing simple data that need to be imported into a new format scheme. I came up with a flexible scheme, but it relies on the fact that critical data from old libraries are stored in one table. This table only has a primary key, a foreign key (both int), a datetime, and a decimal field, but adding the number of rows from the two old DBs indicates that the total number of rows for this new table will be around 200,000,000 rows.

How do I deal with this amount of data? This is data that extends for about 10 years and needs to be available. Fortunately, we do not need to draw even 1% of it when executing queries in the future, but all this should be available.

I have ideas based on the fact that I have several tables for the year, supplier (source data), etc. - or even one database for each year, with the last 2 years in one database (which will also contain stored procs to manage all this.)

Any help, ideas, suggestions are very, deeply, very much appreciated,

Mt.

Answer 1

. , ( ), , , .

, , ? 1%, - 99% .

-, .

, "" "".

int ( ) datetime int, ,

SELECT * FROM megaTable WHERE datetime > dateTimeToEpoch('2010-01-23')

, , , datetime. , , int YYYYMMDD.

Answer 2

? SQL- , Microsoft SQL 2005, .

, , . , . , :

• , , .

• (, - ).

• (, ), .

• . . ​​ 2011-01-01 00: 00: 00.001, ? , ? ? ?

, "" "" (, , , , ), SQL- ( ). , . , , . .

Answer 3

(2 ints, 1 datetime, 1 decimal) , . SQL Server 2005 .

, . .

EDIT. Assuming you are using DECIMAL (9) or less, the total size of the tuple is 21 bytes, which means you can store the entire table on less than 4 GB of memory. If you have a decent server (8 GB of memory), and this is the main user of memory, then the table and secondary index can be stored in memory. This should provide ultra-fast requests after a slower warm-up time before the cache is full.