Posted in SQL Server

A Closer Look at SELECTs

I am putting together an Intro to SQL presentation for a local meetup. The goal is to go over the basics of the CRUD operation SQL statements (Create\ Insert, Read\Select, Update, Delete), especially for those who are new to SQL.

As I am putting this presentation together, I seem to have acquired a deeper appreciation of the SELECT statement. With the proper SELECT statements, I realized that you can accomplish 75% of what you need to do working with databases. OK, I’m totally making up that percentage but you can do a whole lot with a good SELECT statement.

I think there’s a lot to look at with this so I’ll talk about those in future posts. But for now, let’s start with this idea:

If you can write a SELECT statement, you can write an INSERT, UPDATE or DELETE statement.

Continue reading “A Closer Look at SELECTs”

Posted in SQL Server, T-SQL Tuesday

T-SQL Tuesday #88: The Daily DB WTF

It’s time for another T-SQL Tuesday!

This month is hosted by Kennie Pontoppidan (b|t). The topic is “the daily (database-related) WTF”. The challenge is to be inspired by IT horror stories from the dailywtf.com and add our own stories.

I’m one of the DBA types who works on the product\application development side of the house. It is my job to make sure that WTF database designs do not happen. However, I’m usually outnumbered and there’s only so much I can do. But if I can win these arguments 50.1% of the time, it still means I’m winning more than I’m losing. However it also means that I still lose quite a bit. And when you come up with a compromise, things can go weird.

I feel safe in sharing this story for a number of reasons.

  1. It was over a decade ago so I’m hoping the statutes of limitations have expired.
  2. It was not the outcome I wanted. In other words, this is part of the 49.9% of the times I lost.
  3. I am hopefully now relatively secure enough that I can admit that I didn’t help the situation.
  4. I found a way to turn this into a learning opportunity on how to avoid this in the future.

I’ll admit that I’m fairly fuzzy on some of the details of things now. But I remember the important pieces.

The database had a high number of transactions per second. The most frequently updated table was also one of the most central tables in the database. The transactions for this table were updates; inserts and deletes were rare. It had about 40 columns plus with a good percentage of those being nullable fields. I don’t remember the exact number but I think the table would have had about 30,000 records on average.

The programmers came to me and said we need to add a large number of columns to this table for one piece of functionality. It would more than double the total number of columns on the table. Oh, and all of the new columns would be NULL since we would only need to populate them if they were using that functionality and even then, not all of them would require data. The final result would be that 65-75% of the table would end up having nullable fields with the majority of those having NULL for the value.

I said what I think any sane DBA would say to this request: No.

BadIdea-giphy

The programmers said: But this is what we want to do.

Again, I said what I think any sane DBA would say to this request: No.

But I clarified: This table would be fairly wide. It’s used by almost everything in the database and it’s constantly being updated, which means locking and blocking are issues. For performance reasons, we shouldn’t be doubling the size with all of these NULL columns if they’re not going to be used by the majority of the records.

I even came up with an alternate solution:

GoodIdea-giphy

Let’s create a sub-type table or a vertical partition of the table to hold the columns needed by the new functionality so only the records that need to store this data will have it. It will be less rows which means the functionality that uses the fields can be more efficient since it’s looking at a smaller subset of data. Plus, there will be less conflicts for the main table that’s constantly being updated.

The programmers said: But we really want this on the same table….

Racking my brain for some sort of compromise, I came up with an idea. We would have been on SQL Server 2000 at the time. I had read something about table design which said because of the way data was written to and read from disk, the order of columns by size and nullability impacted performance. So, if all the NOT NULL fields were listed first order from smallest to largest data size followed by the NULL fields, also ordered by data size, it was faster to read because instead of having to jump back and forth to accommodate different data lengths and missing data along the way, it created a little more order to speed things along. Or something like that.

As an aside, I don’t know if how data is read from disk on this level is something we even worry about these days with the newer disk technologies. But this theory is why I am still in the habit of putting primary key and alternate key fields at the top of the list of columns on a table. (Anyone else remember this or is this one of those obscure things I incorrectly picked up somewhere?)

The logic seemed sound at the time. And it appealed to the programmers since it met their goal of one table. I was still pretty skeptical of this solution. So, I was asked to test the different solutions and figure out which option had the best performance.

The good news: One of my solutions proved to have the better set of performance results.

The bad news: It was the one where everything was on the same table but reordered based on nullability and size.

Picard facepalm

I thought it was the wrong solution, even though I suggested it. Forget the past tense verb – I still think it was the wrong solution. But it was the only one I was allowed to go with. So it’s what we implemented – our most used and updated table ended up being one of the widest with the majority of fields being NULL. But it was nicely ordered.

*sigh*

Soon after this, we were hiring a database architect. One of my interview questions for the candidates was: “The programmers want to add 50 nullable columns to the table with the most reads and writes in the database. What do you do?”

And now, I’m off to find my dunce cap and sit in the corner for this one. Please be kind in your responses …

 

Posted in SQL Server

A Woman in SQL, 2017

It’s International Women’s Day 2017! One of the people who tweets for @NESQLServer, Olga Demidova, had the idea to blog and tweet to celebrate today by talking about achievements and challenges. I thought I knew what I wanted to talk about until I started writing.

