I've seen a lot of questions here which make me think 'ok this is someone who isn't thinking about databases in a database kind of way'. Which got me thinking... How, as database administrators, developers etc do we think about things in 'the database way'? Jeff has in his signature 'stop thinking about what you want to do to a row, and start thinking about what you want to do to a column' - which is a good start... so - when teaching someone about databases, how would you approach trying to get them to think about the problems in the right way?
Answer by TimothyAWiseman ·
There are a few points I try to emphasize when I try to help someone learn SQL, especially if they are coming from a procedural background.
On a related note, I tend to discourage the use of Object Relational Mappers (ORMs) currently. I suspect in the future they may turn out to be wonderful thing that will help add layers of useful abstraction. Right now, they tend to write very ineffecient SQL queries and obscure what is actually being executed which can make them hard to troubleshoot in general and very hard to troubleshoot when dealing with performance issues. Also, they tend to make it hard for developers to learn the set based thinking that can be so useful in writing truly set based code when dealing with the database.
Eventually the ORM technology may improve and I may change my opinion, but until then I tend to think well written stored procedures are far and away the best way to access SQL data and even hard coding SQL commands into the application tends to be better than ORM (though not nearly as good as well written stored procedures).
Answer by Steve Jones - Editor ·
I think Tim and Grant have great answers. The thing I'd say as well is to step back and don't try to control things at a fine grained level. In most other languages you work with every detail: pointers, variables, classes, you must handle them all.
SQL is more of a wider view language where you tell the server to get xxx as a group, rather than working with every item.
Other than that, it's a balance. There are multiple ways to solve issues, whether writing a query, separating storage, or indexing. However as you change one of the areas, it has effects in other areas. Changing the number of items in an index, or the order, has changes on multiple queries. Same for writing different queries, adding temp tables, etc. You are affecting a shared resource with your changes, something that typically doesn't occur with most other programming. You're not in a box, it's a shared sandbox.
Answer by Grant Fritchey ·
First, I usually have to spend a lot of time teaching that relational storage is better than flat storage (in most ways, not all, and yes, there are exceptions). That goes hand-in-hand with teaching that JOINs are not bad things and that yes, you can have more than two or three without the performance of the query suffering. In other words, I spend a lot of time trying to get people to unlearn persistent and innacurate myths about relational systems.
Next thing to try to teach them is to question the requirements. Yes, I know the user asked to see "everything" but are they really going to read 50,000 rows? No, they're not, so what is it that they really need to look at.
Then you start working on getting them to think in sets and eliminate procedural thinking in loops & cursors... etc.
It's a long hard road.
Answer by CirqueDeSQLeil ·
Beyond things that have been said (steve, Tim, Grant), I think an important thing to note is to look at the big picture. Along the lines of what Jeff teaches (set rather than row), is the need to know the impact downstream from the code. Understanding the big picture is a difficult thing to teach, and is learned by feel and experience. Get to know the systems you support. Understand schema changes, code changes, and hardware changes and how the will affect something else in the database, application, or environment.
Answer by Jeff Moden ·
so - when teaching someone about databases, how would you approach trying to get them to think about the problems in the right way?
First, thank all of you for the very kind words. I continue to be humbled by your generous comments.
I've had a couple of private students and have taught a couple of groups of folks including one at my local PASS chapter. The first thing I teach is the classic "HELLO WORLD" problem. The very next thing I teach is the same thing all programming courses (should) teach... how to count from 1 to some number. It's a real opportunity to demonstrate the differences between the procedural world and the set based world especially in the areas of performance and simplicity of code. It's also where I teach that SQL Server loops in the background so you don't have to. Since I also have to demonstrate the performance differences between several methods, it's also an opportunity to show them 4 different ways to measure the performance of their code. After all, "A Developer must NOT guess... a Developer must KNOW." ;-)
Oddly enough, I start the counting demonstration with a couple of flow charts (a nearly lost art nowadays) for the counting problem because it gives a visual representation of how most people think to solve the simple counting problem and how to overcome the urge to write something procedural when they see a problem definition written by someone who may not understand the concept of a declarative language such as T-SQL.
Answer by David 1 ·
Learn, know and apply foundation knowledge about the science and practice of Data Management. In other words, a body of knowledge that is independent from knowing about the software you use.
Too many people today rely far too much on learning only by programming examples or from product-specific sources. A question I like to use in job interviews is to ask the candidate to recommend some books on data management, database design, programming or whatever. If they mention only books with "Microsoft", "Oracle", "SQL Server", etc in the title then I take that as a bad sign.
Answer by Ian Roke ·
I think the problem with SQL Server is that it is so accessible and easy to get started with. In it's most basic form it isn't much more than a glorified Access database but with fewer restrictions.
I have come across so many poorly designed databases in my time that I can only draw one conclusion from that and that will be companies have struggled to move with the times so people who were once editing screens on mainframe systems are now forced to migrate that to a powerful database server so they panic and use methods they are familiar with.
I find the best databases are designed by people who have read a book or two on SQL Server database design including Beginning Database Design: From Novice to Professional, Building a Data Warehouse: With Examples in SQL Server and my absolute favourite book of the moment Pro SQL Server 2008 Relational Database Design and Implementation from Apress.
If somebody comes to me, or I am forced to explain the concepts to somebody, I will always tell them to have a look at one or all three of those books and then come back to me with any questions. I never had anybody to call on so I had to learn myself yet strangely I found that the best method anyway.