I tend towards first approach for all the usual reasons of normalisation. This approach also makes it easier to perform data cleansing on mailing details. If you are possibly going to allow multiple addresses (mail, residential, etc) or wish to be able to use effective dates, consider this approach Customer (id, phys_address_id) Cust_address_type (cust_id, mail_address_id, … Read more
I’m not familiar with SQLite but a little Google’ing turned up this. The documentation says If the database schema contains foreign key errors that require looking at more than one table definition to identify, then those errors are not detected when the tables are created. Instead, such errors prevent the application from preparing SQL statements … Read more
LIMIT has become quite popular with a variety of Open Source databases, but unfortunately, the fact is that OFFSET pagination has been about the least standardised SQL feature of them all, having been standardised as late as in SQL:2008. Until then, the jOOQ user manual page on the LIMIT clause shows how the various equivalent … Read more
You cannot expect any records to be outputted when using a GROUP BY clause, when no records exist in your source. If you want an output of 0 from the SUM and COUNT functions, then you should not use GROUP BY. The reason is that when you have no records, the GROUP BY clause has … Read more
Logical reads are counted differently for worktables: there is one ‘logical read’ per row read. This does not mean that worktables are somehow less efficient than a ‘real’ spool table (quite the reverse); the logical reads are just in different units. I believe the thinking was that counting hashed pages for worktable logical reads would … Read more
MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) The statement can be considered in (roughly) right-to-left order: OVER (PARTITION BY deptno) means partition the rows into distinct groups of deptno; then ORDER BY sal means, for each partition, order the rows by sal (implicitly using ASCending order); then KEEP (DENSE_RANK FIRST means … Read more
This works: UPDATE myTable SET myTextColumn = REPLACE( REPLACE( REPLACE(myTextColumn ,’ ‘,’ ‘+CHAR(1)) — CHAR(1) is unlikely to appear ,CHAR(1)+’ ‘,”) ,CHAR(1),”) WHERE myTextColumn LIKE ‘% %’ Entirely set-based; no loops. So we replace any two spaces with an unusual character and a space. If we call the unusual character X, 5 spaces become: ‘ … Read more
As other already pointed out, the queries return different results and are comparing apples to oranges. But the underlying question remains: which is faster: keyset driven paging or rownumber driven paging? Keyset Paging Keyset driven paging relies on remembering the top and bottom keys of the last displayed page, and requesting the next or previous … Read more
I worked with @deathbob on this project as the iOS lead. In our instance I had multiple classes which contained the attributes “remote_id” and “remote_update”. I initially set the tables up using subclasses. I had a “RemoteEntity” abstract entity which contained those attributes and a bunch of other entities which inherited from it, each with … Read more
It appears that storing the data in a BINARY column is an approach bound to perform poorly. The only fast way to get decent performance is to split the content of the BINARY column in multiple BIGINT columns, each containing an 8-byte substring of the original data. In my case (32 bytes) this would mean … Read more