The Basics of Joins – Skill #4

This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.

Databases are built to store data. That’s the primary purpose, and in SQL Server, we store data in a relational form. That means that often we have data spread across multiple tables. Why we do this is a discussion for another day, but suffice it to say that we often have structures like this:


Part of the table in AdventureWorks above and the HumanResource.Employee table below.


One typical join task might be to get an employee’s name, or a list of employees and their names. Here we have a birthday in the Employee table, but we don’t have a name. That’s in the Person.Contact table. Essentially we want to match these up using basic, elementary school set theory.


In the diagram above, you can think of each letter as a row in a table. As an example, let’s assume that B in the orange circle represents the row in the employee table with a ContactID value of 4. The B in the pink circle would represent the row in the Contact table with a ContactID value of 4 as well.

When we join these to get the Employee name and birth date, we get:


I used a join in my query to get that:

, c.LastName
, e.BirthDate
   INNER JOIN HumanResources.Employee e
     ON c.ContactID = e.ContactID
 WHERE c.ContactID = 4

In this query I’ve included two tables in the FROM clause with the INNER JOIN key phrase between them, which specifies I only choose the matching rows. The match is made in the ON clause.

I’ve also qualified this to only apply to the row with a ContactID of 4 in the WHERE clause.

There’s a lot more you can do with joins, and you can include more than two tables, such as this query:

, c.LastName
, e.BirthDate
, pa.AddressLine1
, pa.AddressLine2
   INNER JOIN HumanResources.Employee e
     ON c.ContactID = e.ContactID
   INNER JOIN HumanResources.EmployeeAddress ea
     ON e.EmployeeID = ea.EmployeeID
   INNER JOIN person.Address pa
     ON ea.AddressID = pa.AddressID
 WHERE c.ContactID = 4

I would recommend that you practice working with basic joins, based on the information that you commonly see queried in your application. Sooner or later someone will ask you for some data that isn’t available in the application and you will want to write a query to extract it for them.

Basic Inner Joins – Basic Skill #4

This post is part of a series based on my presentation The Top Ten Skills You Need for SQL Server. This post is part of Skill #4 – Joins.

T-SQL Tuesday #23

This post is also serving as my T-SQL Tuesday post for the month. This month the party is brought to you by Stuart Ainsworth. If you want to know more about what T-SQL Tuesday is about, read Stuart’s post as well as Adam Machanic’s original post and then join in next month.

An inner join is essentially an intersection of two sets. If you go back to grade school, and think about sets, you can have two items like this:


If you look at these two sets, you see that there are various elements in each set. If we were to show the intersection, it would be this set: (B). This is shown below (excuse my horrible artwork):


In SQL, we deal with tables, but we can model this as follows:

( mychar varchar(1) ) GO CREATE TABLE SET2
( mychar varchar(1) ) GO INSERT SET1 SELECT 'A' INSERT SET1 SELECT 'B' GO INSERT SET2 VALUES ('B'), ('C'), ('D') GO SELECT a.mychar
  , b.mychar
   INNER JOIN set2 b
     ON a.mychar = b.mychar
mychar mychar
------ ------
B      B

The results are the matching values in each table. In this case each table is a single column, modeling the images above where there is a single letter in each item of the set. The matching columns are the join columns, and in database work, these would be the data items that we are storing in both tables.

However to expand this, in a database table, we usually have multiple items, so each letter could be a series of data elements, or could have a series of other fields attached to it. Suppose I change these “sets” a little:

( mychar varchar(1) , Customer VARCHAR(50) , ActiveDate datetime ) GO CREATE TABLE SET2
( mychar varchar(1) , Customer VARCHAR(50) , ActiveDate datetime ) GO INSERT SET1 SELECT 'A', 'Bob', '1/1/2011' INSERT SET1 SELECT 'B', 'Bill', '2/1/2011' GO INSERT SET2 VALUES ('B', 'Steve', '1/2/2011'), ('C', 'Andy', '3/1/2011'), ('D', 'Brian', '3/3/2011') GO SELECT a.mychar
  , b.mychar
   INNER JOIN set2 b
     ON a.mychar = b.mychar

