Be the best you you can be

Ho-Jon: “How can I ever thank you?”
Hawkeye Pierce: “You just go and be the best you you can be.”

“Won’t you please, please tell me what we’ve learned?  I know it sounds absurd, but please tell me who I am…”
— Supertramp, “The Logical Song”

“Who are you?  Who, who, who, who?”
— The Who

At my CrossFit gym last night, we had a tearful good-bye to a friend (one of our members) who was moving out to the western part of the state, along with her husband and children, for a new life.  One thing she said struck me: “I’m not the same person I was when I walked into this place.  How is this new person going to be able to adapt to a new place?  Am I going to be able to find another Ray, or another [name], or another [another name]…?”

I said to her, “all you can do is be you.”

I said that, and I believe that.  But what, exactly, does that mean?

I could probably write an entire book about that (and some people have), but I’ll spare you the gory details.  Besides, I’m no psychologist, and what I say might be worth about as much as a politician’s alt-facts (don’t get me started).  But, since this is a ‘blog article, and I write what I think, well…

For starters, you’re the one person whom you’ll get to know the best.  You know your likes and dislikes, your strengths and weaknesses, your tastes, your interests, and so on, better than anyone else.  You’re the one person over whom you have complete, 100% control (disclaimer: I am not talking clinically; that is another discussion about which I know nearly nothing).   If you don’t know yourself, if you don’t take stock of who you are, you’ll start having issues.

Knowing yourself leads you to something else: having confidence and faith in yourself.  If you know yourself, you know, for the most part, what you’re capable of doing.  I’m not always sure as to what I’m capable of handling, but I do know myself enough to know what I can do.

This bring me to another thought: being you also means testing your limits.  Testing your limits means stepping outside your comfort zone.  Are you capable of doing more?  Often, you won’t know until you try.  And once you do try, how does it make you feel?  Proud?  Accomplished?  Can you do even better the next time you try?  The point is, you will always be you, but you are never static.  We are always changing.  Who you are now is probably not the same you from years ago.  And who you will be in several years won’t be the same you that you are now.

The world is a scary place.  It is human nature to fear what we don’t know.  But the world around us often defines who we are.  Who we are depends on what kind of cards we’re dealt.  We are often shaped by the changes we face.  And in the end, the way you deal with change is to continue being the same, ever-changing you that you’ve always been.


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

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:


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.


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:


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.


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.

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.

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!

Installing SQL Server 2016

In an earlier article, I discussed how to get started with SQL Server.  I mentioned downloading a copy of SQL Server Developer Edition and installing a sample database.

It had been quite some time since I performed a SQL Server installation (my current job only requires that I know T-SQL, not how to install or implement it; after all, I am a developer, not a DBA).  Additionally, I figured that having SQL Server installed on my own local machine gave me a platform on which I could practice my skills in a safe environment.  With that, I decided that I would shake off the rust and perform an installation on my own machine.  This article documents my efforts.

I’ll start by describing the platform on which I’m installing SQL Server.  I am installing this on my own laptop PC, not a server.  It might or might not be an ideal environment for running SQL Server, but it’s what I have.

  • HP ProBook 4430s laptop
  • Intel Core i5-2450M processor @ 2.50 GHz
  • Windows 10 Pro 64 bit
  • 8GB RAM
  • 325GB available disk space

I decided that I wanted to keep up with the latest version (which, at the time of this article, is SQL Server 2016), so I needed to find a copy to install on my machine.  To do that, I need to go into Visual Studio Dev Essentials.  Visual Studio Dev Essentials is free, but it does require you to create an account.  If you don’t have an account, you will be prompted to create one when you click “Join Now.”

Once your account is established (or if you already have one), you are redirected to the Visual Studio Dev Essentials page.


SQL Server can be downloaded from the Downloads menu at the top of the page, as well as the Microsoft SQL Server icon under Tools.

I went to the Dev Essentials page, and saw a note that SQL Server 2016 had a critical prerequisite, so I started with downloading and installing the patch.  Select the download that matches your system (for me, it was English-United States x64.exe).  Note that this requires a restart after setup completes.  Once the patch was installed, I went ahead and downloaded SQL Server 2016.  It’s a 2.1GB download.

Once the download finished, I ran the setup.exe program, which brought me to the SQL Server Installation Center.SQLServerInstallationCenter

Clicking the Hardware and Software Requirements brought me to the corresponding page.  Reading through the requirements, I didn’t see anything that would immediately be an issue.  The only thing I saw was that it required .NET Framework 4.6, which supposedly is installed by the program.

