There are many factors that have contributed to the slow but steady rise of the popularity and ubiquity of SQL as a way to work with data. Among all these factors, the most significant one in my opinion is the concept of “what” rather than “how”. The second factor of greatness of SQL for me is the ability to quickly and easily answer questions about data. I choose to start out with these two big ideas that to me, is fundamental to the language. 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...