I still only have one matching row, but there are other data points. If I alter my diagram, they look like this:


The matching rows, in this case the rows with a “B” in them, have different data, which seems to be an issue. However suppose set 1 was a list of customers along with their first order date and set 2 was a list of salespeople and the dates they started. Then the join might be on sales, with the letter (A, B, C, D) representing the order.

That’s the basic of a join. It gets complicated as you look to join three, four, or more tables, but this is the basic idea of an inner join in SQL.

Map a Login – Basic Skill #3

This post is part of a series based on my presentation The Top Ten Skills You Need for SQL Server. This post is part of Skill #3 – Setup Security.

I wrote about the basic security model for SQL Server, and got a question about mapping users to logins. It actually is done automatically for you in the dialog when you create a login, but I thought I’d cover the basic process here in more detail.

Logins allow access to an instance of SQL Server. Users are the construct in a database that can be assigned permissions (explicitly or through a role). The mapping between a user and a login is what allows SQL Server to determine which logins get which rights.

Let’s look at an example. On one of my instance, I have a test login called “JoeLogin”. If I connect to the instance, I enter the credentials of “JoeLogin” and the password to connect.


Once I connect, however, the first thing the SQL Server database engine does is set my context to either the database I’ve specified, or my default database. This immediately maps me to a user in that database and allows me the connection to run commands, or it returns an error if I don’t have access.

In this case, I have access to my default database, and I can check on my user credentials with this code:


This returns “JoeUser”, which is my user name. You can read about user_name() here, but it is a system function that returns your current database user name.

My login has essentially been bypassed, and would only be used if I needed to check permissions to execute an instance level function, like setting a configuration value or viewing the error log. However I can check my login by using the SYSTEM_USER function.


This will return my login name, and it returns “JoeLogin” on my instance. I can easily see this in SSMS, in the right corner of the status bar at the bottom of the window.


The Mapping

We can view the mapping between users and logins in two ways. If you want to see where a user is mapped, you can right click the user and select properties.


This will bring up a dialog for the user, and at the top you can see the login mapped to this user:


If you want to see where a login is mapped, you can right click the login in the server level Security folder and select properties. This brings up the login dialog, and if you select the “User Mapping” item from the left pane, you will see the list of databases and the user mappings.


In this example, my JoeLogin has been mapped to two database. In db1, the default behavior is applied and the login is mapped to a user with the same name. In db4, I have changed the default and mapped to a user called “JoeUser”.

I haven’t run across a good reason to change the user name from the login name, and I don’t recommend it, but if you think you might have some issues, this is how you check things.

Create a Login – Basic Skill #3

This post is part of a series based on my presentation The Top Ten Skills You Need for SQL Server. This post is part of Skill #3 – Setup Security.

I wrote about the basic security model for SQL Server, recently and wanted to now expand on the practical aspects of how you setup security. Let’s start with logins and creating them.

In the current versions of SQL Server, which includes SQL Server 2008 R2 and below, a login is the way in which you connect to an instance of SQL Server. Things may change in SQL Server 11 with contained databases, but I think this will still apply in many situations.

Logins are defined at the instance level, and in Management Studio, you can right click the Logins folder and select New Login to create one.


This will create a dialog like this one:


There are lots of choices here, but really there are only a few decisions that we make for most logins. You might use the other options, but these are the basics for 80% of the cases, following the 80/20 rule of this series.

The first thing is the login name. This can be a user or group in Active Directory, and as you can see in the next image, if you don’t know the exact name, there is a search button. This is the standard AD search dialog.


There is a radio button below the name, which defaults to Windows Authentication. That’s for AD accounts, and is the recommended default. You can also choose SQL authentication, which means that a password is required and the administrator must set it.


