How do you want to be remembered?

Have you ever thought about your own obituary?  (I apologize for the morbid thought.)  Dying is something we’re all going to do someday.  When that day arrives, what kind of a legacy do you want to leave behind?

This week, I had the misfortune of attending two different wakes for two different people.  Interestingly, I did not know either person well; in one case, I was friends with the deceased’s sister, and in the other, the deceased and I had mutual friends.  In both cases, despite not knowing the deceased that well, I felt compelled to go.  Mainly, I went to support my friends in their time of grief.  However, both people had compelling life stories that made me wish that I had known them better in life.

I don’t remember the exact wording of the quote, nor do I remember where I read it, but I remember reading something to the effect of “the way you measure the success of your life is by the number of people who show up for your funeral.”  Okay, granted, after I pass on, I won’t know how many people will show up at mine, but I’d like to think that a large number will show up.

(Side note: my favorite rock band is Kansas.  I’ve told people that I want “Dust In The Wind” performed at my funeral.)

Honestly, I don’t know how I’d want to be remembered (or at least, outside of this article, I’ve never really stopped to think about it).  I suppose I’d like to be remembered as someone who was a good person, someone who cared (sometimes too much), someone who gave it a shot, and someone who gave his all in whatever he did.  (There’s probably more to it than that, but it’s not something I feel like writing now, and to be honest, you probably don’t want to read about it.  I’d rather do my thing and let others be the judge of how I did.)

When it comes down to it, how you live your life and how you treat others will likely be your legacy.  So make the best of it.  As someone once said, live every day like it’ll be your last — someday, you’re going to be right.

SQL Saturday #545, Pittsburgh

I got word yesterday that I will be speaking at SQL Saturday #545, Pittsburgh on October 1!  I will be giving my presentation, “Disaster Documents: the role of documentation in disaster recovery.”

Hope to see you there!

On data types

Previously, I talked about something I saw in the code that built the tables in my baseball database.  I wrote last time about primary keys.  In this article, I want to talk about data types.

Let’s revisit the code that builds the Batting table again.

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
) ON [PRIMARY]

Last time, I mentioned that every single column allowed NULL values (and I’d like to expand on that in a future article).  Today, I’d like to talk about the fact that every single column is defined as varchar(50).

For those of you who are getting your feet wet with SQL Server (or don’t know what I’m talking about), let’s take a moment to discuss…

What are data types, and why are they important?

Imagine that you’re presented with a math problem (don’t worry; we’ll keep it simple).  However, when you’re presented with the problem, it looks like this:

XII + IX

Want to give it a shot?  The truth is, there’s a reason why we don’t normally use Roman numerals in our society.  Unlike the Arabic numeral system that we use in everyday life, the Roman numeral system is inefficient and not very intuitive at a glance.  For us to use Roman numerals, we’d first need to convert them into a format that we can easily understand.

So, knowing that XII = 12 and IX = 9, our new math problem is 12 + 9.  We now know our answer is 21.  (And if we want to convert back to Roman, it’d be XXI.)

Data types operate along the same principle.  A number that’s stored as a data type of, let’s say, varchar(50) (for those of you who don’t yet know T-SQL, that’s “variable character of up to 50 characters”) needs to be converted to a numeric type of (for example) int (for “integer”) before it can be processed.

“Okay,” you might ask, “why is that such a big deal?  Just let the system do the conversion and let it do the work.”

Let’s go back to that Roman numeral example for a moment.  In order for you to understand what the Roman numerals were, your brain had to do some work to perform the conversion.  Imagine that you had to process dozens, even hundreds, of those problems.  That’s a lot of Roman numerals that you have to process.  That makes for a lot of work.

Likewise, imagine that a computer system has to convert millions of data types.  That’s a lot of extra work that your computer has to do.  That can make for a very inefficient system.  One of the big issues that a DBA or a developer often has to deal with is a slow system.  Using proper data types can often help with trying to fix a slow system.

What kinds of data types are there?

In a word, many.  A number of web sites list data types for various systems, including MSDN and W3Schools.  Since I’m writing primarily about SQL Server, I’ll focus on SQL Server data types.  I won’t talk about all SQL Server data types (I’ll leave you to look at the links for that); rather, I’ll just discuss a few that are relevant to our conversation.

Let’s start first with the varchar data type, since that’s what’s defined in our tables.  Varchar is for variable character.  What that means is the data type accepts character strings up to a length that you specify.  A data type of varchar(50) means that the field accepts strings up to 50 characters.

