Louis Davidson
SQL Down Under Show 50 - Guest: Louis Davidson - Published: 15 Aug 2012
This show features SQL Server MVP Louis Davidson discussing database design and his latest book.
Details About Our Guest
Louis Davidson is SQL Server MVP and well noted in writing areas around SQL Server and also quite a bit with blogging. I noticed particularly around Simple Talk and he has an interesting series around what counts for a DBA.
Show Notes And Links
Show Transcript
Greg Low: Introducing Show 50 with guest Louis Davidson.
Welcome, our guest today is Louis Davidson. Louis is a previous guest on our show and we would like to welcome him back. So welcome Louis.
Louis Davidson: Thank you.
Greg Low: Louis is SQL Server MVP and well noted in writing areas around SQL Server and also quite a bit with blogging. I noticed particularly around Simple Talk and he has an interesting series around what counts for a DBA. What I will get you to do for people who haven’t listened to the earlier show because it was a while ago. So maybe just a quick description of how you come to be here and what you are currently doing.
Louis Davidson: I have been a SQL Server fanatic for I guess for like 17 years now I think. Maybe a little bit longer but I am not going to admit any longer than that. Back in the day I couldn’t find any good materials on database design and I got a job tech editing a book. A VB database mash up book and I was talking to the editor and I said ‘are there any books on database design?’ You guys want to have one and you should find somebody to write that. They said ‘Well why don’t you write that.’ I had never written more than a 20 page paper in my life. I remember the grades I use to get on my 20 page paper weren’t all that great. They were like maybe you should try it. About a year later I was finally finished with it. I said I will never ever do this again.
Greg Low: Yet you have continued to do so.
Louis Davidson: A couple years later they said when Rocks changed ownership, the new owners said will you revise it and I was like ‘no, no’. Then they said we will give you a little bit of money. I then said well ok.
Greg Low: I was going to say, if it is a book then it would be very little money.
Louis Davidson: Yes, it is amazing how little it is. I don’t want to brag about how little money I made but. You do it for the love. One of the things that is really cool is I reference my own book a lot because I put all the techniques that I used for writing triggers or building constraints for a given type or even normalization. I need to figure out something but I am trying to explain to somebody something. It is just what I wrote down everything I knew.
Greg Low: It is good too because I like the idea that it is targeted directly at SQL Server because one of the other things is there are books with this type of topic around. They tend to deal with a very lowest common denominator way of doing things. For example I remember reading book on database refactoring and thinking it is so excellent that somebody is finally producing really good content and around that. Then when I had a look at how they did everything, every single thing inside the book was done by triggers even if it was something that for example would just be a computed column. Then I thought, that is one of the problems with something that is not targeted at a specific product. While it is a way you could potentially do it, it is not something you would want to do. I do like the idea that you are targeting the product directly.
Louis Davidson: Yes we made a conscious decision that the first half of the book would be pretty much implementation independent. Kind of like you should do a design.
Greg Low: Yes.
Louis Davidson: Find out what the requirements are. You find out why you design a relational database the way you do. Learn the rules, learn the system and then you start applying it to whatever SQL Server does. Really whatever you implementation tool is. In my case it is SQL Server.
Greg Low: Yes. Tell us what the book is called, the current one.
Louis Davidson: It is called Pro SQL Server Relational Database Design and Implementation. Just pretty much what you could say in one breath.
Greg Low: Actually I found that is a pretty good series those books actually. There have been some interesting books in that series. I tend to be a SQL Server book junkie as well as being a product junkie and since I have had a kindle for the last few years although I am now using and iPad with a kindle app almost in preference in all time to the kindle. I do tend to get almost every SQL Server book that comes out. That is a series that I quite like.
Louis Davidson: It is nice having things on the Kindle and then you can search and you can like I need to learn how to do a trigger. You can go to the index. Indexes are never as good as you expect them to be. You can go in and search for the word index or create index. You can flip through it until you find one. Even better just go to your directory and start choosing Windows.
Greg Low: Yes, one of the things I really like as well is that I use to have a really good library but it was here and I was somewhere else. The difference is now I have got either the iPad or a pc and thing is that I have got the same books on both basically and they are always with me. This is a really good thing, particularly for books like I quite like Art Tennick his MDX cookbook type ones and the DAX cookbooks and so on. These sort of things where they are just like little recipes for how to do things and so when you are thinking about roughly how would I do this. That is exactly when you want those sorts of books there with you.
Louis Davidson: The web is great, there is always this discussion about whether books are necessary or the web is necessary. I like the book for teaching you something and teaching you a common set of techniques. I use the web more than books obviously because the first place you go to. Oh I forgot how to create an index, you go there and you type in create index in Bing or in Google or what you use and it comes up with a thousand different ways to do something. Sometimes it is done by somebody who has no idea what they are talking about.
Greg Low: Exactly.
Louis Davidson: Sometimes it is done by one of our MVP friends and sometimes by one of our future MVP friends. In a book you get a chance to go through the whole entire process, teaching people along.
Greg Low: If we look at, in the current line well I should start with what are the most common design mistakes you think people make?
Louis Davidson: Mostly just not designing. Not thinking.
Greg Low: At all?
Louis Davidson: One of my latest things I am trying to put words around is more or less design testing. Once you have a design you don’t implement it yet. You think about what the process is and you write out the process and you go through the steps of storing data mentally if you can. Keeping up with what is going through and going through all the different processes. People don’t take time well for a lot of people I know can read about it on the web when I am answering questions in the forums whenever I get the chance to get there. You don’t get the time to think about all the different possible meanings of what they are storing. I have got a presentation on patterns. One of things is that you can store a value in one table or another table or another table and that one question has the exact same answer. What you may have answered three different questions by where you store a piece of data and not thinking about what you are actually ending up with. Lead you to not having a solid enough answer to what it is you are implementing and you usually find that out whenever it is implemented. Users are using it and people writing queries and they are going, I don’t understand what this means and you go well either do I.
Greg Low: Do you think a lot of that though is that it is very rare that people seem to get to do Greenfield database applications? I mean I am sort of thinking, most of the time what I see is I see applications get thrown away and rebuilt and things like that but the data in an organization seems to live on and on and on. Beyond generations of applications and part of the issue there is that people never get to start with a clean slate and design something. Invariably most of the guys I know doing development work in around databases seem to spend most of their time modifying something that is already there and dealing with the mess that is already there.
Louis Davidson: Well yes, me too. That is one of the fun parts about writing and doing presentations is that you do get to do things because you don’t have a legacy holding you back. Even then you still need to think about what you are building as you are building it and what it means. I had this problem as well.
Greg Low: The reason I am asking that is I am wondering if rather than always where we talk about hey here is good design. Maybe one of the things that would be interesting is to spend much more time on the here are the techniques that allow you to go from something horrible towards something better. Do you know what I mean? Because I think in many cases, it is those techniques are actually quite critical. It is how do I do refactoring and retake control of something and slowly morph it towards a good solution rather than how do I design a really good solution. Mind you I suppose you need to have a target of where you are going anyway.
Louis Davidson: Oh yes and that is what I think I was trying to say. You really have to be able to read the data model that you have and know where you need to get to and build out a path. Right now I am building on a third party tool but it has an email 1, an email 2, and email 3 columns. We decided we needed to have a bit of information to say this email address is an active email address; this email address is a historic email address. This one is also an active email address, so we need some way of doing that. Obviously my design is let’s have an email table and nice and easy. You can have email 4 for free, right?
Greg Low: Yes.
Louis Davidson: But we couldn’t do that because the application is a little bit fragile in the way it builds itself. You have email address 1, status, email address 2, status, email address 3, status and everybody by this time. Email address 1, 2, 3 didn’t seem as silly when they were building it, it probably seemed pretty reasonable. Now when we have 6 columns, I guess 9 columns for representing email address all in the same table flattened out, it starts to get silly looking.
Greg Low: Yes.
Louis Davidson: People like yes we probably ought to do that the right way.
Greg Low: I think that is the thing I was getting at. If you sat those people down when they were initially doing the design, given the current requirements they would not have designed it that way but it ends up being that way because they keep morphing something that is already there and just adding that extra little bit and so on. I think this is where the real problem comes in. As I am going around doing some consulting you look at things. What I hear all the time is people who just apologizes for what is already there. You know full well, those same people would never design it like that if they were given a chance today.
Louis Davidson: They say that.
Greg Low: Ok, fair enough.
Louis Davidson: From somewhere right. I mean if you look at any kind of software, database just happens to be the things that are the most foundational of structures that are really hard to change because of the billions and billions of rows of data that is attached to them. Sometimes in that email situation, one of the things I want to do is implement that table right and copy the emails back into the other table and sort of provide a path. The legacy code means it will work one way but the future code should work another way. You may end up with duplication, trying to get there but if you don’t really have a complete road map of how to get to what is right then you have multiple copies of things that don’t work and don’t match. Nobody has time because somehow, somewhere, somebody told people that yes it use to take 6 years to right a piece of software but now we have this new thing, new techniques and we could do it in a weeks .
Greg Low: Yes, or seconds they just whip it up and there it is and looks almost finished.
Louis Davidson: I mean, I know it is easier now and we have better techniques but it doesn’t necessarily take days.
Greg Low: It is actually one of the challenges where you have good tools for doing say a proof of concept. Is trying to explain to people that when they see the outcome of that, is that is not the thing.
Louis Davidson: Yes. I mean I don’t know who came up with idea that iterative software building would take less time than building it all at once. I mean there is the problem with building it all at once wasn’t that it took longer or shouldn’t have been if people weren’t padding their hours or whatever golfing or what they needed to do. But the problem was that by the time you were finished you were like we don’t need that anymore.
Greg Low: I was about to say, if you look at the total time. I tend to agree there. The reason I like having lots of iterations of small things is exactly what you say. You can keep coming up with results, you can keep getting feedback and you can keep driving in the direction that somebody wants to go. I do like the quote, I don’t remember who said it but they said ‘any large successful software project use to be a small software project that was successful.’ That is thing, every time I see large waterfall approaches I always say to people, you know this never works, right? They always say, yes, yes, yes we understand that but and then they want to go and do that anyway. Invariably when then they do it, as you say, it does take much longer and in the end the thing that comes isn’t what people wanted.
I find the real problem with a lot of this is no matter how good somebody is at asking questions or drilling into what customers want they often can’t explain what it is they want. It is not until they can see it that they start to realize what they really want. The other thing is that I think, they use the wrong words. I will have people say sales but they actually mean profit, you know and so on. This is the sort of thing where you go, no matter how good a job you did at doing that in the end it is not what they want because they said something different.
Louis Davidson: One of our examples that happened to me was that we did a project for a chemical company and they wouldn’t sell product unless it was a certain quality. We built a system there originally would not sell unless it meets the quality. What they really meant was we usually don’t sell it that way.
Greg Low: Yes. I think that is an early, early design thing you learn is that when they are adamant of such things it is usually not true. You mentioned along the way, design tools. One of the questions as I was on twitter yesterday and asking people what I should ask you. SQLRockStar buddy Tom LaRock, I should ask you What design tools you like?
Louis Davidson: And yes he better not park in front of my car at SQLSaturday. Obviously T-SQL, nobody took me on my offer on bribery to say their tools. I use Erwin quite a bit and I use it because I had a friend who worked at Erwin 15 years ago. When it was owned by Logiworks and our company has used it for 15 years. I have been there for 15 years and that has been kind of the defacto standard.
Greg Low: Do you use that for the logical design or do you go further and have that spit out ddl statements?
Louis Davidson: I use it for the logical design to start with. I use something called a concept map sometimes. I have discovered a tool for. My wife working on a document at education and they were building this concept map. I was like man that looks a lot like the conceptual data model, that or paper or whiteboard to start with. I use Erwin and I know other tools do this. They are not paying me and I am not advocating them as the only tool.
I use it for implementation as well. Some of the things that a data model can give you is you have all this meta data and you install properties about the attribute. You can put in relationships that don’t actually necessarily exist in the actual model. I have some tools that can generate code based on properties of the table. If I have a column that I want to make domain out of the distinct values out of that column because the application wants it. I add a user defined property and stores it then I have a stored procedure that goes out and creates these stored procedures automatically. I can store the definition of columns and I can store the definition of the table and all that sort of thing. Starting with a modeling tool is much easier. I know people who design using tables and go straight to the tables.
I get kind of in love with the tables I create. I build them and put some data in there and they look really well. I was doing a design just this week and I was to the point and I was like ok I am going to create this database. The next thing I get an email from somebody who was questioning if we can do it the way they said we could do it. I was like I have to delete half the work I have done and it is very simple to delete a picture or erase and it is not that easy to get rid of code.
Greg Low: That is an interesting point where there is an awful lot of people who are not prepared to throw away their first design and that is an interesting thing that you mentioned. I know when doing high level language coding and so on and one of the things they say is that you need to always be prepared to just throw away something. Invariably what you build will be far better than the thing you started with but if you feel like in love with everything you build and you just want to keep changing it you will often end up with a pretty poor design.
Louis Davidson: We have just started using a source control tool pretty heavily for all of our code. We would have the databases and backups. One of the things I find is that I have really never gone back to previous editions of the code to see what would happen. I still find it kind of interesting because there have been times where I would write a bunch of code and lose it somehow. The database would get deleted, I would lose the backup, I would trust automated tools doing the backups and they weren’t. Invariably you rebuilt it and you find how much better it is the second time.
Greg Low: Exactly.
Louis Davidson: Because you have lost the mistakes and you have just kept the good parts. The second time you go through you don’t make the same mistakes. You remember.
Greg Low: I think also have a much clearer picture in your head of where you are going than you often do the first time you build it anyway. So you will build something cleaner.
Louis Davidson: That was one point I didn’t make a second ago when we were talking about design. While it is great to do iterative design, you still need to have knowledge of where you are headed. If you go back to the email, if you only ever wanted to have one email address ever for one person and that was the final goal in the world. The initial design would have been great. We all know that as time passes we know people are going to have more than one email address. More than one phone number, so there is a different sort of way to implement that in the future.
Like you started out saying databases don’t change as much as we would like them to. Because all that code attached to it is very hard to get rid of. Even when they do an entire wholesale UI recreation, they usually use the exact same structures and try to phase it in. No adding any kind of change, unless the database absolutely positively needs it the change.
Greg Low: Actually I have a theory as to one of the reasons I think databases don’t change so much as well is that I think in most places I go into. The people managing it have no visibility in the code that touches it. So they are always caught in a bind where the minute they go to change something they have no idea who is about to yell at them or what have they just broken. So they tend to be very conservative about ever changing anything. I do think that is just simply a lack of one layer of abstraction. More and more modern design, let’s call modern in quotes design techniques and say everything always just hits the tables and there is no concept of like a proc layer of view layer of something in there. I think the problem with that approach is that while it is straight forward and they see it as very simple is it leads to a situation in large organisations of complete stagnation where nobody can change anything because they can’t go off and run every single report and every single Excel spreadsheet and every integration services package and every little Access database to see what happens.
Louis Davidson: Absolutely, there is absolutely no question that the thing. One of the reasons I started this SimpleTalk blog about what counts for a DBA is I see the people that come to SQLSaturday and I see the people that are MVPs. The people who tweet and I see what makes them good. Then I see other people who just don’t do the kinds of things that they do. A lot of times on the forums they are just like just give me the answer, I want to have the exact answer. Fix it the way I want it fixed and don’t teach me some technique that would make easier.
Greg Low: Yes don’t tell me that the approach I am taking is actually wrong.
Louis Davidson: Yes and I always try to be kind and tell people you know you could solve it this way. This will get you by; next time you know this is a little better. I am not perfect and I am not the only answer, that’s really great about having so many people in this community of ours helping each other. I don’t understand and still don’t quite understand why we do it. We all work for different organisations and different companies and we are all in competition with each other and yet we are all out here helping each other write better SQL and write design databases and write code. It is a very strange phenomenon, all of it.
Greg Low: But good, but good. Listen one I do want to ask you about, is how you like to handle enumerations or enumerated data? Typically in a design model it is easy to see them as an entity and so on, but the problem is that if you end up having an entity there to hold like very single little list of things that could be a enumeration in a high level language. The performance of that in terms of join performance and so on gets atrocious. We don’t have any way of doing just like build in enumerations in SQL Server and I just sort of wonder how you decide where something becomes or table or not? Or is there any other way you typically like to try and implement those?
Louis Davidson: So I definitely don’t like the one table approach where there is one table for all your domains. I really hate that. I say have as many small tables as you possibly want.
Greg Low: Yes the table to rule them all. Yes you often see that, you see like a code table or something like that and I always go why can’t you have a separate table and they always go. Oh, like it’s a revelation. There isn’t a rule that says you can only have 15 tables or 20 tables.
Louis Davidson: No, that number is way out there.
Greg Low: Yes.
Louis Davidson: I always suggest you have at least some way to disambiguate what a value is and you get your database and there is a one in there. What status equals one? Well maybe we need to go back to the code that is not good.
Greg Low: And this is the thing, so do you want to have that knowledge only available in the app or do you want to have that somehow visible but do you embed it in a view or do you embed it in the table entries. How do you decide that?
Louis Davidson: I usually create tables for almost everything. I find any time I try to put it in a check constraint, it fails. Creating a view you are going to be an index to make that work well enough. We also say that if you want to have enumerations in your code that is fine too. Just make sure you have that in a table so it is validated somehow.
Greg Low: Yes one of the approaches I saw the other day that I wonder what you think about is they simply create a CLR data type and then it had properties for all the different values and they simply used a data type to hold that.
Louis Davidson: Am I supposed to eww to that, because that sounds really terrible.
Greg Low: Yes.
Louis Davidson: Hopefully I am not; it is nobody we know right.
Greg Low: No.
Louis Davidson: They won’t be at PASS this year, you didn’t like my. I don’t why people are afraid of foreign keys. I have done a lot of testing on foreign keys. Is it slower to have a foreign key? Yes. Is it slower enough that you would ever possibly notice this on any system that isn’t like Facebook? No. You can have 20, 30, 40 foreign keys on a table. If tables all fit on one page which is 8000 characters that is a pretty big domain then there is going to be one read.
Greg Low: Yes it is not going to be relevant. It is interesting the thing you raise, this is something I come across all the time with the lack of foreign keys and if I look at the reasons why people list is first up. The developers are lazy and they haven’t thought about the order in which they have to update data and they have run into problems with that. The easiest way was just getting rid of all the foreign keys. The second thing I hear all the time is they say they application does that and yet every time I go and check on a sizeable site they are wrong. Then I find data that doesn’t match and they say that’s right there was that bug. They always have a story but it is usually wrong somewhere along the way. The third one I hear is in relation to performance. The thing I am typically saying to them is have you actually ever tried it and the thing is a lot of them have a rule where they just go we just don’t do that because we are concerned about performance. What I try to get them to do is say look why don’t you have them up to the point where you can’t have them. In that case, my guess is it will only be 1 or 2 tables at best and even then I would rather have them there and disabled, rather than never have them in the first place.
Louis Davidson: Well I mean unless you have very large keys. You can fit a lot of data, a lot of rows into a 2 or 3 level B-tree index. Hundreds and hundreds of thousands of rows. If you have an index on a foreign key unless you are updating millions and millions of rows. Usually we are talking about OLTP databases you are not updating millions of rows at a time. You are updating 1 or 2 at a time. The things that most people are doing wrong generally far outweigh the things that they are doing right that would harm performance. Like updating a table by something other than an indexed column, certainly a unique index column is perfect by some non-new index column and locking the entire table for the update.
That is the kind of thing that people do and they do and then they go oh SQL Server is slow or our disks are too slow and we are going to need faster disks. Somebody who knows what they are doing and goes wow, just put an index here, here, here.
Greg Low: Yes, that is another really good point. Whenever I see arguments around foreign keys being problems, invariably it is an index issue rather than something else. Mind you I have a hunch myself that I think SQL Server brings that on itself. I have had a connect item up for many years saying I think by default SQL Server should just provide an index on foreign keys that are declared unless you use the I know what I am doing option. Or something because I think the default action of not having those things indexed tends to be way more tragic than the alternative downside.
Louis Davidson: They are either good for doing a search or good for doing a delete. So like you have a low cardinality parents that have maybe 5 rows and a billion rows in a table. It is not helping you in queries when you try and delete one of those rows it can either be 2 or 3 reads or 2or 3 billion reads, right.
Greg Low: Actually that is another classic, I hear all the time. It is a very common myth, where people say yes the more indexes you have the slower your updates and deletes will be. I think like really? Just try and delete a customer when you have a gigantic order table attached to it and you don’t have an appropriate index.
Louis Davidson: If people would learn, obviously there is so much to learn to get really, really great at this. If people would learn how indexes would work and then learn things about the DMVs which will tell you how many times indexes have been used and how much IO are being used. I have seen people correct ways of doing that. If I keep this query it will cost me this much, if I delete this query and this index will cost me this much based on how much it has been used. You know there is so much richness deep down in there. I know I had a conversation with a developer once and I understand their thinking. SQL Server just works. It is just an amazing data chunking kind of place. They are not worried because they have got enough hardware and it just works. I don’t need to worry about it. But then their company grows and they do need to worry about it and they think they know what they are doing when they get kind of lost.
Greg Low: Yes it is interesting. Actually that applies to all different parts of the product too. I find like for example in Analysis Services I am surprised by the number of times we get sort of called in for performance type issues and you find there a no aggregate aggregations that have been created at all. The fact that the thing was able to answer queries at all in the time it does is a miracle really and the idea you can apply aggregations and it might run quickly all the time. That seems kind of foreign to some of the people. When we go in, it is not usually they haven’t done the aggregations correctly; often it is they haven’t done them at all. I find exactly the same in transactional systems. I have lost count of the number of times I have gone in and said yes we have just got the app going and we were going to get to the indexing sometime. You go, right, ok and you sort of look at the problem and look interesting.
What is intriguing is how long people will put up with that sort of situation. I was at a place that does design of fiber glass products and they had a website where people could come in and design the objects on the fly and they were getting literally up to a minute and a half type page turnaround times in the their apps. You go whoa, when somebody is trying to use your website, I mean if they are sitting there for a minute and a half all the time like things are not good if every time they do something. Of course the only reason they get somebody involved is eventually they start being over 2 minutes. The site starts timing out but in the end the simply the lack of an index would have made those things be a quarter of a second. The thing is that they will never look at that; they will just keep putting up with it. Getting longer, longer and longer. It is just an amazing scenario.
Louis Davidson: Yes I am a big advocate that you don’t index too much during the development process because you will start guessing what people are really going to do. What the optimizer is going to do? I also don’t wait until it is in production and is running slowly to do the first performance tuning. There is performance testing on real size data on real size numbers of people attaching to the database. There is thousands of examples of websites that go up and they get a little bit bigger amount of traffic and all of sudden they fail and everybody is like oh that is terrible and oh no.
They associate that with the product they are working with that they are trying. It just looks bad. I know it is really hard to do performance tuning. It is no question. One of the things I will admit quickly is that database design and database implementation in T-SQL coding they are probably one of the easiest on the whole disciplines in my mind. With T-SQL it has been around for 20 or 30 years, it works the same way. They have added incremental things to it over time but it is not like it is a completely different language every time. The paradigm is largely the same.
The increased technology comes in the engine. The engine is like so much different than when we first started. Back then I know our SQL Server had a 16MB on OS2.
Greg Low: Yes indeed. In fact I remember trying to spin up a virtual machine of SQL Server 1.1 and the reason we couldn’t get it to boot is that we were giving it too much memory. Of course, a real machine with more than like 32MB of memory just wasn’t an issue at the time. Accidently I gave it 128MB of memory and of course it just would not boot at all because the operating system didn’t even understand that.
Louis Davidson: That was hard disks size that was. You have a 15MB database, we ran our entire organization on that and it was amazing to think back to that little machine sitting in that closet replacing the main frame where the tapes were as big as the machines.
Greg Low: The approach for doing a lot of that was very different though because if I look I showing age here but I did work a lot on large HP minis and things like that. We often have 50 or 60 users on a machine with 2MB memory. You think how could that possibly happen but the thing is the way the code was done was very different. For example when you compiled code it didn’t just become an exe and go into memory. What they then had was they had a segmenter that then took the exe and broke it into appropriate pieces and it didn’t just break it into sizable chunks. It broke it along; let’s keep loops within the same piece of code. It was an intelligent segmenter and those segments were what would end up in memory and so on. Like there were very, very different techniques that people were using at the time for doing that sort of work. It wasn’t just let’s building executable where every exe is umpteen megabytes in size and just throw a whole heap of them in memory. That just wasn’t the thinking.
Louis Davidson: That kind of talk was why I just got my degree in computer science when I gotten this job as a land administrator and I started doing. I was like programming is hard. All this stuff, I didn’t do very well in math. I have a minor in math and got Cs in every single math class. SQL is just a very wonderful programming language. I don’t know that the technique that I use to build. I think I wrote 3 or 400 triggers and 3 or 400 stored procedures on that system. Created or modified and did this by hand, we didn’t have tools yet to develop them.
It was pretty much the same techniques now but I have better tools.
Greg Low: Yes that is kind of how I feel. I find one of the things I think with the industry is that I am endlessly telling people that I think you need to be prepared that 80 or 90% of what you know today is useless in 4 years’ time but it is that other 10 or 20% that I think keeps you out of trouble. That is kind of a good thing.
Listen you mention the fact that a lot of this stuff hasn’t changed too much along the way. Another question we had yesterday from our buddy Buck Woody was saying what do you think is noticeable in 2012 and I suppose the allied question is why do you need to keep producing another version of the book?
Louis Davidson: That is a tough question isn’t it? As a relational engine lover of sorts, I don’t know how to put this nicely. It is always a little disappointing that the engine has been just incrementally changing over the years. Every release we don’t get like the new, it’s not like Silverlight. We can now do movies with this, we can still store data. I remember one version we got back in the good old days. We had constraints instead of having triggers. That was amazing.
Greg Low: Yes.
Louis Davidson: In 7.0 they changed the engine and pretty much SELECT * FROM Table works in every single version the same way. In the 2000 edition we get sequences which are cool. What I call my least popular presentation ever has been on sequences. I could talk 2 hours on the techniques and examples and showing how one is faster than the other but it is a nifty new feature that lets you have an auto generated value that is not tied to a table.
Filetable is a pretty cool. I have written a little about that.
Greg Low: Oh so when you said 2000 you are meaning 2012 where we have sequences.
Louis Davidson: Oh yes, I meant 2012.
Greg Low: So I think they are nice. One of the things I have seen that used for as well, is you could have a single sequence used across multiple tables. Do you tend to come across that much?
Louis Davidson: I have not actually thought of a good reason why you would want to do that other than a version. Like if you want to have database wide version that you update. I really haven’t seen a good call for that. I have an example of it because it is a compelling example. My favorite example, we have an account number generator that we built. It is a critical section using application lots and I built an example to replacing that with just a loop inside a stored procedure getting the next sequence till it reaches the next acceptable sequence number. We have rules where certain number patterns, we don’t want them to show up in our account numbers for people. These are 3 characters long and sometimes you will have to skip 1000 or 10000 rows and you know you would think it would be best to go stop add the number first to it and move along. In my testing I was able to just keep it in a tight loop. If the next value created along with a check digit doesn’t match your allowed patterns, get the next one, get the next one. Within an hour on my laptop I was able to create 10 million of these things. Which is a really large number if you are creating 10 million accounts in an hour in your company you are doing really well. That is like the population of New York every hour.
There is that kind of really cool thing. It is not covered by transactions, so if you roll back. It is not blocked by other people using the sequence so you can both be in different transactions and both being hitting the sequence table really, really fast. That is a neat feature and that is probably one of the biggest database design features they have added. All the coding stuff is really nice that thrown is excellent. That is getting a little bit better towards excellent error handling.
Greg Low: Yes I must admit that it is an area that I keep yearning for something much, much more though. I think I keep looking at the sort of constructs that I have in higher level languages and sort of wishing I could get that at a lower level. I think maybe what I am after is testability and things like that. This is something that does frustrate me in the product. I hate scenarios where I can write code but I can’t test it. For example I can write code to trap an error but I can’t throw the error and then you go right ok so how exactly does someone test that code.
Louis Davidson: Well it is a little bit better now with the re-throw that you get with 2012.
Greg Low: Yes but I can’t throw a system error for example, I can trap a system error but I can’t throw one. I can rethrow one.
Louis Davidson: Yes.
Greg Low: But I am not going to create a disk check sum error to find out what happens you know what I mean.
Louis Davidson: Yes there are still certain errors that can’t be trapped because they are just too bad.
Greg Low: I think the thing. My testability side of me says that any time I write code I should have a way of testing that code works and in a higher level language I mean I could just throw any error I like to see what happens.
Louis Davidson: Yes that is kind of like the difference between a declarative language and a procedural language. The procedural language you have control. You are writing every bit of the code and interacting with the operating system stuff. In the declarative language, it is like asking a question and saying, here get it done. There is just certain thing it can’t let you do.
Greg Low: But I look and go why can’t I just go throw 823, you know what I mean? Or throw some number, why exactly can’t I do that?
Louis Davidson: Yes there are definitely some things it should let you do.
Greg Low: Yes so I look at that and whenever I talk to them again, it is a very conservative thing. They say well there would be these errors turning up and coming to product support and we would have no idea where they came from. Well yes, maybe but the thing is if I look at RAISEERROR for example you have options. You can set the Status to a particular value that even though it might not 100% tell you that is where it came from it would give you a very, very big clue but that is likely where it came from or something like that. Where they can do a RAISEERROR of any error you like but if you do that with a system error this status is the one that is going to be applied not the one you pick or something like that. Anyway I think there are ways around that.
Louis Davidson: That is an interesting reason why they wouldn’t let you do that.
Greg Low: Oh no, yes that is one of the ones I got back. It is sort of interesting the conservative approach with some of those things. As I said, there are many of those sorts of things I look at where even with procs I like the idea of having some sort of code contract and being able to discover that with the meta data. It is another good example that I think is problematic. You shouldn’t have to read the code of a proc to know how to use it. You know what I mean. I think all of that sort of thing should be discoverable in the meta data.
Louis Davidson: Yes we use to write our stored procedures with a meta data parameter that would then spit out a bunch of meta data that the user could use. It was super tedious. I had to figure out and write down. They are getting better with that in this version, they did something with not set the format but it will give you the first output.
Greg Low: sp_describe_first_result_set is probably the one you are meaning and even that I find kind of strange the way they have implemented it because some of them they have done system stored procs. Some of them they have done as views but I can’t see why you wouldn’t just have one to one with both of them. Because to me, I would much rather them be in views because I am thinking of somebody writing tools or things like that. I want something I can query as oppose to having to execute a system stored procedure and catch the output.
Louis Davidson: I am not suggesting that it is perfect; I was just pointing it out to anybody just listening. They are getting a little bit better. I am sure other people would listen, that is kind of the idea.
Greg Low: I think, as I said I look at those sorts of constructs in high level languages and just wish a lot of those things were just there. Another example is things like I would like to know what exceptions the proc knows it might throw without me having to read the code of the proc.
Louis Davidson: Yes.
Greg Low: I mean if you are going to call this proc you should be prepared to handle the no such customer exception and the no such order exception and the whatever. I shouldn’t have to go and read the whole code in the proc to know that. I think that should come back in the meta data.
Louis Davidson: Maybe this is why it is better not to be a programmer with that procedural language. I mean I get excited when they add things like format. If I can write a stored procedure then I am happy.
Greg Low: What I do want to do in the limited time we do have available then still. Is in what counts for a DBA topics that you have had I would like to run through your thoughts on a few of those. I notice that the top one you had on your list was passion.
Louis Davidson: Yes.
Greg Low: So why?
Louis Davidson: When I came up with this idea, I was thinking what really makes people great. The first one was passion. When you do an interview with somebody and you ask them why do you want this job? You want to hear something better than I need money, I have kids and they are hungry and I want to feed them. Something a little bit less than I am super and you will be great if you have me on your team. I want somebody who says I love this stuff. I love databases, I love coding. I go to these things on the weekends for free. Maybe they don’t speak, maybe they just go to learn, maybe they write, maybe they read books on the side I want to know somebody really, really loves what they are doing. I have this theory on jobs and I think there are some garbage men out there. There are some sanitation engineers who are they happiest human beings on the earth. Everybody else around them is thinking man why is that guy doing that for. And they are thinking I am cleaning this part of the world, this is my job. I envied those kinds of people back when I originally didn’t have a job because I was a land administrator and this is really horrifying. I was climbing underneath people’s desks, telling them to reboot their machines, jiggling cables. I was like man I should find another kind of job. I got this opportunity to be in databases. Over the years I have just grown to love it and I see this community of people out here working and helping each other and it is just a wonderful thing. I want to see people really intense about the work they do.
It was kind of interesting; my dad was an incredible mechanic. But he never really loved being a mechanic. He didn’t work cars at home, when he came home he was like tell me something about cars and he was like no.
Greg Low: Actually do thing that is one of the big tests? Is the fact you would continue to do some of that stuff even if you don’t have to? The reason I ask that is that I have friend Mitch Denny who some people will know. Mitch works more in the development side but when he is talking to people, he would classify them into day time programmers and night time programmers or developers. He has sort of an interest in finding people who even though they will do that stuff during the day. It is not just a job for them. That there will be something that they will want to go home and dabble and do things and whatever.
I suppose there is a limit to how far you want to do that though because you could end up like a completely anti-social type of person. There is some balance there where there is evidence of a passion around it.
Louis Davidson: I sometimes I do wonder if being anti-social wouldn’t be perfect. Nothing else and all I did was work. But you know that is not really the case. I gained a lot of weight when I started too because I didn’t exercise. When I first wrote my first book, it took about a year of coming home. Six hours a night for 12 months through holidays and everything. Sitting at my mother in laws house writing and there is a level and a place where you really shouldn’t go. One of the things I made sure of was I always went to my daughters events, basketball, volleyball. Actually one of the reasons why I bought my first smart phone was so that I could continue working at half time. Then I was typing up ideas. There is always balance. You obviously need to reach a balance to where you are happy.
Greg Low: You also mentioned that the next one you say skill. So one of the things I am interested in your thoughts on is when you are hiring somebody. I often see people who won’t hire somebody that are very skilled because they think they will be bored in the job. Do you have a take on that either way? Would you prefer to somebody who wants to learn or somebody who knows how to do it?
Louis Davidson: I would be more scared of somebody who thinks they know everything. How do you put this? As you get older, you realize how stupid you are. I am getting older now I realize how stupid I am. Take it back to the book, the first time I wrote my book I thought I was smart. I looked around I found some things. I know how to design a database, I will write a book about it.
I had 8 technical reviewers back then that were 8 people that called me some of the names not in so many words but in thousands of comments. This is really dumb you wouldn’t do this. I get like consultants because I never really worked consultants except some really bad consultants and you learn. The more you grow the more you learn. There is nobody who has all the skills at developing and knows every technique and also knows that business that you are working with well enough that they should be bored.
Greg Low: Yes.
Louis Davidson: If they are bored, well that probably means that very good employees to start with. I am not even going to lie and say that I am the perfect employee. I think you just got to keep working at it. Things change constantly. SQL Server like I said hasn’t changed as much as some other technologies but it continues to change and continues to give you more DBA tasks.
The things I mentioned about 2012 I like, there are also a lot of DBA things that have changed. High Availability, BI changes, stuff that if you are bored of learning all this stuff. You are probably ready to move onto the CEO or something.
Greg Low: No indeed. Listen the last one I am going to ask you about is you had humility on the list. I thought that was a nice addition to the list. In fact the example you used is you said there is a group of players in American football that hope their names don’t get called out during a game.
Louis Davidson: Yes, this is the first one that I got really beaten up by some developers because I am a little unkind. Developers are little bit head strong. Managers are head strong. Packaged applications are largely messed right. You have to work with people in a way you treat them as equals even if you don’t feel that way, right. Ironically the next entry is ego and I am writing right now. It is kind of the counter to this. That while in my example there is another sports analogy of a basketball player. Everybody on the team an hit a free throw. Everybody on the team can hit a shot from anywhere but you call on the person who knows they can do it at the time that it is necessary. The last second of the game you want the guy who is really, really good and really, really deliver. So the balance between humble and working with people and honoring their opinions and also having this kind of knowledge that you know what you are doing is a tremendously hard balance for people.
Greg Low: Do you think part of that is in that case is simply the ability to hand pressure?
Louis Davidson: Yes, absolutely. You have certain people that are good under pressure. Certain people that are good as designers but really probably never coded a major system themselves and never written a query. I understand there are architects who never actually write code.
Greg Low: I think there is also a group of people who have wonderful ideas but yet if you put them in a meeting situation they will never be able to get those ideas across.
Louis Davidson: Yes there are also people in the meeting that have terrible ideas and excellent at getting their ideas across. That is kind of the balance right. The perfect person is humble, you want to work with them, you like their ideas. They share when they need to and then they have an ego that is there and ready to say you know I have been working at this I have worked on this kind of problem before. I know what I am doing. I am not going to tell you. Yes I know exactly how to fix this because I haven’t looked at the problem yet but I am ready to look at the problem because I am working at it. I am studying and ready. Put me in coach right.
Greg Low: Excellent. Listen the final thing we did have a question from Dave Dustin where he asked you about would you architect differently for the cloud but I notice that was a response about an area that you hadn’t been tackling yet. Do you suspect that is an area you are going to be?
Louis Davidson: I did actually create a SQL Azure account about 6 months ago and I tried it a couple of times. It looked a lot like SQL and I had to finish writing the book.
Greg Low: Other things, yes indeed. I think it is something I have spent more and more time on in recent times and it gets more and more interesting as time goes on. Yes I think it is an area we will no doubt spend further time on.
Where will people come across you in the upcoming months at all Louis? I presume the PASS summit.
Louis Davidson: I will definitely be at PASS doing a precon on database design. I would love to have a big crowd to do that. I have tried doing database design sessions in an hour. They always come out as a big long preaching session with no kind of application. Even though I am doing a presentation this week at Cleveland SQLSaturday event. It is a design session and I have to trim it down completely. Quite a bit because I have a lot of code in the session that I want to present. I will be doing 24 hours of PASS session on the characteristics of a great database. Kind of a fun session, just talking about what makes something good. What makes things bad? Then there is Disney World in December, they can buy me lunch.
Greg Low: There you go. Listen thank you so very much for your time today Louis and we will talk to you again soon.
Louis Davidson: Thank you very much.
Phone: 1300 SQL SQL (1300 775 775) l International +61 1300 775 775 l Fax: +61 3 8676-4913
Copyright 2017 by SQL Down Under | Terms Of Use | Privacy Statement