If you choose SQL authentication, and are on Windows 2003 or later (Vista/XP as well) that allow you to set password policy. As a note, leave these checked unless you have a great reason not to. For most logins you should not know the user’s password and it ought to conform to the policies.

The rest of this page is advanced stuff that isn’t often needed. The only thing that you should look over is the default database. For normal users, make sure this is a database the person will have access to. For administrators, leave it at master.


Next we look at the server roles page:


These are roles, or groups, with permissions for the instance. They don’t necessarily give a person access to a database, but many of them could allow someone to gain access, so for most users, leave this blank. For administrators, give them just what they need.

The user mapping is next, and this is where you can have the dialog create a user in the database and grant access.


Most users will need access to a specific database, usually the one you chose as their default database. If you select a database checkbox, the user will be created with the same name as the login by default. Leave this alone, it’s a good policy.


Once you select the user, you also can add a database role at the bottom. Everyone is a member of public, and you should have a database role you’ve created for permissions that you can assign to them. I dislike giving regular users any of the fixed database roles like db_datareader. They are too global in permissions and have caused me confusion later on.

Create your own role and assign permissions.

Next is the explicit securables tab. Don’t use this unless you know what it means. I never use it, and most of the time you shouldn’t. Leave it along until you learn why, and more importantly, why not to use it.


That last tab isn’t one you normally need, but you might come here if a user has locked themselves out.


Leave these defaults alone unless you need to disable the user or prevent them from accessing this database for some reason. You can switch the radio buttons. If the user is locked out, the bottom checkbox will be selected and you can uncheck it.

That’s the basics of creating a user in SSMS. For the most part, stick with defaults and keep your security simple, and restrictive. Don’t grant more rights than you need to.

The 80/20 Rule – Top Ten SQL Server Skills

Over the years that I’ve worked in technology, I’ve found that the 80/20 rule seems to apply pretty well to all the different areas that one can specialize in. It doesn’t matter whether you build PCs, work on the help desk, administer systems or develop software, the rule has pretty well been true for my career.

The 80/20 rule essentially says this: 80% of your job takes 20% of your time (or skills) and the remaining 20% of your job takes 80% of your time (or skills). It doesn’t matter if this is your daily work or a project that you are assigned. Most of your effort is in finishing the last details, or handling the most complex parts.

I’ve heard this joked about in software development as the rule of 90s as well, saying that the first 90% of a piece of software takes 90% of your time and the last 10% takes the other 90% of your time.

I built my presentation, The Top Ten Skills You Need for SQL Server, based on this rule. It includes the most skills that often solve questions I see asked at events or on forums. It seems that these ten skills along with the basics of performing them will solve most of your problems.

The Basic Security Model in SQL Server – Skill #3

This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.

Users and Objects

The basic security model diagram that I use is the one below. It’s not fancy, but it conveys the basics of security in SQL Server.


From left to right, users or clients are mapped to principals. Those principals are both in the instance (login) and database (user) as well as roles. Permissions are assigned to roles on objects.

That’s essentially what the basic security model should be for most people. There are other types of structures (credentials, certificates, etc), but in terms of the 80/20 rule, here’s what most DBAs should do:

  • Create a login for a person (either Windows or SQL Server login)
  • Map this login to a user with the same name in those databases that person needs access to. Only pick those databases needed, not all databases.
  • Create a role in each database for each group of users/permissions.
  • Add the users to this role
  • Grant permissions on the objects needed to these roles.

It’s not complicated, and sticking to this simple scheme, and not granting db_owner or sysadmin to logins or users will allow you to implement basic, easy to understand security in SQL Server.


Create a Log Backup Schedule – #1 Skill You Need

This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.

You Need Log Backups

By default databases in SQL Server are created with the Full Recovery Model. That means that without log backups, the log will continue to grow and grow until it hits the growth limit you’ve set, or it fills the disk. I see questions on this constantly at SQLServerCentral from people who have a 10MB database and a 653GB log file.

