Two days until SQL Saturday in Albany!

Hear ye, hear ye!  This Saturday — that’s two days from today — the Capital Area SQL Server User Group will be hosting SQL Saturday #513 at the University at Albany!  The event is free (there is a nominal fee for lunch), and anyone, whether you’re new to SQL Server or are a seasoned database veteran, is welcome to come!  (Just make sure you register!)

I’ll be giving a presentation about how to talk to non-technical people.  Come and check it out!

Want to know what SQL Saturday is about?  Check out my ‘blog post from Monday, where I talk about my path to becoming a SQL Saturday speaker!

Hope to see you there!

So you want to be a SQL Saturday speaker?

I attended my very first SQL Saturday in April, 2010, when I traveled down to New York City for SQL Saturday #39.  I’ve attended several more since then.  I’ve lost track of how many I’ve attended, but they’ve always been a fun experience while learning things about SQL Server, and a great way to spend a day.  Who needs to spend Saturday at the beach, the pool, or the ballpark, when you can spend it learning about technology?

It was on that New Y0rk City trip when I first met Dan Bowlin.  Together, along with a third person, Joe Barth, we co-founded CASSUG (Capital Area SQL Server User Group), the Albany-area SQL user group.  Since then, the group has grown considerably.  I am not sure how many people are affiliated with the group, but roughly twenty people regularly attend our monthly meetings, and I know that there are many more who are members of the group.  Our group has hosted its own SQL Saturdays — we will be hosting our third one this coming Saturday.

I’m not exactly sure when it was, but after attending a few more of these conferences, I knew that I wanted to contribute in some way, shape, or form.  The question was, how?  I am by no means an expert on SQL Server.  Most SQL Saturday attendees are people who know a lot more about SQL Server than I do.  So it was highly unlikely that I could develop a SQL Server-related topic that would be of interest to these database professionals.

As it turned out, SQL Saturday sessions talk about more than just SQL Server.  While SQL Server is definitely the dominant topic at these daylong conferences, SQL Saturday also includes several topic tracks, as well as resources, that may be of interest to developers, general technologists, job seekers, and non-database professionals.

It was at one of our local user group meetings where an idea occurred to me.  I’m not sure what sparked the idea, but I realized that there was a need for technologists to communicate their ideas and knowledge to people who didn’t understand technology — and I had enough experience doing exactly that to be able to lead such a session.  I started jotting down some ideas.  By the end of the meeting, I had enough material to create my own presentation.

When our user group adjourned for the evening, I ran my idea past some of my friends and colleagues.  I asked them whether or not they thought my idea would make a viable SQL Saturday presentation topic.  Every single one of them, to a person, answered yes.  All of them told me that they thought it would make an excellent presentation topic.

So, I worked on my PowerPoint presentation slides and submitted my presentation to our next SQL Saturday.  I figured that my hometown conference would be a good place to start.  I submitted my presentation under the professional/personal development track.  In order to prepare, I asked if I could present it at one of our user group meetings (which I did).  It went very well, and I received some very positive feedback.  It ended up being a good warm-up for my SQL Saturday presentation, which also went very well.  Since then, I’ve made some tweaks which have resulted in a better presentation.

That was just a little over a year ago.  This coming Saturday, I’ll be speaking at my fifth SQL Saturday, having spoken at other events around the Northeast United States.  And I intend to do plenty more.

I wrote earlier about how user groups can be beneficial to your career and even your social life.  If you’re interested in becoming a presenter, a user group is a good place to start.

So if you have an idea you want to present and some expertise you want to share, put a presentation together, try it out, and submit it.  You can never tell where it might lead.

The only person whom you can change

“Q: How many psychiatrists does it take to change a light bulb?”
“A: Only one, but it really has to want to change.”

“If you choose not to decide, you still have made a choice…”
— Rush, “Freewill”

“Minds are like parachutes.  They only work when they’re open.”
— Thomas Dewar

The other day, I was watching the Tour de France on the TV with a couple of friends, when another guy walked in.  He started going off, unprompted, about how “bicycling isn’t a real sport,” “I know because I worked with bicyclists,” and so on.  The way he was talking, it was blatantly obvious, even to me who doesn’t know as much about cycling (as a sport) as my friends did, that he had no idea what he was talking about.  This person was aggravating — to the point where one of my friends finally said to him, “I’m done talking to you.”