There is also a char data type.  Like the varchar type, you specify the number of characters allowed by the data column.  Unlike the varchar type, however, it is a fixed length string.  A column of type char(50) means that the data will always have 50 characters.  If data that is fewer than 50 characters is stored, it pads the rest with spaces.  This is important to consider, because those characters (even the space characters) take up space.  If you’re storing millions of rows of data, and you’re using char data types for data that takes up fewer than the number of characters you specify, that’s a lot of wasted space.

So, let’s revisit our Batting table.

BaseballBatting

When we built our primary key, we created a composite primary key from the playerID, yearID, and stint columns.  Can we improve upon these columns?

Let’s look at playerID.  Do we need to reserve fifty characters for this column?  To find out, I ran the following query.

select distinct playerID, len(playerID) from batting
order by len(playerID) desc

To translate this query into English*, this means “give me each distinct playerID, along with the length of the playerID, from the batting table, and give me the results in descending order of length.”

(*For those of you who don’t speak T-SQL, yes, I do intend to write an article about it.)

The results looked like this:

PlayerIDLength

So what does this mean?  It means the longest playerID is nine characters.  Could it be longer than nine?  Possibly.  Will it ever get up to fifty?  Unlikely.  So while the varchar(50) will definitely accommodate this, my thinking is that it’s overkill.  I’m going to shorten the column.  I’m going to cut it down to 25 from 50.

Now, let’s look at the yearID column.  I ran the same query as earlier, but this time, I replaced playerID with yearID.  Every column was four characters long.

SQL Server provides us with a number of date and time data types.  My first thought was to use one of these data types, but at that point, I stopped to think.  What, exactly, is the purpose of the yearID column?  It identifies a season.  That’s it.  Will I perform any calculations with it?  It’s possible, but unlikely.  Will it ever change from four characters/digits?  No.  I’m going to make this a fixed four-character field.  For that, we’ll use char(4).

Now, let’s look at stint.  What is this column?  It keeps track of a player’s stint with a team in a season.  So, let’s see how this works.

I went into the database and looked to see what stint values were in the Batting table.  There were five.  I looked up the player who had five stints, and found a man named Frank Huelsman, who played in the early 1900s.  I ran a query that gave me this.

FrankHuelsmanStint

In 1904, he had five stints with four different teams, having played for the White Sox (twice), Tigers, Browns, and Senators.

So, what data type should we use for stint?  Let’s think about this.  We could use a char(1), but I don’t think we should.  Is it possible that a player could have ten or more stints in a year?  Unlikely, but yes.  So we’d need at least two characters.  Let’s try char(2) — or should we?

I say no.  I say that we should use a tinyint data type for stint.  Why?

I say it for a couple of reasons.  First, there the matter of ordering.  Let’s order the numerals 1-10.  How would you expect them to be ordered if they were stored as characters?  If you say 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, you’d be wrong.  If it was defined as a character data type, the order would look like this: 1, 10, 2, 3, 4, 5, 6, 7, 8, 9.  When we’re talking about characters, 10 comes after 1.

Second, there’s a matter of memory.  tinyint uses only 1 byte, as opposed to 4 bytes for int. The memory used by char and varchar data types are dependent on their sizes (i.e. how much is allocated).  Also, tinyint accommodates values from 0 to 255, which is plenty for our purposes.

So, let’s start by redefining the data types for our composite primary key.  Because T-SQL in SQL Server won’t let us alter multiple columns at once, we need to run them as separate queries.  I wrote three statements like this:

alter table Batting
alter column playerID varchar(25)

alter table Batting
alter column yearID char(4)

alter table Batting
alter column stint tinyint

This should do the trick — or so I thought.  When I ran the queries, SSMS gave me error messages that looked like this:

Msg 5074, Level 16, State 1, Line 1
The object 'PK_playerID_yearID_stint' is dependent on column 'playerID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN playerID failed because one or more objects 
access this column.

What’s going on here?  Keep in mind what we’re trying to change: three columns that are part of a primary key.  So before we can alter these columns, let’s drop our primary key.

alter table Batting
drop constraint PK_playerID_yearID_stint

Now when we run our alter table statements, we should be set.

Or are we?  I tried to recreate the primary key (I decided to rename the primary key, while I was at it).

alter table Batting
add constraint PK_Batting primary key (playerID, yearID, stint)

Instead, it gave me the following error:

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

Now what’s going on?  Apparently, I’d forgotten to include the NOT NULL statement when I altered the columns.  So this time, I’ll rewrite the statements like this and rerun them.