A log backup will mark the transactions in the log as backed up, and that space can be re-used. The log backup aids in space management and also provides recovery to points in time in between full backups. With that in mind, you need to do a few things.

Schedule Log Backups

As soon as you create a database, and you create a full backup schedule, schedule log backups as well. It’s easy to schedule a single daily log backup along with your full backup. They don’t block each other, and for most non-production systems, this works fine.

Note that the total space used for the log backups is the same whether you schedule 1 a day or 86,400 a day. There is a slight overhead to each file, but essentially the space used is the same. However backups scheduled more often result in a smaller log files size for the LDF.

When Do You Take a Full Backup?

This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.

When is a Full Backup Taken?

Most people might answer this with “every day”, or “as often as needed”, but those are a little nebulous, and not necessarily correct.

I do recommend that you backup as often as you can, which is every day for most people. Those with large databases might go once a week, or even once a month, but you want to get a full backup periodically. If you have a really large database, say 50TB or greater (in 2011), you might opt for a yearly SAN snapshot of some kind, but you need some full backup as a base.

So when do you backup your database? Here’s a list of some times you might think about:

  • As often as you can on a regular schedule (daily, weekly, monthly, etc)
  • Before applying a Service Pack or CU*
  • Before applying a Windows patch*
  • Before upgrading your application*
  • Before a large data load*
  • After restoring your instance in a DR situation

That last one might throw people, but I’ve seen more than a couple situations where someone recovered a database in a DR situation and never setup backups. And there was another failure.

Whenever you create a database, either from scratch or after a restore, make a backup.

* Note that if you have Enterprise Edition or above, you can use Database Snapshots to substitute for a full backup in some of these situations.

#2 Skill – Performing a Restore

This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.

You Will Restore a Database

At some point you’ll need to restore a database. It might be a database on your local instance of SQL Server to correct a problem with a query or a patch, but you’ll need to restore data.

This goes along with the first skill of backing up a database as the counterpart. A backup saves the data (and objects) and a restore brings that data back.

Restores are fairly easy, but there are a couple of things you need to learn right away:


By default the RESTORE command brings a database online by going through the recovery (redo and undo) processes. For a full database restore, this means you cannot restore additional logs. You might not to this time, but at some point you will.

So always use WITH NORECOVERY.

You need this in database mirroring, log shipping, and more scenarios. Always include this in your restore commands. To bring the database online when you are sure you are done restoring (even if this is only one restore), use the RESTORE command and WITH RECOVERY, as in:


Learn to move files

I find that many restores take place for practice, or on servers other than the original ones. In that case, the paths might not exist. Often the production servers, or the main servers you use, will have more drives than the test servers. In that case, having a file stored on the z: drive for a server doesn’t match up with a development server containing only a C: drive.

The WITH MOVE option is used to move the existing logical files in your restore to a new location. Here is the sample command from Books Online.

Script Restores

It’s easy to make mistakes with the GUI in SSMS. Learn to script restores and run the scripts. Even if you use SSMS to setup the restore, don’t click OK. Instead click this:


The Top Ten Skills You Need for SQL Server

This talk is designed to talk about the most common skills that you need to work with SQL Server on a daily basis. It is mostly for administrators, but quite a bit of this information would benefit developers if they understood it.

The top ten skills are:

  1. Perform a Backup
  2. Restore a Database
  3. Setup Security
  4. Join Tables Together
  5. Create an Index
  6. Run Database Maintenance
  7. Schedule a Job
  8. Send an Email from SQL Server
  9. Import Data
  10. Search BOL

Length: 75 minutes (can be done in 60)

Slides are available on SkyDrive.
Talk specific slides are here:
The Top Ten SQL Server Skills You Need – PASS Summit 2011
I have a series of blog posts under the TopTenSkills tag that relate to these items.