Voice of the DBA

Category: Editorial

  • Who Likes NULL?

    The title says it all: who Likes NULL values in their tables?

    I have tended to allow NULLs in quite a few places in my design, often because I view the world as messy and incomplete. I also find that applications are faulty, and might not validate data, might not run long enough without a crash to let a user insert a lot of data. The application might mangle data, or just might not have been updated to support a new column of data. I’ve found that there are times where I accept the messy real world and use NULL to represent unknown values.

    Dr. Low notes this as well in a recent post. His view is similar to mine in that he uses NULL values when we don’t know the actual data. This is preferable to some magic value that has to be coded in every application using the database. There are too many chances of mistakes, and definitely the possibility of leakage for these magic values.

    As we use more and reporting and aggregation tools, users may inadvertently see strange values exposed. Many of these tools wouldn’t be coded to translate magic values to some agreed upon value, which results in confusion and distraction for clients. The data in our systems becomes used in new and different ways as we start to connect new applications to existing databases. We may also use ETL processes to move information among systems, often to data warehouse or OLAP data stores. Often there are proof of concept prototypes built with self-service tools, such as Power BI, and the logic that was originally coded to translate magic values is lost.

    That doesn’t mean that every field should allow NULLs, but that we should consider them in places where the data is useful, but not necessarily mandated or captured in every transactions. If we have valid defaults, use them, but if not, don’t be afraid of NULL. Understand the meaning and implications of allowing NULLs and use them carefully.

    I’m curious about if you agree with me. Do you default to NULL values or do you avoid them at all costs? Do you use them judiciously? Give me the reasons why or why not, and if you have examples of where you allow NULLs, let us know.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.1MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • #SQLCareer

    Information Technology is a place that I think could be a very good career for many people. Our working conditions are good, the pay is above average, and we can continue to do this job for most of our lives. We have flexibility to work in many locations, and at different hours. There are downsides, as each of those advantages can work against us. We can be expected to work all hours, and in all places, including while on holidays. We don’t have the excuses of not being in an office, and with today’s small machines, we can be asked to carry our work with us wherever we go. How you handle those challenges and achieve balance in life is up to you, but I will say that it can be a burden to do so.

    When I first started to work in this business, I came from positions that required more physical labor and onsite work. I was pleasantly surprised at how much I preferred an office environment, especially as technology became more important and workplaces evolved into casual, even fun, places to spend my day. I’ve appreciated the growing flexibility as bandwidth has increased and remote work became first tolerated, and then accepted. In some cases, it’s even preferred. As my children have grown up, one of the great benefits for me has been the flexible schedule that allowed me to share many moments in their lives. The flipside is that I’ve also had to work many hours while they’re at home, at all hours of the day and night and many weekends.

    When I started working in this field, I had no idea what life would be like, and I bet that many people that are learning about computers are similarly naive. When I’ve spoken with students, or even adults seeking training for a new career, I find that the environment in education, not to mention the tools and techniques, are very different from that of most jobs. I’m regularly reminded of this while talking with my son, who is studying some computer science in university. He’s learning concepts and skills, but missing some of the very basic, practical items that I think academic training ignores.

    I think we can do better. I read Iris Classon’s post about her day, where she covers some of the tasks and flow of her workday. I’m looking forward to reading more about how her life as a developer goes, but I’d also like to put out a challenge to those in the SQL Server community. I think we can help others learn what it’s like to work in our field, either inspiring them to study harder and build more skills, or warn them of the dangers and downsides.

    If you have a blog, I’d like to ask you to write four posts. These could be four days in a row, or one day a week for a month. Once a month is probably too little, but if that’s the time you have, then do that. Let us know how your day goes, what tasks you undertake, the challenges, maybe even a little about the problems you solve. If you enjoy blogging, you’ll get ideas for even more posts. If you don’t, you’ll still build some communication skills. If you like your job, maybe you’ll get excited to share the things that are fun. If you don’t, then you might stop and think as you write about how you can make changes in life. You might even teach yourself something about your own career.

    Either way, you’ll learn something. I’ll ask that you tag these posts on your blog and any sharing you do as #SQLCareer. That way we help others get a sense of what it’s like to work in the Microsoft Data Platform space. If you want to write more, that’s fine, but set a reminder now, and schedule out a few posts about what your job is like.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.6MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Where’s the schema?

    Across the last few years, I’ve read many articles and listened to quite a few talks that discuss the advantages of NoSQL databases. I’ll admit that I’m often skeptical of the advantages of other datastores overcoming the disadvantages with a relational system, but I try to keep an open mind. I do appreciate that there are some benefits to using another data store in certain situations.

    One of the talks I heard recently discussed the fact that in many of these stores, we can add data in a “schemaless” fashion, and it’s stored in a flexible format that allows the developer to quickly capture the data they are using and retrieve it without requiring up front design work to build a particular format.

    That had me pondering the question of whether or not here really are schemaless data structures. If a developer  (or whatever SDK or framework they use) looks to persist come data, clearly there is a format of sorts, which means there is a schema. That schema might not be transferred or persisted in the data store, but there is some schema they expect, both on storage and retrieval. Whether this is a JSON, XML, some proprietary structure, or something else, there’s an known structure that the developer uses to work with the data.

    Is there really schema-less data? I tend to think no. All of the data we have contains some schema. That schema might vary from row to row, which is often what developers like when building applications. There is, however, a structure. The developer knows it, and must serialize and deserialize the data, or depend on some library like ADO.NET to do so. This often appears to a developer to be a lower barrier to entry. There’s less complexity and often no need to map the objecct-like structure of properties to some relational schema and make decisions on sizes.

    That’s not completely true, as the schema of the data still exists and must be persisted in the application. There is code that must handle the various values stored in some hierarchical fashion. If this changes over time, as values are added, the the application must deal with the missing values in older properties or arrays. If items are removed in the application, then would older sets of data just disappear? Perhaps, but the developer must make a decision, which may have implications for users of their application. This doesn’t even deal with the issues of aggregation and reporting, which might force other systems to implement the same schemas and business logic. Those rules and specifications don’t easily transfer from one application to another, especially when different teams or developers are involved.

    There’s always a schema, and the rules have to be implemented up front, or later on. Whether you use a RDBMS or a NoSQL store, you are going to be dealing with a schema. The question is do you want to deal with it in a central location or in every application? I lean towards the former, but you might prefer the latter. Neither is wrong, but you should be sure you understand all the advantages and disadvantages of your choice.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.1MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • DevOps is Really Helpful

    A few weeks ago I gave a presentation at the 24 Hours of Pass Summit Preview. During my session, a demo broke and I had to ignore it. Later I found the issue and blogged about it. What was neat for me is that I didn’t need to keep my code up or apps open, but I still found the issue quickly. I found it because I have a DevOps process that instruments and tracks everything.

    This happened to me a few years ago. I was at a SQL Saturday and demoing one of the Redgate tools. There was an error and I couldn’t move changes to a downstream database, which threw me off. That was the point of the talk. I decided to debug on stage (or behind the podium in this case), looking through the logs. Within a few minutes, I had found the issue and solved it. A real life DevOps story in action.

    The idea of DevOps is what most talented developers and DBAs do. They ensure that they don’t just make changes on a whim, and they don’t depend on SSMS to be kept open. If they find an error, they save it, or they ensure their process captures all relevant logging. I’ve built systems like this, but many of the modern tools we use in DevOps automatically do this. Build servers and release servers make it a point of capturing all logging, so when that PoSh or CLI system runs, the output is saved and available for solving problems. And the data is available for improving the process, which is the goal.

    At various times in my career, I haven’t followed a set process, I’ve “tried” things in SSMS, a configuration dialog, or a command window that haven’t worked. I’ve sometimes remembered to undo them, sometimes not. I’ve lost track of what I’ve run often, just because I was working in an ad hoc manner. That led me to learn to do better, which led me to embrace and follow many of the principles and ideas that people call DevOps today. These are things I learned to do those things 15 years ago.

    Lots of DevOps is about using automation and tools, and those really do help. Having a pipeline for software changes, and one for configuration changes, reduces the chance of mistakes and problems. It also lets you instrument and track everything that happens. However, these are the easy things. These are the things that really talented professionals already do in their daily work.

    The hard part of building software is building a culture that learns to work together instead of separately, or worse, adversarially. It is truly hard to work together and concern yourself with the customer first, and your job last. Or put the QA person, the DBA, the developer, or someone else ahead of what makes your day easier. It’s also hard for management to build a framework where we don’t have incentives to put our own interests ahead of others. If we can do that, and build a DevOps style process, things work smoother, and our software will be better. Maybe more importantly, we’ll enjoy our time at work.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.7MB) podcast or subscribe to the feed at iTunes and Libsyn.