Description
I want to share some thoughts from my experiences doing project-based work as an IT contractor. Working as an IT contractor means taking on a mix of short- and long-term projects. In a short-term project, you need to work on a specific task with a team of developers, you need to solve a problem quickly, and then you are done with the project! The team of developers will continue their work and you will search for a new project and challenge.
Three stories…
Some time ago, I was on a project where most of the members were very experienced Java developers. They were developing a document management system that was metadata-based and written almost exclusively in Java. The database was Oracle. The manager and their technical leader invited me to do some Oracle work, to solve some database-specific tasks for the project. All the team members were pure application developers and no one had much expertise with databases. Everything was transformed in SQL, but no one cared too much about that. The developers were good artists and the design was very sophisticated. It was written following the principles of object-oriented programing.
One day, I was in the office listening to some of my colleagues discuss a certain SQL statement. They asked me some questions, which I did not understand. I went over to their desks and looked at what they were working on. I was still confused because the SQL statement in question was quite basic. A self-join was required. I added the self-join and everything worked perfectly well. The team members were amazed! They were not aware that a table can be referenced many times in a join using various aliases. They were not familiar with a self-join!
Later we spoke some more about our careers. They were surprised by the fact that, the same way they spend their entire career doing Java, I spend my entire career doing database development and especially doing SQL. “SQL is so simple. Your task is such a trivial task. Your life is much easier than ours,” they said. “We fight with such complicated concepts and you just manipulate rows and columns in your simple query language!”
Another time I was in France with two of my good colleagues, Clark and Marjorie. Clark is American; he has been my manager for a long time and he is a very good friend. Marjorie is an elegant French woman; she is my colleague in support in the French area. We were in a café and we were chatting. We ordered some wine. In addition, I asked for some mineral water. In my country, people mix sparkling water with wine. I thought about asking Marjorie what she thought of this custom, but I knew the answer. To any French person, mixing water with wine is a blasphemy. So I left the water where it was, apart from the glass of wine!
What is the connection between these three shared memories? Being an application developer does not make you SQL independent, in most cases. Whether you like it or not, you will be in contact with SQL, sometimes more or sometimes less! The same way any database developer knows the basics of structured programming, normally most application developers know the basics of SQL. There is no such thing as a superior or inferior language, just useful and compatible ones according to the business. When you are in the country of relational databases, even if you are an application developer, you should learn how to write fair SQL.
Writing Correctly Is Critical to Quality
I believe that one of the most important aspects is the way we write our code, our style of development. It is hard to say what it means to write correctly in the context of software development. There is a degree of subjectivity involved in any judgment of this type. However, I believe that no one can argue with me when I say that the style of development and the way we write our code is proportional with the quality of the software we build.
I am a database developer with many years of experience. During my time spent inside relational databases, I have gathered enough experience to be able to explain that the database area requires a certain and distinct style of development. This is the topic of this book: how to write code inside a relational database in a certain manner, distinct and specific.
There are millions of lines of codes in databases all over the world that are written in a total inadequate manner and these lines of code cause many performance issues in many places. All of these performance issues can be avoided if the programmers understand that a certain style of development is required in a relational database.
The style of this book is not academic although it is a book about database programming. I am a practical person and I think that programming is part of our lives.
Basic Terminology
Due to the direct style of this book and the fact that this book is about database development, which means that it is a technical book, I define some basic terminology. The Internet is full of classifications and manuals, courses and documentation, libraries and practical examples. The concepts are explained and re-explained by specialists.
I want to be consistent and to avoid any possible confusion, so let’s clarify some concepts and keywords used in this book.
A data-oriented software application is composed of at least one user interface, graphical or not, and the database behind it. When I say database, I am referring mostly to a relational database. One of the main goals for any software application of this type is to allow data access in the database via the user interface. The end users read and write from the database via the user interface. This is what I call a classic data-oriented software system. I will use the term “classic software application” for simplicity. The focus is on the database, so the topic of the software application is the database section.
Another type of data-oriented software application is one that transfers data between classic systems. Medium or large companies have many classic software applications. Every software system of this type has its own purpose, its own database, and will cover one part of the business or another. I call this a specific software application. In most cases, there is no user interface and no classic end user. One or many classic systems are the targets and the one or many classic systems are the sources. This system can be a replication system, a data migration system, or an Extract-Transform-Load system (ETL) part of a data warehouse system.
Please be aware of the distinction. One is to develop in a database in a classic system and another one is to develop in a specific system. My main interest in this book is related to the variety of specific systems where many developers work in the same manner they work in classic systems.
Finally, please read Oracle database as Oracle and Microsoft SQL Server as SQL Server.