[dba-SQLServer] Billions and billions of rows

[John W. Colby]

So the table ended up with 7 billion rows and a combined 320 gb data and 
index space.

I decided to rebuild the table using just the FKs (PKSimmons and 
PKDB101), and a PK of those two fields combined.  I also compressed the 
index (page).  It is unclear how much compression I will get since the 
data being compressed are two integer FK fields.  All just scientific 
inquiry.  :)

I can say however that the Index storage is almost nothing.

So I ran a count on the first attempt at this junction table, selecting 
just the second PKSimmons and the time was 1.5 minutes vs just over 2 
minutes for doing it the old (hard coded) way.  Not a significant 
savings I must admit.  That was with a noncompressed / non clustered 
index however.  We shall see whether a single compressed / clustered 
index will speed things up.  Of course it is likely to take me another 
day to yank the two FK fields out of the first table and insert into the 
new table (insert into / select).

-- 
John W. Colby