Developing an introductory presentation to SQL Server

So far, all of my SQL Saturday presentations have been professional development talks — “soft topics,” as they’re often described. I don’t present about technical topics, but I do present topics that are of interest to technology (and perhaps other) professionals.

This is not to say that I don’t have technical skills. I do have a background in development and databases, but as I often introduce myself during my SQL Saturday presentations, I probably fall under the category of “knows enough SQL to be dangerous.” I am neither a SQL expert nor an MVP. While I am knowledgeable about SQL Server, I likely won’t be doing any presentations about power BI, data compression, or data security anytime soon.

I can, however, discuss rudimentary topics about SQL Server that might be of interest to people who are just getting started with SQL Server. When I first started my ‘blog, I wrote some articles about how to get started with SQL Server. As my ‘blog (and my professional life) has evolved, I’ve been moving more toward the soft topics about which I’m more knowledgeable and tend to present, and away from the hardcore technological topics.

An idea that has been in the back of my mind for some time is to develop presentations geared toward people who are just getting started with SQL Server and even databases in general. This idea is not new; I’ve toyed with it for a while, and only lack of time has kept me from developing it further.

One observation I’ve made during my frequent trips to SQL Saturday events is that many of the presentations are geared more toward “seasoned” SQL personnel; that is, people who already have some background knowledge of SQL Server and its workings. They are all very good topics, but for a person who is just getting started, they can be overwhelming — as is often described, a proverbial “drink from a firehose.”

There does seem to be a market for this idea. I’ve spoken to Grant Fritchey a few times about my idea, and he has encouraged me to pursue it. One thing that was mentioned to me was that part of the reason why many SQL Saturday presentations tend to be more advanced is that the presenters themselves are fairly advanced. A lot of them are SQL experts and MVPs, and are presenting topics at a much higher level from where a SQL beginner would need to start. It would be akin to asking a college professor to teach kindergarten.

Grant even suggested that I make these presentations into an entire precon — as there is way too much material to cover in a single SQL Saturday presentation. This is an idea that intrigues me, and it’s something that I’m interested in developing. It’s just a matter of me taking the time to sit down and putting it together.

I have a few reasons for writing this article. Among them are a form of self-encouragement to pursue this endeavor and a forum to list some of my thoughts. On the latter, I wanted to list a few topic ideas listed so that I can refer to and develop it as I go along.

Some of the topics I would cover would likely include the following.

  • A general high-level introduction to SQL Server and databases in general
  • Basics of T-SQL
  • An introduction to relational tables
  • Basics of data normalization
  • An introduction to database applications

I’m sure there are some other topics that haven’t occurred to me. If you have any suggestions, feel free to list them below in the comments.

This is an idea that has been kicking around my head for at least a few years. Maybe sometime, I’ll actually sit down and start working on it. Hopefully, that sometime will be soon.

Advertisements

A user group is a good place to start

“The journey of a thousand miles begins with one step.”
— Lao Tzu

Earlier this week, a friend at my CrossFit gym (and who has become more interested SQL Saturday and my SQL user group) asked me if I thought a talk about Excel would make a good topic for SQL Saturday.  I said, why not?  If it’s a talk that data professionals would find interesting, then it would make for a good talk.  I encouraged him to attend our next user group meeting and talk to our group chair about scheduling a presentation.

I’ve written before about how local user groups are a wonderful thing.  It is a great place to network and socialize.  It is a free educational source.  And if you’re looking to get started in a public speaking forum, your local user group is a great place to start.

I attended my first SQL Saturday in 2010, and I knew from the very start that I wanted to be involved in it.  Our local SQL user group was borne from that trip (Dan Bowlin — one of our co-founders — and I met on the train going to that event).  I came up with a presentation idea that I developed and “tried out” at a user group meeting.  I submitted that presentation to a few SQL Saturdays.

That user group presentation was in 2015.  I’ve been speaking at SQL Saturdays ever since then.

If you’re interested in getting into speaking, if you want to meet new people who share your interests, or even if you just want to learn something new, go find a local user group that matches your interests and check it out.  You’ll find it to be a great place to kickstart your endeavors, and it could lead to bigger things.

Reaping what you sow

I originally started my ‘blog to supplement my SQL Saturday presentations (among other things).  I’ll admit that I wasn’t entirely sure what I was getting into with this endeavor, but one thing that was in the back of my mind what that my efforts might lead to bigger and better things.  It’s still too early to know whether or not I’m near that goal (I’m not there yet), but I’m seeing signs that I might at least be heading in the right direction.

I previously mentioned that I was invited to record a podcast for SQL Data Partners.  That podcast is scheduled to air tomorrow — when it does, I’ll post a link to it!  (Update: my podcast is now online!)  I was excited to do that podcast; recording it was a lot of fun (although there were a couple of things that I wish I’d said differently — that’s another article for another time), and it made me feel pretty good that I was being recognized for a skill that’s right in my wheelhouse.

I’m also seeing subtle indications that my skills are being recognized.  In my current job, people are increasingly referring to me and asking me questions about documentation, writing, and communication-related issues.  On the SQL Saturday circuit, I feel as though I’m treated as an equal among other speakers, despite the fact that I’m not necessarily an expert in SQL.  I’ll admit that I’m somewhat humbled when I think about the fact that I’m sharing space with SQL MVPs.  My presentations may focus on soft professional development (rather than hardcore technical) topics, but these people make me feel like a fellow professional and one of their peers — and that makes me feel pretty good!

There are many resources you can tap to get yourself going.  I highly recommend an article by James Serra where he discusses how to advance your career by ‘blogging.  I also suggest a SQL Saturday presentation by Mike Hays where he talks about creating a technical ‘blog.  They are both excellent presenters, and I recommend attending their presentations if you have such an opportunity!

There are a number of ways to refine and practice your skill sets.  Activities such as writing ‘blog articles, taking part in a user group, speaking about topics in your field, answering questions in an online forum, taking courses, and so on, provides a solid foundation for the skills you want to establish.  It’ll take time, but if you make the time and effort to develop and enrich your skills, your efforts will eventually bear fruit.

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
) 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.

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!