My first draft of this focused on one of worst experiences I’ve had as a DBA. To be fair, I’ve been pretty lucky in my career where the reason that particular story stood out was because it was not the norm. Of course, I have a story or two to add. (“I thought your division just hired someone for IT. Is he on vacation?”, “She’s just overly emotional and I don’t know how to handle it.”) I do have to step back once in a while and wonder if some of the issues that I faced were because I was the female DBA up against a team of (mostly) male programmers or was it just the old-fashioned conflict of being the one DBA against a team of programmers.

But it’s the 3 words included in the image at the top that made me rethink what I wanted to write about.

Encourage: 

The one thing I have noticed about the SQL Server community is how supportive it is for women. When someone shared the sexist comment that she received as feedback from a session, men wrote and shared posts about how this does not represent their community and how it’s not welcome. When someone belittled professional development sessions about identifying gender bias and putting together PowerPoint presentations (led by a teenage girl), the community rallied to support those two sessions. The WIT luncheon at PASS Summit had a waiting list because so many women and men wanted to attend.

But even with this, we still have a long way to go. The above examples still occur. I recently saw two tweets in the past couple of days that did some analysis on the numbers of female Microsoft MVPs. (Here’s one of the tweets – it has links and credits for the authors. I’ll update with the other if I find it again.) The numbers were broken out in different ways but the conclusions were still the same. Other members in the community can quote surveys and studies about women in business and in tech but the results still show there’s work to be done.

Our goal should be to make sure that everyone in this community is welcoming, supporting and encouraging of all of its members so we are as diverse and as strong as we can possibly be.

Energize: 

On my best days, I’ve been described as being passionate about my job and what I do. This has been both my strength and weakness.

Part of the reason I started to this blog was to help myself work on areas that I don’t always get a chance to work on. Plus, it helps me get involved in the community. And by getting involved and finding these new challenges for myself, it helps breathe new life and energy into what I’m doing and what my career path can look like down the road. (Or at least this is what I tell myself when I find myself struggling with an idea.) But it’s also about reminding myself that it’s OK that I’m not expert or perfect but it’s just about learning how to be better.

I think this is something we all struggle with. But we should all find what we’re passionate about and find a way to go after it.

Empower: 

Some people have inspiration boards where you put up images that help you figure out what your dreams are. When you look at it, you’re inspired by the images to go and achieve those goals for yourself. I don’t have an inspiration board, but I have an image to share.

My go-to avatar for various instant messenger programs over the years has been a PowerPuff girl. For those who don’t know, the PowerPuff Girls is a cartoon about three kindergarten girls who are made up of sugar and spice and everything nice and Chemical X, which gives them superpowers, and they save the world in time for bed. I usually went with Buttercup, not just because she’s the brunette like me but because she’s the bad-ass of the trio. Let’s face it – I’m not really bad-ass, but we’re talking aspirations here. They recently did a reboot of the cartoon and introduced a website to make yourself a PowerPuff girl. Here’s what I came up with:

PP wallpaper
Deb the PowerPuff DBA

So what is it about being a PowerPuff girl that it’s my go-to? You are undeniable and unapologetically female. Plus, you have that Chemical X which gives you a super power. What’s that super power? Maybe it’s just the thing that makes you you. Altogether, it turns you into a bad-ass girl who saves the world in time for bed or whatever you have in your life that makes you strive for that work-life balance. Because in the end, you need that balance to recharge if you’re going to be a bad-ass girl again tomorrow.

This may not work for you but it does for me. But more importantly, you should find whatever you need to inspire you.

Encourage, Energize and Empower are pretty powerful words. Hopefully we all can come behind these words and find a way to embrace them. And I will do my best to help you achieve those goals. Just let me know.

Thanks to Olga for the suggestion to write about this topic. And while I’m on that subject, I want to say “Thank You” to all of those in the community who encourage, energize, and empower me – whether you know you have that influence on me or not.

 

Posted in SQL Server

Reviewing My Go-To SQL, Part 1

Recently, I was doing some performance testing and came to a realization – I think I’ve been using the same code for this since my SQL Server 6.5\7.0 days. Yikes!

Usually I am trying to look at two or more ways of writing something and figure out which one is faster. If I’m trying to improve bad code, I can keep the starting point and incrementally see if what I’m doing is improving it.

Continue reading “Reviewing My Go-To SQL, Part 1”

Posted in SQL Server, The Survey Project

All in the Family

allinthefamilyI wanted to spend some time looking at questions about people to see if we can fill out some of the Person table, or at least confirm it makes sense. So I’m going to start skipping around the survey looking at different questions to try to find more information for people.

The next group of questions about people have to do with parents and other relatives.

Continue reading “All in the Family”

Posted in SQL Server

My Late Assignment

I feel like I already broke my New Year Resolution. I wasn’t able to complete the January T-SQL Tuesday challenge on time. I’m hanging my head in shame.

dog-homework-excuse

But I found the subject really interesting and 2 other people blogged about the same exact topic I wanted to talk about so I think it’s still worthwhile to follow through on the challenge, even if I’m late to the party.

This month, Brent Ozar (b|t) challenged everyone to go to Microsoft Connect, find an item and write about it.

Continue reading “My Late Assignment”