In my owned systems I separate what is better as an INT Pkey vs a GUID.
The Name table is GUID because it grew wild at first and then slowed down to a crawl, or few new rows being added they can go anywhere in the index pages with a 70% free space setting with little problem.
Actual business transactions have an int because they grow quickly and no need to force index repagination frequently as invoicing is run nightly. Trading Partners are GUID because they come from a test system and with all of the mess in contracts it is so much easier to just work with the GUID.
On Mon, Mar 28, 2016 at 10:13 AM, < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 2016-03-28 11:01, Stephen Russell wrote:
For me it was moving business that was prepped in test and easily moving it to production. The keys are no longer an issue.
The issue with indexes is the free space available in an index page before it has to split into two pages for the same data.
This is only in a primary key because it can always be a problem in the secondary one. The GUID generated may fit into any one of different btree index pages, but a int will only be placed at the last page.
The point you made about switching between systems (Test, Production, Local, somewhere else) was the bonus for me using this approach. That, and as mentioned earlier, being able to combine data from different disconnected datasets (Database1, Database2, etc.). No more worrying about AutoInc either meaning something else to a different disconnected dataset, or duplicate integer keys (and see reason prior as a result).
[excessive quoting removed by server]