Open Live Writer

I’m writing this post from the Open Live Writer project software. I’ve used Live Writer for years, but it’s shown its age. Not the least of which is doens’t support OAuth2, so I haven’t been able to use it with my Blogger blogs.

That should change, but we’ll see. Scott Hanselman noted there’s an open source fork of the software. I downloaded it today, and it’s slightly different, but not much. Mostly fonts from what I see.

I may clone the project and see if I can fix things myself. There are some nagging items for me. However you should get the latest version and keep an eye on the project if you want a better blogging software tool.

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.

The May Blogger Challenge–Continuing On

Last month I participated in Ed Leighton-Dick’s April Challenge. My four posts were tagged the April Challenge, and this month I plan to continue, and hopefully get a few more people to up their blogging.

Building on April

To make the next step in starting to blog regularly, I’d like to see people in May continue with their weekly blogging, but do one more thing: write one additional post.

This additional post I’d like to be one of the T-SQL Tuesday topics. Pick one from the list of past choices and write a post. Feel free to title it with the T-SQL Tuesday number and post it. It really doesn’t matter when you wrote it. The whole idea is to just write.

Getting Started in May

If you didn’t participate, or you struggled to get 4 posts, get started in May. Write one post per week and publish it. You can tag it the #SQLNewBlogger or you can just send a note to myself or Ed. We’d like to know what you’re doing.

If you struggle to write, do one of these two things:

  • Make a note, send yourself an email, whatever, when you close a ticket or complete a section of code.
  • Use one of my topics

Either one of these things work. I’d prefer you do the first one and just make a note when you do something. I’m sure you do something at work. Maybe it’s check logs, maybe it’s solve a problem with an instance, maybe it’s write a report. Anytime you do something, make a note.

Then write about that note in a weekly session.

If you are still struggling for topics, here are a few that I’d like to see you tackle in May:

  • Where do you use a unique index?
  • Generate a script after adding a column in the SSMS table designer
  • Query for the list of recent full backups in msdb for a single database
  • Show how to join two tables.

Pick one and write. Set aside a 15 minute session a week to write, and work on a short, < 1 page, post.

Good luck, and hope to see you blogging in May.

April Blogging Challenge 2 – Primary Key in CREATE TABLE

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.

Designating a Primary Key at Design Time

In the first post on this subject, I noted that I often add a primary key after the fact, using ALTER TABLE. However I’ve been wanting to build the habit of adding the PK at design time, inline with the CREATE TABLE statement, so I decided to look up the syntax and start practicing.

The inline code adds a CONSTRAINT clause into the table definition. I can do this in two ways, the most common is after I’ve built all the columns, I add CONSTRAINT, a name (PKUsers) then the PRIMARY KEY keyword with the optional clustered/nonclustered designation, and finally the column(s) in parentheses

Here’s my new code:

CREATE TABLE Users ( MyID int IDENTITY(1, 1) , firstname varchar(250) , lastname varchar(250) , gender char(1) , postalcode varchar(12) , contactphone varchar(12) CONSTRAINT pkUsers PRIMARY KEY CLUSTERED (MyID) ); GO

The better way, IMHO, is to do this inline if you have one column. I know this isn’t consistent, but I can easily see the constraint this way.

CREATE TABLE Users ( MyID int IDENTITY(1, 1) CONSTRAINT pkUsers PRIMARY KEY CLUSTERED (MyID) , firstname varchar(250) , lastname varchar(250) , gender char(1) , postalcode varchar(12) , contactphone varchar(12) ); GO

This is more difficult to see if you have multiple columns, but you can do this:

USE sandbox; GO CREATE TABLE Users ( MyID int IDENTITY(1, 1) , firstname varchar(250) , lastname varchar(250) CONSTRAINT pkUsers PRIMARY KEY CLUSTERED (firstname, lastname) , gender char(1) , postalcode varchar(12) , contactphone varchar(12) ); GO DROP TABLE dbo.Users; GO

Simple and easy to find the PK now, it’s named correctly, and it’s easy to read.

References

A few places I used

Quick and Easy Blogging

This post occurred to me while I was writing my first post.  Actually, this occurred to me first, but I realized that I often do the ALTER method in post 1, so I wrote that first.

Changing to this post required using the same MSDN article, dropping the table, rewriting the DDL code, and testing it. About 5 minutes for this one because it was based on a previous post.

Look for the other posts in the April challenge.

New Blogger Challenge 1 – Adding a Primary Key

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.

Primary Keys

I firmly believe that every table should have a primary key. At least until you have a reason not to have one. If you have a reason, fine, but if you can’t explain it or convince me, then just add a primary key.

I have tended to build tables like this:

CREATE TABLE Users
(
  MyID int IDENTITY(1, 1)
, firstname varchar(250)
, lastname varchar(250)
, gender char(1)
, postalcode varchar(12)
, contactphone varchar(12)
);
GO
ALTER TABLE Users ADD PRIMARY KEY (MyID);

Lately I’ve not liked that as my primary key now has a name like [PK__Users__7131A74146D2BBC1]. I’d rather have a more organized database with a touch more effort.

The better way to add the key later is like this:

ALTER TABLE dbo.Users 
  ADD CONSTRAINT pkUsers PRIMARY KEY (MyID);

This way I can name the key, and I specifically note this is a constraint, and with the PRIMARY KEY option, it’s a unique constraint.

References

A few places I searched around to double check myself.

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.

The April Blogger Challenge

I’d encourage you to take Ed Leighton-Dick’s challenge to blog in April. Read his post, start writing, and put your post out there. Tweet about it, and be proud.

However, if you’ve never blogged, I have a modification for you. Publish privately. The important thing is to just start writing and communicating.

If you’re looking for help getting started, I’ve got a few posts for you:

Blogging is a great way to give potential employers some insight into who you are. My view is this can only help you find a better job that’s a good fit for you. If you work at it and to it well.

Due Diligence

I often talk with people about building their brands and finding a way to ensure they are a highly desirable employee. One of the ways that I think people can do this is with a technical blog about their career. Having a technical blog allows someone to show off their skills in a particular area. The blog doesn’t have to be ground breaking work or extremely innovative solutions to complex problems. While employers need those people, they also need people that do solid work every day on regular problems.

An interview isn’t a great way to find good employees. Many of us have had experience with either (or both) sides of the interview table and realize that interviews aren’t necessarily that helpful. If we bothered to track the impressions we make of candidates and compare that to the actual work they accomplish over the first year or two, I suspect we’d find that we have no evidence that were making great decisions. The success of employees seems to be a bit hit and miss.

A blog, however, provides the employer with a bit more confidence that a person can handle the job they are hired for. A blog takes time, and across months (or years), it can show quite a bit about a person’s knowledge and skills. It allows hiring managers, and co-workers that may interview a person, the ability to perform a bit more due diligence and investigation into someone’s skills than an interview provides. It’s much more of a representative look at a person than what they say or write on a resume.

I know that it isn’t a perfect solution. People plagiarize posts and copy from Books Online and more, but the Internet helps here. Search a few of their paragraphs and you might catch plagiarizers easily. After all, someone that wants to copy posts to avoid work, probably has a few other tricks in their bag to avoid doing other work for you.

Think about starting a blog today and giving potential employers a way to learn more about you.

Podcast: http://traffic.libsyn.com/voiceofthedba/duediligence_57_v1062.mp3