Best way to model Customer Address

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

How universal is the LIMIT statement in SQL?

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

PARTITION BY with and without KEEP in Oracle

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

SQL method to replace repeating blanks with single blanks

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

Core Data Performance with Single Parent Entity

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