In a previous post, I wrote about how we make sense of the world by modelling relationships between things as tree-like hierarchies. This time we will add to this hierarchical data structure, a representation derived by calculating all possible paths. This set of paths is referred to as a transitive closure, and can be thought of as the set of all paths if you start at each node in the tree. Read More...
SQL views are aluring as a means of abstraction; a “building block” to hide away commonly used complexity. It is no wonder then that us developers will try them out, and before you know it, your clever recursive CTE view on that hierarchy is used everywhere, by everyone, but how is it affecting overall database performance… They look like tables, can be joined on, selected from, and in some cases even updated just like tables, yet the reality is that they are not like tables. Read More...
This article examines how sub queries can be substituted for the far more readable common table expressions, or CTEs available in many RDBMS systems. I was motivated to write this article when a friend who is fairly new to SQL expressed difficulty in grasping queries containing nested sub queries. If you’ve never heard of CTEs before and you want to get the most out of this article, I recommend you get AdventureWorks2014 sample database and experiment a little with the queries below. Read More...
“He began to copy one of our tables, which contained partial user information, including email IDs, hashed passwords, and last tested URL. His copy operation locked the database table, which raised alerts on our monitoring system. On receiving the alerts, we checked the logs, saw an unrecognized IP, and blocked it right away. In that time, the hacker had been able to retrieve only a portion of the data.” – From the postmortem of the Browser Stack hack of 9th November, 2014 at 23:30 GMT Since relational database management systems (RDBMS) have been used in production environments since 1970 (Micro DBMS), and the theory on which they run was developed in the preceding decade,and perfected in the three remaining decades of the previous century (long ago), it was not surprising that, as a subject, it received very little attention in our curriculum - at least where I studied. Read More...