[dba-SQLServer] [AccessD] A real puzzler

[John W. Colby]

The insert finally completed.  There are 7043347063 rows in the table.  
Data space 170 GB, index space 151 GB.

The table consists of an identity PK (bigint) which is a literal PK and 
is therefore clustered on the PK.
The only purpose of that PK is to cause the clustering.

There are then two other fields, a PKSimmons, values 0-221, and a 
PKDB101, values 0-225 million.

And finally a second index (other than the clustered index) on the two FKs.

In hind sight I probably should have not used the identity, instead 
simply making the two FKs be the PK itself, clustered.  It is never 
clear to me, but I believe that if I want to do that I would need to 
pull the two PKs sorted (taking a lot of extra time on each select) or 
the insertion into the clustered index would be a mess, since my 
understanding is that clustered PKs are required to be inserted in 
sorted order.  An identity is by definition incrementing values and thus 
slide right in in consecutive order.

Not being a SQL Server guru that may be just a serious misunderstanding 
on my part.

John W. Colby

On 8/9/2015 1:00 PM, Arthur Fuller wrote:
> Hi JC,
>
> I have no suggestions other than those already presented, but I do have a
> couple of questions. How large is the database itself? Is it clustered? How
> large is that one very large table?
>
> A.
> ​
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>