Someone once said that you can’t outtalk someone who knows what he’s talking about.  Unfortunately, you also can’t outtalk someone who has an opinion and is unwilling to change it.  It is for exactly this reason why I refuse to talk about politics with anyone.  No matter what facts you tell that person, you’ll never be able to change him or her.  Any attempt to do so only gets you frustrated and angry.

There is only one person over whom you have 100% control: you.

You have the power to influence change on others.  You can’t completely change them.  We all have our own thoughts and opinions that we express to others.  But as much as we’d like for people to see the world the way we see it, it’s ultimately up to them to make up their own minds and act upon their own thoughts.

I’m thinking about the movie Coming To America, where Eddie Murphy’s character is talking to his newly appointed bride-to-be.  “Jump on one leg.  Bark like a dog.”  And she does — without any question.  This scene brings up two thoughts.  First,  what he couldn’t do was convince her to be her own person.  He doesn’t have that power.  Nobody does, except for that person.  Second, would you really want a friend, spouse, or significant other who (as much as we like to joke about it) is completely obedient to every single word you way?  I sure don’t.  My wife is one of the most strong-willed individuals I know (note: individual is a key word).  And I wouldn’t want her any other way.  Sure, we fight on occasion; all spouses, couples, and even friends do.  There is no relationship that is 100% perfect.  There shouldn’t be.  Every person is their own individual.  It’s up to that person as to how to change.

I previously wrote about how change is inevitable.  It is up to us to determine how that change happens — and how we should handle it.  We can always try to influence change — and we should.  But influencing change is not the same as implementing change.  People will always try to influence you with their opinions.  What you do with those opinions is up to you.

Support your local user group

I’m involved with a number of local groups.  I participate regularly with my local SQL Server user group and my local Albany UX group.  I occasionally attend events held by my local college alumni group.  And I hold a leadership position within the local community symphonic band with which I play.  Additionally, there are several other local groups with which I would like to be involved; only lack of time keeps me from getting involved with more of them.

Why is it important to get involved with local user groups?  There are many good reasons.

  1. It’s a free resource for learning.  Both my SQL and UX groups regularly include a presentation about some topic at their meetings.  These presentations provide me with an opportunity to learn something new.
  2. It’s an opportunity for you to get involved and to give back to the community.  I am a musician in my spare time.  My involvement with music groups give me a chance to share my talents with the rest of the world.  Likewise, I’ve become a presenter with my SQL group (more on that in a minute).  Through my user group, I have an opportunity to share my knowledge and my thoughts.
  3. It’s an opportunity to grow.  Years ago, I started attending SQL Saturday, a series of SQL-centric technical conferences that are held at various locations.  I wanted to contribute to these conferences, but I wasn’t sure how.  I gave some presentations at my local SQL group.  I took those presentations, submitted them to SQL Saturday conferences, and was accepted.  I now regularly submit to and speak at SQL Saturdays around the Northeast United States.
  4. It’s a chance to network and make new friends.  I have made a significant number of friends through my involvement with user groups.  These are people with whom I feel comfortable getting together, having dinner, inviting to parties, playing games, going to ballgames, and so on.  From a professional perspective, it’s also a great opportunity to network.  It’s entirely possible that user group involvement could lead to professional opportunities and job leads.  You never know.  Speaking of professional opportunities and job leads…
  5. It looks good on a resume.  Getting involved with user groups demonstrates that you are genuinely interested in something.  That’s something that might appeal to potential employers.
  6. You become involved with something bigger than yourself.  Doesn’t it feel good to be part of a team?  When you’re involved with a user group, you can point to it and proudly say, “I’m a part of that!”
  7. It’s fun!  I’ve often told my wife that band practice “isn’t just a hobby; it’s therapy.”  I’ve often gone to rehearsal angry about something, and by the end of rehearsal, I’ll completely forget about what it is that upset me.  These user groups are something I enjoy, and it makes for great therapy.

These are some of the reasons.  Are there any others?  Feel free to add by commenting!

So go out there, find a user group that interests you, and get involved.  Chances are that it might lead to something.  You never know!

What’s in a (team) name?

Albany once had a CBA (Continental Basketball Association) team, the Albany Patroons. They were a competitive team that had some pretty good history behind it, having produced coaches such as Phil Jackson and Bill Musselman.  I told myself that I had to go catch a game sometime.

