Voice of the DBA

Tag: sql server

How Many Calls? Part 2
I wrote recently about a report that a friend of mine needed from his Access database. In the first post, I used a CTE and an outer join to cull together some data, however since I wasn’t sure Access would support any of that, I came up with another way.

If you read the last post, you’ll have my setup code and sample data. In this one I decided to move to sub-queries as the first part of my work. I started with this:
```
SELECT a.date_sent 'date_sent' , ISNULL( a.five_day_call, 0) 'five_day_calls' , ISNULL( b.ten_day_call, 0) 'ten_day_calls' FROM ( SELECT date_sent
       , COUNT(*) 'five_day_call' FROM calls
       WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 6
       GROUP BY date_sent
) a
inner JOIN ( SELECT date_sent
  , COUNT(*) 'ten_day_call' FROM calls
   WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 11
   GROUP BY date_sent
 ) b
   ON a.date_sent = b.date_sent
 go 
```
I know this doesn’t work because both queries don’t have all the dates. However there was a better way, and one I should have thought of immediately. A few people noticed this in the comments to the previous post. We are looking to count records, according to some criteria. The SUM function will do this. If I run this, I get a count of calls in my 5 day window.
```
SELECT a.date_sent 'date_sent' , SUM( CASE WHEN DATEDIFF(DAY, date_sent, acc_call_date) < 6 THEN 1 ELSE 0)
```
FROM calls GROUP BY date_sent

That worked, and it’s easy to understand. What SQL Server does is look through each row, and if there is a row that matches the DATEDIFF function, meaning the call back date is less than 6 days from the call date, then it returns a 1 for that row. Otherwise a 0 is returned. If I then sum the results for each day, I essentially get a count for the date since each call counts as one (or zero).

To get the final query, I can then do this, adding a column for each type of row I am looking for.
```
SELECT a.date_sent
 , SUM( CASE WHEN DATEDIFF(DAY, date_sent, acc_call_date) < 6 THEN 1 ELSE 0) 'five_day_calls' 
 , SUM( CASE WHEN DATEDIFF(DAY, date_sent, acc_call_date) < 11 THEN 1 ELSE 0) 'ten_day_calls'
```
```
FROM dbo.Calls
```
GROUP BY date_sent

That produced the correct results, and it was easy to convert to Access.

Again, I’m not sure this is the most efficient way to do this, but it worked and it was an interesting problem. If you have a better way to do it, let me know.
December 29, 2011
Backing up the Service Master Key
Making a backup of your Service Master Key (SMK) should be one of the first things that you do if you are enabling encryption. This key encrypts and secures your other keys, and it will be needed when you have to rebuild the server.

Backing up the key is simple. You use the BACKUP SERVICE MASTER KEY command and it only has two arguments
- File – The path and name of the file for the backup.
- Password – provides security for the backup file. This is needed when restoring the file.
That’s it. Performing a backup is as simple as running a command like this:
```
-- Backup the SMK
BACKUP SERVICE MASTER KEY
 TO FILE = 'c:\sqlbackup\MainServiceMaster.key'
 ENCRYPTION BY PASSWORD = 'S3cureP@ssword!sneeded'
```
Securing this file, however, is a little harder, and managing it is really hard. Ideally you do not want this key stored with the backup files for the server, because you don’t want anyone to have this and the data. However if you must do that, and that might be the practical thing to do so that it’s available when you use this tape/disk to recover the server, I recommend you do not keep the password with this file. Store it in an admin system somewhere else, preferably a protected system with something like KeePass or Password Safe.

A couple things to note. Just like with a backup, the service account needs write permissions on the path (local or UNC) to create the file. This command requires the CONTROL SERVER permission, so not anyone can use it.
December 26, 2011
How many calls – A T-SQL Question
I got a call from a friend recently that was looking for some query help. He was actually using Access, which I haven’t used in years. He knew a little T-SQL, so he could convert anything I gave him to work with his database.

Here was his issue. He had a list of calls made for a marketing campaign, and with each call, a call back date. His task was to get counts of the calls made for a particular date for which there were call backs within two time periods: 5 days and 10 days.

