Voice of the DBA

Tag: blogging

  • Rebooting SQLNewBlogger in November

    I ran into Ed Leighton-Dick last week at the PASS Summit and we were chatting about the SQLNewBlogger challenge he started in April. I enjoyed it and while a number of people started, it seemed like most waned away. I tried to keep the challenge going with my own posts, adding notes to the posts on how much time I spent to give you confidence you could successfully blog yourself.

    Ed wanted to kick off the challenge again, and he did earlier this week. There’s a November SQLNewBlogger challenge, asking you to get four posts done this month. One is due each Tuesday, which means the first was four days ago, on Nov 4.

    Don’t let this discourage you if you didn’t write a post.

    Start now. Write a post today. Look through the ones I’ve written. Heck, write about the same topic I wrote about. Have you written about defining a FK at table create time? If not, write that post now, reference mine, and show how to do it. Give us a sentence or two on why you would do this.

    Write about using the DAC. Write about PowerBI. that one’s still on my list. Ed has ideas as well, and feel free to use one.

    The idea is to just write. Take some time this weekend, reflect on something you did this week, jot some notes, and write a short piece to showcase your knowledge.

    This is your chance to showcase your knowledge for your next employer, or even your review at the end of the year.

    I know I’ve been inspired, knocking out 5 posts this week, which I’ve scheduled each Thursday. I’ll be late to Ed’s challenge, but that’s OK. I’m continuing to write short pieces about my knowledge. In fact, a few changes to a PoSh script today gave me two posts, looking to specific skills I worked on.

    You can do the same. I look forward to you blogging, and seeing your posts appear in the #SQLNewBlogger hashtag on Twitter.

  • No Live Blogging

    The PASS Summit opens today with the first keynote. This is one of the few times that I’ve declined the blogging table at PASS. The table has grown, it’s become a bit loud at times, and the pressure to take notes and push them out distracts me from listening and thinking.

    As  a result, I’ll be in trying to think about what’s happening and what it might mean for our industry. Having given keynotes and developed them, I know it’s a marketing show, and a bit of entertainment. However it also can generate some excitement, and certainly the ideas shown can influence managers and executives, which can free up money for us to play with.

    I’m a bit excited to see what’s coming, and what Microsoft might announce. If you’re not at the Summit, jump on the PassTV channel and watch live.

  • Reframing to Overcome Filtered Index Limitations

    I’m continuing on with the Blogger’s challenge in this post.

    Turning the Problem Around

    In the last post, I wrote about limitations in filtered indexes. I proposed that my table had lots of data with NULL or blank spaces in the gender column, and I wanted to avoid indexing those rows. I tried this code

    CREATE INDEX Users_Gender_Filtered2
 ON Users (gender)
  WHERE (gender IS NOT NULL OR gender = ' ');

    However, that failed. I can’t use two sets of criteria in the filtered index. But I can use one, so I need to re-frame the problem.

    If I look at the data, I have four choices: ‘M’, ‘F’, NULL, ‘ ‘. I can group two of those choices together, looking for a positive (matching) set of data rather than a negative (non-matching set. The easy way to do that is with an IN clause.

    CREATE INDEX Users_Gender_Filtered2
 ON Users (gender)
  WHERE gender IN ('M', 'F');

    This works, and I’ve got a filtered index. In the real world, I’d actually drop the first index (gender is not null), and only go with this one.

    References

    A few places I used to research this post.

    Quick and Easy Blogging

    This post occurred to me as I was writing the other post. I almost added a note on turning the index around, but realized this is a separate topic, and it makes a nice, second post. This post required < 10 minutes.

    This post continues the April Blogger Challenge is from Ed Leighton-Dick, aimed at new bloggers, but anyone is welcome. I’m trying to motivate and cheer people on.

  • April Blogger Challenge 4–Filtered Index Limitations

    The April Blogger Challenge is from Ed Leighton-Dick and aimed at new bloggers, but anyone is welcome. I’m trying to motivate and cheer people on.

    Filtered Index Limits

    I ran across a limitation recently with filtered indexes and was surprised. Apparently, you can’t make complex filters in your definitions.

    In the last post, I wrote about creating a filtered index on gender, looking for non-null columns in a table. That code looked like this:

    CREATE INDEX Users_Gender_Filtered
  ON Users (gender)
  WHERE gender IS NOT NULL;

    However, what if I find that I have lots of NULL values, but also lots of spaces, ‘ ‘, in my table because this is an optional field, and the data entry code changed at some point. Neither of these rows actually helps me in finding the rows with an ‘M’ or a ‘F’.

    I tried creating an index like this:

    CREATE INDEX Users_Gender_Filtered2
 ON Users (gender)
  WHERE gender IS NOT NULL OR gender = ' ';

    That didn’t work, so I tried parenthesis.

    CREATE INDEX Users_Gender_Filtered2
 ON Users (gender)
  WHERE (gender IS NOT NULL OR gender = ' ');

    That didn’t work either. No matter how I moved parens around, they didn’t allow a complex (if two criteria are “complex”) filtered index.

    This is a limitation of a filtered index right now. I’m not sure if it will be changed, though I would like it to be. There are other limitations, so read the docs carefully, and think about what might work well in your environment.

    References

    A few places I used to research this post.

    Quick and Easy Blogging

    This post occurred to me while I was writing some code. I mocked up a table in about 2 minutes, and then ran a quick search on the Internet. Reading a few links was about 10 minutes and then testing the code (including dropping the table and recreating it a few times) was less than 5 minutes. All told, I solidified some knowledge and completed this in about 20 minutes. I also have drafts and ideas from this post for 2 other posts that cover this same topic in a similar way.

    Look for the other posts in the April challenge.