ben.mcintyre (10/7/2015)
Kim: just for posterity, no, all the joins in the database have identical data types on each side.The two different ID types reflect only that some tables might conceivably be required to generate more than int32.MaxValue records (noting that removal and regeneration of records when generating for example costing figures is quite frequent).
Is there a reason for using "Numeric(19,0)" instead of BIGINT?
Both store the same range of values (19-digit integer values), but BIGINT is an 8-byte true integer value, while Numeric(19,0) is a 9-byte scaled value, which has to carry the additional precision information.
I am just wondering if SQL is doing some scaling checks internally as part of the join and that is creating the additional time.
I am sure we have people on the forum that have deep internal SQL Server knowledge who can let me know how far off base (pun) I am. :hehe:
Perhaps the Unique constraint causes the optimizer to decide this index is really good, but then when it comes time to do the joins there is additional overhead?