I decided to go ahead with the installation.  After clicking the Installation tab, I clicked New SQL Server stand-alone installation or add features to an existing installation.  For the Product Key, I specified the free Developer edition, clicked Next, and followed the subsequent prompts.  I received a warning about Windows Firewall being enabled.  I opened my Settings, went into Network & Internet, and checked my Windows Firewall.  Interestingly, my Windows Firewall would not let me turn it off; it said it was controlled by Norton 360, which is installed on my computer.  This was interesting; I had no idea that Norton 360 was controlling my Windows Firewall.

For the moment, my notification regarding Windows Firewall was a warning, not a failure.  I decided to continue to see what would happen.  I went ahead and clicked Next.  This brought me to the Feature Selection window.


I went ahead and clicked Select All.  I had no idea what features I would use, I figured that I was installing SQL Server 2016 so I could practice and learn about its features, and it would only take 8GB of disk space — considering that I have 325GB available, it’s only a drop in the bucket.

However, when I clicked Next this time, I came across an error.


Interesting.  Should I install the Oracle SE Java Runtime Environment or not?  To answer that question, I asked myself whether or not I needed the PolyBase Query Service for External Data.  For that matter, what, exactly, is the PolyBase Query Service?  I found my answer here.  (Note that this feature is new to SQL Server 2016.)

For the moment, I decided that I didn’t need to work with data in either Hadoop or Azure blob storage, nor did I foresee an immediate need to interface with BI tools.  If I ever come across a need for them, I figure that I could always add it later.  So I unchecked the PolyBase Query Service (for now) and decided to proceed.  (Unchecking this, by the way, brought my disk requirement down to 6.5GB.)

I went with the default named instance (MSSQLSERVER).  In fact, I pretty much went with the default configurations (the one exception being selecting Mixed Mode instead of Windows authentication mode; I did add myself as a SQL Server administrator, as well as other services configurations).

I didn’t think to keep track of how long it took to install SQL Server 2016, but I would guess it took about an hour.  SQL Server installed with no further issues.

SQL Server Management Studio 2016

For as long as I can remember, SQL Server Management Studio (SSMS) was included as a standard part of installing SQL Server.  That is not the case with SQL Server 2016; SSMS must be installed separately.  Clicking Install SQL Server Management Tools in the SQL Server Installation Center takes you to this page.  Because SSMS tends to be my go-to tool for using SQL Server, I wanted to make sure it was included with my installation.

I downloaded the SSMS .EXE file, which is 825MB.  Installing SSMS is straightforward; click Accept to accept the license agreement, and the package installs.

Other SQL Server 2016 Tools

The SQL Server Installation Center also includes support for SQL Server data tools (SSDT) and for a new R server installation.  For now, I am bypassing these tools; I will likely install them at a later date.

After all was said and done…

Once everything was installed, I opened SSMS, which ran successfully.  (One thing I’ll add is that SSMS now looks like Visual Studio, which makes sense since it is powered by the VS engine.)

I now have SQL Server 2016 installed on my machine.

What’s next?

I will be implementing Sean Lahman’s baseball database into my SQL Server.  My exploits will be documented in a future ‘blog article called “Installing a Baseball Database.”  Stay tuned!

SQL Server: How to get started

A few weeks ago, my friend Jim called me.  His company was ending its relationship with a major client, and as a result, his position was likely to be reduced (or eliminated — I don’t remember which one he said) over the long term.  He knew that I’d done a lot of work with SQL Server (mind you, I don’t know enough to be an expert, but I know enough to get by), and had some questions for me — mainly to the effect of, “I want to learn SQL Server.  How do I get started?”

I see this question come up a lot in various forums, especially on (which, by the way, was one the first references that I gave Jim).  I suggested that he create a free account on the site so he could access and ask questions on the forum.

I mentioned he should look into attending SQL Saturday.  And on hindsight, I’d forgotten to mention that he should look into joining a PASS chapter.

I suggested that he obtain and install a copy of Microsoft SQL Server Developer Edition, which is a free download.  I also mentioned downloading the famous (infamous?) Northwind/AdventureWorks sample databases.  Practice makes perfect, after all, and if he could get hands-on experience with SQL Server in a sandbox environment, all the better.

Side note: Jim (a Red Sox fan) and I (a Yankee fan) are both big baseball fans (yes, we argue frequently!).  When I think of databases on which to practice, my preference is to download a copy of Sean Lahman’s baseball database.  I have nothing against either the Northwind or AdventureWorks sample databases; my thinking is that if you’re going to learn a new technology, you might as well make it fun!

And, of course, no introduction to SQL Server would be complete without Books Online (frequently abbreviated “BOL”).

Those are my suggestions.  Do you have any more?  If so, please feel free to leave them in the comments below!