alter table Batting
alter column playerID varchar(25) not null

alter table Batting
alter column yearID char(4) not null

alter table Batting
alter column stint tinyint not null

After running these statements (which ran successfully), I retried my statement that added the primary key.  This time, it worked.

So, our primary key columns are now altered.  Let’s look at the rest of our table.

Our first two columns are teamID (for “team ID”) and lgID (for “league ID”).  I checked the data in these two columns (I’ll save you the trouble of looking at the T-SQL I used).  I found that they are never null, and they are always 3 characters and 2 characters, respectively.  They are also not unique.  For our table, I’ll redefine them as char(3) and char(2).

Now let’s look at the rest of the table.  For the benefit of anyone who doesn’t understand baseball, let’s write out what these columns are.

G Games
AB At Bats
R Runs (Scored)
H Hits
2B Doubles
3B Triples
HR Home Runs
RBI Runs Batted In
SB Stolen Bases
CS Caught Stealing
BB Base On Balls (Walks)
SO Strikeouts
IBB Intentional Base On Balls (Walks)
HBP Hit By Pitch
SH Sacrifice Hits
SF Sacrifice Flies
GIDP Grounded Into Double Play

They’re all statistics, and they’re all integer values.  For now, we’ll make them all integer values.  Do we make them NOT NULL?  Not necessarily.  Why?  Because for some records, certain statistics didn’t exist during their time, so they might not exist.  Some of these numbers may very well be NULL.  So we won’t add the NULL constraint.

Here’s what I wrote for my T-SQL:

alter table Batting alter column [teamID] char(3) not null
alter table Batting alter column [lgID] char(2) not null
alter table Batting alter column [G] int
alter table Batting alter column [AB] int
alter table Batting alter column [R] int
alter table Batting alter column [H] int
alter table Batting alter column [2B] int
alter table Batting alter column [3B] int
alter table Batting alter column [HR] int
alter table Batting alter column [RBI] int
alter table Batting alter column [SB] int
alter table Batting alter column [CS] int
alter table Batting alter column [BB] int
alter table Batting alter column [SO] int
alter table Batting alter column [IBB] int
alter table Batting alter column [HBP] int
alter table Batting alter column [SH] int
alter table Batting alter column [SF] int
alter table Batting alter column [GIDP] int

The query, which took 20 seconds to run, ran successfully.

Note that I only dealt with a few data types in this article.  I didn’t talk about decimal data types or computed values.  (What if, for example, we wanted to keep track of batting averages?)  I’ll cover those in a later article.  Hopefully, at this point, you’ll have a better idea of what data types are and what they can do.

What music taught me about being a professional

They say learning music improves cognitive skills, and I definitely understand the correlation.  I’ve been playing music my entire life.  Reading a score is like a computer reading a program; there are notes (that correlate to instructions), dynamics (attributes), repeats and D.S./D.C. instructions (loops), and so on.  (If you want to take the programming analogy further, you could say that each movement in a suite is either a function or an object.)

However, that is likely another article for another time.  What I want to talk about is my involvement in music, and some of the lessons learned throughout my life.  Granted, not everyone is a musician.  For the purposes of this article, I could easily replace the word “music” with “sports.”  These lessons aren’t necessarily about the science of music; rather, it’s how my experience in music shaped my life and, eventually, my professional development.   It’s about being a part of something bigger than myself, and how I’ve been able to contribute.  Many other people likely had these same experiences playing sports; it just so happens that my experience was in music instead of sports.

Anyway, for the context of this article, some personal history about my musical background is in order.  I started learning the piano when I was 7, and I picked up the clarinet when I was 8.

In my school district, second graders were offered the chance to learn an instrument, which was determined by your skill in recorder lessons.  You had your first and second choices of instruments.  The saxophone was actually my first choice; the clarinet was second.  I don’t remember why, but they wouldn’t let me take up the saxophone, so I ended up on clarinet.  As it turned out, I became pretty good at the clarinet — good enough that it provided me with opportunities later down the road.

Lesson learned: just because your first choice doesn’t work out doesn’t mean opportunity doesn’t exist.

XmasSax

I ended up learning how to play saxophone years later, when I was in high school.  I actually discovered that it was easier for a clarinetist to learn saxophone than it was for a sax player to learn clarinet.

Lesson learned: sometimes, things happen for a reason.

Like just about any typical kid, I hated to practice.  (Admittedly, I still do!)  But as time went on, I realized that the only way I was going to get better was to work on it on my own time.  So I worked at it, and got better.

Lessons learned: practice makes perfect, and preparation is everything.