It never happened. The very next year (after I proclaimed that I had to go to a game), the team changed their name to the Capital Region Pontiacs, after the local Pontiac dealerships.  The name change turned me off completely. I said to myself, “no way am I supporting that team.” I never made it to a game.

I liked the “Albany Patroons” name.  Wikipedia defines “patroon” as “a landholder with manorial rights to large tracts of land in the 17th century Dutch colony of New Netherland in North America.”  It was reflective of the region’s Dutch heritage, so it was appropriate.  “Capital Region Pontiacs,” on the other hand, pandered to car dealerships.  What does that have to do with Albany?!?  The new name put me off so much that I vowed never to attend a game.  As it turned out, the team ended up moving (or folding — I don’t remember which).  As far as I was concerned, good riddance.

This isn’t the only time that a team turned me off because of a name change.  I stopped rooting for the NY/NJ MetroStars when they became Red Bull New York.  (I now consider myself a fan of NYCFC.)  There was once a hockey team, the Albany Choppers (named after the local Price Chopper chain of supermarkets).  I never felt any affiliation with them.  Albany also had an independent minor league baseball team, the Diamond Dogs, and a hockey team, the River Rats.  To me, those didn’t sound like names worthy of professional sports teams in the Capital Region; rather, they sounded like gimmicks.  Although I went to a few games, I was never enamored with either team simply because of the names.  To this day, I do not have — nor do I have any desire to own — a single piece of Diamond Dogs or River Rats gear.  Today, the Capital District is home to the Tri-City ValleyCats (a single-A New York-Penn League farm team for the Houston Astros) and the Albany Devils (an AHL team affiliated with the New Jersey Devils).  To me, those names sound professional, not gimmicky, and I am more prone to support them.  (As the baseball fan that I am, I do regularly attend ValleyCats games, and I do have a few ValleyCats shirts and baseball caps.)

I don’t know if any statistics exist as to how fans react to team nicknames — whether it’s by attendance, paraphernalia sales, or what have you.  I would be curious as to what they are.  If I had to venture a guess — and that’s all this is — I’d suspect that a team’s name could affect those stats.

So for those of you who are involved with sports marketing, take heed.  What’s in a name?  Possibly everything.

Scary Deployments

My friend Steve Jones ‘blogged this, and this spoke to me enough that I thought it was worth a re-blog.

Voice of the DBA

I was listening to a web developer talk about some fundamental changes in a web platform. In this case, an older system was being replaced completely with a new one, and as one of the reasons, the developer showed some typos that had existed on the old site for years and hadn’t been fixed. The reason? This quote:

“Very few people understand how the entire system works that are still in the building … The thought of deploying [changes] brought people to tears.”

That can’t happen. Ever. We can’t be afraid to touch systems. When this happens we get paralyzed, and we don’t do good work. Or we’re not a good fit for a project. Or perhaps, we’ve got a bad attitude.

I’ve worked in a few companies where developers were afraid to touch a system. It’s amazing how quickly this attitude becomes contagious, even scaring management from considering change…

View original post 148 more words

Setting up a primary key

When I set up my baseball database, I noticed something in the SQL code used to build the tables.  In the CREATE TABLE T-SQL code that was supplied by the Sean Lahman’s baseball database, I saw code that looked like this:

