question

Matt Whitfield avatar image
Matt Whitfield asked

What are the most important aspects of your modus operandi?

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?

t-sqldatabase-designlearning
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I don't actually start out teaching someone about databases... I start out by teaching them how to think. I posted an answer below to try to explain how to do that.
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered

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.

  • First and most importantly, think in sets. I tend to actually spend some time talking about set theory, but then I came from a mathematics background before I moved into a DBA role.
  • Second, SQL is more declarative than most other languages. Think about what you want accomplished, and tell the system that in the plainest terms possible. You should very rarely think about how something should work and focus on what the end results should be.
  • Finally, just like in any field, observe/read the writings from the masters. I tend to refer all new (and most experienced) SQL developers to the stuff written by Joe Celko and I am also a big fan of Jeff Moden's articles.

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).

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ian Roke avatar image
Ian Roke answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

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.

3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 Definately hard to stop them thinking in a procedural way but it is hard because that is how programmers are programmed to think.
1 Like 1 ·
+1 - definitely good info!
0 Likes 0 ·
Good answers, especially the part about questioning the requirement. There have been so many times that people did not realize fully what they were asking for and got something much better after we went back and forth a little on what they really needed.
0 Likes 0 ·
David 1 avatar image
David 1 answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I see where you are coming from, but I am not certain if asking them to recommend books is the best way to go. I frequently forget the title of books, especially technical books which tend to have boring names even when they have interesting contents. Also, right now there is a lot of information in non-book form. In fact, at the high end of theory, they are likely looking at articles in technical and/or theoretical journals more than books, and at the low end magazines and Internet sites have more information than most will ever need.
1 Like 1 ·
Steve Jones - Editor avatar image
Steve Jones - Editor answered

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.

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yeah, good point. Balance is something I'm always working towards. The right amount of control and the right amount of permissiveness. Great point.
0 Likes 0 ·
Definite +1 for zen balance
0 Likes 0 ·
CirqueDeSQLeil avatar image
CirqueDeSQLeil answered

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.

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 - I can't think of a case where keeping an eye on the big picture is not a good thing in computing.
0 Likes 0 ·
Agreed! It is always good practice to know the downstream effects when possible, and to notify everyone that will be affected when a change is made.
0 Likes 0 ·
Jeff Moden avatar image
Jeff Moden answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.