As a kid growing up, my piano teacher lived next door.  To get to my lessons, all I had to do was climb over the bluestone fence that separated our properties.  Every year, we had an end-of-year recital at our local community college.  (I remember looking forward to the post-recital reception — hey, cookies! — the most.)  Every year, I felt nervous about getting on stage to perform — a feeling that lessened each year.  I suppose it was a sign of my growing confidence in something that I enjoyed and was able to do fairly well.

By my senior year in high school (and my final recital), my fellow seniors asked me to present our piano teacher with flowers as a “thank you for putting up with us everything you did.”  (I told them I wouldn’t do it unless they were on stage with me; they obliged.)

To this day, I’m not entirely sure why my fellow seniors picked me to be the spokesperson, but they saw something in me that I didn’t.  By that time, I’d gotten comfortable with performing on stage, but I felt pretty nervous when my friends appointed me to make that presentation.

Lesson learned: to be successful, you need to step out of your comfort zone.

My school’s marching band had built quite a reputation.  In 1973 (?), they were selected to perform halftime at a New York Jets game.  The Jets still played at old Shea Stadium, and the weather that day was, to put it mildly, not nice.  Despite the weather, the band took the field at halftime.  The play-by-play man said on national TV, “this is probably the bravest marching band in all of America” (or something to that effect — they actually showed halftime shows on TV back then)  In 1976, they were selected to perform at the Macy’s Thanksgiving Day Parade.  In 1980, they were featured in a commercial for Pepsi-Cola.  I remember watching that commercial as a wide-eyed kid and proclaiming, “I’m going to be in that band.”

Lesson learned: sometimes, you need a dream — or a goal.

Ray_OCS_MarchingBand

I was supposed to play clarinet in my high school band, but my freshman year, the band got a brand new set of marching bells.  Because I played the piano, I was one of the few people in the band who knew his way around a keyboard.  So guess who got the job of playing the new set of bells?  I was already getting praise for my musical prowess, but somehow, playing the bells gave me a whole new prominence with the rest of the band.  Had I stayed on clarinet, I would’ve been just another face among about a dozen other clarinet players.  I played the bells for three years before I switched back to clarinet.  But the bells gave me new experience that I was able to parlay into mallet percussion opportunities later in future groups.

Lesson learned: if an opportunity arises, jump on it.  You never know where it might lead.

I played in my high school band for four years, and ended up doing quite a bit with them.  I marched the Macy’s Thanksgiving Parade in 1981 (yes, I am visible in the video link; you can see me in the percussion section playing the bells at 0:22).  We performed halftime for a couple of New York Giants games.  We were in the stands for the infamous snow plow game (the Patriots would not let us on the field because of the snow; we performed The Star Spangled Banner from the stands and left the game before halftime, so we never got to see the field goal).  We played pregame for a couple of Yankee games (I remember standing in center field, playing my music, and thinking to myself, “wow, I am playing in center field in Yankee Stadium!”).

My last game as a member of my high school marching band was a Giants game.  I remember thinking that it would be the last time I’d step on a football field with a marching band.  Little did I know at the time that I would end up attending a college that had a marching band.

Ray_SUMB_Parents_Wknd

College was a whole new experience (as it usually is for any kid coming out of high school).  Here I was, a small town kid going to a large university that happened to play NCAA Division I sports.  And as is often the case of most major college football schools, the school had a marching band.

When you’re in high school, and you become really good at something, you tend to think you’re hot stuff.  You’re a big fish in a small pond.  But for me, in going to a large school like Syracuse University, the pond suddenly got a heck of a lot bigger.  Suddenly, here were a bunch of people who could do the same things that I could — sometimes, even better.  For me, however, it set the bar higher.  I was determined to do well in a band of over two hundred people.  And for four years, I felt proud to be part of that team.

Lessons learned: you get better by setting the bar higher.  Be a team player; your contributions make a difference.

Even after I graduated from Syracuse and moved to the Albany area, I continued to play my music (and I still do).  More opportunities came up.  I joined a few large ensembles.  I became an accompanist for a church.  I’ve written songs and created some demos.  I got paid to play for weddings.  I found a bar in downtown Albany where I could play for a few extra bucks.  I was asked to accompany students, instrumentalists, and show productions.  And through all these opportunities, I still continue to improve and grow as a musician, as a professional, and as a person.  I continue to enjoy what I do.  And I intend to keep playing for a long time to come.

Lessons learned: never stop doing what you love.  You’re never too old.  Share what you love with the world.

Play on!