CREATE TABLE [dbo].[Batting](
	[playerID] [varchar](50) NULL,
	[yearID] [varchar](50) NULL,
	[stint] [varchar](50) NULL,
	[teamID] [varchar](50) NULL,
	[lgID] [varchar](50) NULL,
	[G] [varchar](50) NULL,
	[AB] [varchar](50) NULL,
	[R] [varchar](50) NULL,
	[H] [varchar](50) NULL,
	[2B] [varchar](50) NULL,
	[3B] [varchar](50) NULL,
	[HR] [varchar](50) NULL,
	[RBI] [varchar](50) NULL,
	[SB] [varchar](50) NULL,
	[CS] [varchar](50) NULL,
	[BB] [varchar](50) NULL,
	[SO] [varchar](50) NULL,
	[IBB] [varchar](50) NULL,
	[HBP] [varchar](50) NULL,
	[SH] [varchar](50) NULL,
	[SF] [varchar](50) NULL,
	[GIDP] [varchar](50) NULL

Granted, there are a number of things about this code that strikes me, but one thing hit me immediately: all the columns are nullable.  That tells me that there are no primary keys defined.  I checked the other CREATE TABLE statements in the code as well; likewise, all of them had all their columns set to be nullable.

(There are some other things about the CREATE TABLE statements that struck me as well, but I’ll save those topics for another time.  For the purposes of this article, I’ll concentrate on primary keys.)

First, if you’re getting your feet wet with relational databases, it behooves the question…

What is a primary key?

First, I’ll start with a few links that help explain this:

primary key uniquely identifies a row of data.  To illustrate this, here’s an example.

Let’s say we have a person named John Smith taking night classes at State University.  In his database file (which, for the purposes of this example, has his name and address), he has the following information:

Name Address
John Smith 123 Main Street

Now, let’s say his son, John Smith Jr. (who lives at the same address) enrolls at State University (for the purposes of this example, let’s assume the database doesn’t allow suffixes like “Jr.”).  He is entered in the database.  So now, you have this.

Name Address
John Smith 123 Main Street
John Smith 123 Main Street

How do you know which record is which?  Answer: you don’t.  So, let’s add an ID column to our table.

ID Name Address
1 John Smith 123 Main Street
2 John Smith 123 Main Street

Now we know that we have two different records for two different people who are both named John Smith who live at 123 Main Street.

Granted, this is an overly-simplistic example, but the point is that the ID column — which is always unique and is never NULL — uniquely identifies each row.

So now that we know what a primary key is, let’s go back to our baseball database.

Creating primary keys for our baseball tables

We could create an ID column for our baseball tables, but I don’t want to do that here.  Instead, I want to make use of existing columns for our primary keys.  I’ll start with the MASTER table, which keeps track of ballplayers.

I ran a simple SELECT * FROM MASTER to get an idea of what’s in the table.  Running the query gave me this.


I noted a couple of things.  First, I see a column called “playerID” which could be unique and used as a primary key.  Second, I see that the table contains 18,846 rows.  So can I use “playerID” for my primary key?  Let’s find out.

I ran two query statements.  The first gives me a count of unique entries I have for “playerID.”  The second tells me if any NULL values for “playerID” exist.

Here’s what I got.


The first statement returns 18,846, which matches the number of rows in my earlier query, telling me that all values are unique.  The second statement doesn’t return anything, which tells me that there are no NULL values for “playerID.”  Therefore, the “playerID” column can be used as the primary key for this table.

So how do we make this column the primary key?  There are a couple of ways to do it in SQL Server.  For this, I decided to use T-SQL.

alter table dbo.Master
add constraint PK_playerID primary key clustered (playerID)

When I ran this, it returned an error.

Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'Master'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint or index. See previous errors.

Okay.  Why did this appear?  Keep in mind that a primary key cannot be null.  When our code created our tables, it made them all nullable.  So we need to add a NOT NULL constraint before we create the primary key.

alter table dbo.Master
alter column playerID varchar(50) not null

Running this statement sets the column to not allow NULL values.  Once I ran this, I re-ran the command to create the primary key, and it completed successfully.  So I now have a primary key on my Master table.

What if I don’t have a single unique column?

Let’s take a look at the Batting table.


So, what should we use for our primary key?  A batter can play multiple seasons, and can play for multiple teams in a single season.  Chances are that you won’t be able to make use of a single column.  However, we can make use of multiple columns for our primary key (this is called a “composite” primary key).

We find that the Batting table contains 101,332 rows, so we need a combination of columns that will give us 101,332 unique results.  I figured that playerID, yearID, and stint should give us those results.

I ran the following query.

select distinct playerID, yearID, stint from Batting

This returned 101,332 results.  Indeed, I seem to have identified my composite primary key.

My first task is to set these three columns to not accept NULL values (otherwise, we’d get the same error from earlier).

alter table dbo.Batting alter column playerID varchar(50) not null
alter table dbo.Batting alter column yearID varchar(50) not null
alter table dbo.Batting alter column stint varchar(50) not null

Once the NOT NULL constraint is in place, we can create our composite primary key.  Note that in my T-SQL statement below, I provide three columns (playerID, yearID, stint), not just one.

alter table dbo.Batting 
add constraint PK_playerID_yearID_stint 
primary key clustered (playerID, yearID, stint)

The query ran successfully.  We now have a composite primary key on the Batting table that uses three columns.

Hopefully, this gives you a good introduction to primary keys on database tables.  In upcoming articles, I’ll cover other aspects such as foreign keys, indexes, and data types as we continue to further refine and improve our baseball database.