I want to walk through what I tried and what worked. I actually came up with two methods, though I’m not sure either is that efficient. However they worked, and since this is something he’ll run in Access monthly, it’s not a big deal.

I set up a table and get some samples from him:
```
 CREATE TABLE Calls
( date_sent DATETIME , acc_call_date DATETIME ) GO -- rules -- #1 acc call < 6 days -- #2 acc call < 11 days INSERT calls SELECT '12/1/2011', '12/2/2011' -- meets #1 INSERT calls SELECT '12/1/2011', '12/3/2011' -- meets #1 INSERT calls SELECT '12/1/2011', '12/8/2011' -- meets #2 INSERT calls SELECT '12/2/2011', '12/8/2011' -- meets #2 INSERT calls SELECT '12/2/2011', '12/9/2011' -- meets #2 INSERT calls SELECT '12/3/2011', '12/4/2011' -- meets #1 INSERT calls SELECT '12/3/2011', '12/4/2011' -- meets #1 INSERT calls SELECT '12/3/2011', '12/11/2011' -- meets #2 INSERT calls SELECT '12/4/2011', '12/11/2011' -- meets #2 INSERT calls SELECT '12/5/2011', '12/6/2011' -- meets #2 go
```
I have two rules that track the calls. As a quick note, if a call is returned in 5 days, it’s also returned in 10 days, so we should never have more calls returned in 5 days than are returned in 10 days.

Essentially to meet rule #1, we want this:
```
SELECT date_sent
       , COUNT(*) 'five_day_call' FROM calls
       WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 6
       GROUP BY date_sent
```
If I run this, I get back three rows:

date_sent five_day_call

———————– ————-

2011-12-01 00:00:00.000 2

2011-12-03 00:00:00.000 2

2011-12-05 00:00:00.000 1

These are the counts of calls returned in five days. If I change the scalar from 6 to 11, I get back the calls back in ten days.
```
 SELECT date_sent
  , COUNT(*) 'ten_day_call' FROM calls_made
   WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 11
   GROUP BY date_sent
go
```
The results, as expected, include the 5 calls above, but also have the additional calls returned in ten days.

date_sent ten_day_call

———————– ————

2011-12-01 00:00:00.000 3

2011-12-02 00:00:00.000 2

2011-12-03 00:00:00.000 3

2011-12-04 00:00:00.000 1

2011-12-05 00:00:00.000 1

Now I need to combine these sets. The first thought is often a UNION, but in this case, that doesn’t work. Here’s what happens:
```
SELECT date_sent
       , COUNT(*) 'five_day_call' FROM calls
       WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 6
       GROUP BY date_sent
UNION SELECT date_sent
  , COUNT(*) 'ten_day_call' FROM calls_made
   WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 11
   GROUP BY date_sent
```
I get duplicate rows for each date if there are rows from each separate query:

date_sent               five_day_call

———————– ————-

2011-12-01 00:00:00.000 2

2011-12-01 00:00:00.000 3

2011-12-02 00:00:00.000 2

2011-12-03 00:00:00.000 2

2011-12-03 00:00:00.000 3

2011-12-04 00:00:00.000 1

2011-12-05 00:00:00.000 1

I can’t do a DISTINCT here, nor can I sum up the rows, because the ten day calls include the five day calls.

Plus my friend really wanted this report:

date_sent               five_day_call ten_day_call

———————– ————- —————-

2011-12-01 00:00:00.000 2             3

2011-12-02 00:00:00.000 0             2

2011-12-03 00:00:00.000 2             3

2011-12-04 00:00:00.000 0             1

2011-12-05 00:00:00.000 1             1

This report is designed to measure the effectiveness of calls, and business analysts need an easy report. If I join the two queries on the call date (date_sent), the problem is that I don’t necessarily have matching call dates for all rows.

What about an outer join?
```
; WITH fiveCTE (call_date, five_day) AS ( SELECT date_sent
       , COUNT(*) 'five_day_call' FROM calls
       WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 6
       GROUP BY date_sent
) , tenCTE (call_date, ten_Day) AS ( SELECT date_sent
  , COUNT(*) 'ten_day_call' FROM calls_made
   WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 11
   GROUP BY date_sent
) SELECT a.call_Date
 , a.five_day
 , b.ten_day
 FROM fiveCTE a
   FULL OUTER JOIN tenCTE b
     ON a.call_date = b.call_date
```
I’ve moved the two queries into CTEs for readability. I then join them on the date the call was made and return the results. I get this:

call_Date               five_day    ten_day

———————– ———– ———–

2011-12-01 00:00:00.000 2           3

NULL                    NULL        2

2011-12-03 00:00:00.000 2           3

NULL                    NULL        1

2011-12-05 00:00:00.000 1           1

Hmmm, not quite what I need, but it’s closer. I need to get the date for ten day calls, and I also need the NULLs removed from the five day calls.

My first take is to remove the NULL counts.
```
; WITH fiveCTE (call_date, five_day) AS ( SELECT date_sent
       , COUNT(*) 'five_day_call' FROM calls
       WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 6
       GROUP BY date_sent
) , tenCTE (call_date, ten_Day) AS ( SELECT date_sent
  , COUNT(*) 'ten_day_call' FROM calls_made
   WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 11
   GROUP BY date_sent
) SELECT a.call_Date
 , ISNULL( a.five_day, 0) 'five_day' , b.ten_day
 FROM fiveCTE a
   FULL OUTER JOIN tenCTE b
     ON a.call_date = b.call_date
```
This was better, and cleaned up the results slightly.

call_Date               five_day    ten_day

———————– ———– ———–

2011-12-01 00:00:00.000 2           3

NULL                    0           2

2011-12-03 00:00:00.000 2           3

NULL                    0           1

2011-12-05 00:00:00.000 1           1

Next, I’ll clean up the dates.
```
; WITH fiveCTE (call_date, five_day) AS ( SELECT date_sent
       , COUNT(*) 'five_day_call' FROM calls
       WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 6
       GROUP BY date_sent
) , tenCTE (call_date, ten_Day) AS ( SELECT date_sent
  , COUNT(*) 'ten_day_call' FROM calls_made
   WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 11
   GROUP BY date_sent
) SELECT ISNULL(a.call_date, b.call_date) 'date_sent' , ISNULL( a.five_day, 0) 'five_day' , b.ten_day
 FROM fiveCTE a
   FULL OUTER JOIN tenCTE b
     ON a.call_date = b.call_date
```
This is much better:

date_sent               five_day    ten_day

———————– ———– ———–

2011-12-01 00:00:00.000 2           3

2011-12-02 00:00:00.000 0           2

2011-12-03 00:00:00.000 2           3

2011-12-04 00:00:00.000 0           1

2011-12-05 00:00:00.000 1           1

That’s what I want, or, what my friend wants. However that wasn’t what I sent. I wasn’t sure that Access would support the full outer join and CTEs, so I actually came up with another way that I’ll write about next time.

If you know of a more efficient way of doing this, I’ve love to know what it is.
December 22, 2011
Creating a Service Master Key in SQL Server
The basis for all the encryption in SQL Server is the Service Master Key, which is the top of the encryption hierarchy. You can see the image below from Books Online of how this is setup.

The service master key is automatically created when you create a key that needs it, usually a database master key. So there is no CREATE SERVICE MASTER KEY command.

This key is encrypted and protected by the Windows DPAPI and the linked to the SQL Server service account. Since it secures all other keys in the encryption hierarchy, if you regenerate it, or restore it, all other keys it protects must be decrypted and re-encrypted. That can be a resource intensive operation, so don’t do that lightly.

You can regenerate a service master key, and you do that with the ALTER SERVICE MASTER KEY command like this:
```
ALTER SERVICE MASTER KEY REGENERATE
```
That’s it. If it works, no errors, no results. If an error occurs, you’d need to deal with it. However this isn’t something you should run often, and if you encounter errors while doing this, I’d suggest you immediately stop, backup all databases and master keys, and then work through the issues.

In another post, I’ll talk a little about the alterations you can make to the Service Master Key and how to back it up or restore it.
December 19, 2011