Voice of the DBA

Tag: T-SQL

Advent of Code 2017 Day 1–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I like the Advent of Code. I stumbled on the site a few years ago and enjoyed working through some of the challenges in 2015. I tried solving them with three languages (Python, PoSh, and T-SQL) but only managed to get through about half the items before life got busy. Last year I was worn out and too busy to mess with the code.

This year I was busy in December when the puzzles came out, but decided to work through the puzzles again as a break from work and life, and to keep my mind flexible. I’ll do a series here, hopefully all 25, but here’s my view of Day 1.

Part 1

Each puzzle has two parts, and you need to solve part 1 to get part 2. In this case I need to take a list of input and compare each digit to the next digit and see if they match. If they do, then you add that digit to your sum.

This sounds like a perfect case to use the LEAD/LAG functions in SQL Server, so I did that. Since I get a long list of input, I also decided to use a string splitter. Here’s the first part where I split the string:
```
DECLARE @i VARCHAR(5000);
 --SET @i  = '1122';
 --SET @i  = '951344679963668529';

WITH myTally(n)
 AS
 (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
 FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
 CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)
 CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) c(n)
 CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) d(n)
 )
 , cteSplit (i)
 AS
 ( SELECT
 i = SUBSTRING(@i, n, 1)
 FROM myTally
 WHERE n <= LEN(@i)
 )

select i

from cteSplit
```
If you run this, you’ll get your @i string out a character at a time. This get’s me the data in a set of rows, similar to an array.

Now I need to compare each row with the next one, and I’ll use the LEAD function. My data is ordered, so I don’t need an order. That means I’m looking at this:
```
LEAD(i, 1) OVER (ORDER BY (SELECT NULL))
```
I’ll compare that with the current value and if they match, I’ll return the value. If not, I return a zero, adding nothing to the sum.

There is one special case. The list is circular, so if the last digit (when we get there) matches the first digit, I need to include that. I’ll add that as special FIRST_VALUE, LAST_VALUE function match. Here’s my code:
```
WITH myTally(n)
 AS
 (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
 FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
 CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)
 CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) c(n)
 CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) d(n)
 )
 , cteSplit (i)
 AS
 ( SELECT
 i = SUBSTRING(@i, n, 1)
 FROM myTally
 WHERE n <= LEN(@i)
 )
 , datacte(j)
 AS
 (
 SELECT j = CASE WHEN i = LEAD(i, 1) OVER (ORDER BY (SELECT NULL))
 THEN cteSplit.i
 ELSE 0
 END
 FROM cteSplit
 UNION all
 SELECT TOP 1 j = CASE WHEN FIRST_VALUE(i) OVER (ORDER BY (SELECT NULL)) = LAST_VALUE(i) OVER (ORDER BY (SELECT NULL))
 THEN i
 ELSE 0
 end
 FROM cteSplit
 )
 SELECT SUM(j) FROM datacte
```
When I do this with the input string, I get a result. It happened to be right

Part II

Part II is a variation of Part I. Instead of looking at the next digit, I need to look halfway around the list. The list is an even number, so if it’s 10 digits long, I need to start with char 1 and look at char 6. If they are equal, add to sum. Then look at 2 and 7, and repeat. Since it’s a circular list, when I get to char 5, I look around to char 1. There’s an optimization here, but when I solved this, I decided to take an easy way out.

I first considered adding the first half of the list to the end and only running through the first len(input) characters. Then I thought, I could easily do a LAG. So, I modified my datacte query to do this:
```
, datacte(j, k, i, l, n)
 AS
 (
 SELECT j = CASE WHEN i = LEAD(i, @len) OVER (ORDER BY (SELECT NULL)) AND n <= @len
 THEN cteSplit.i
 ELSE 0
 END
 ,
 k = CASE WHEN i = LAG(i, @len) OVER (ORDER BY (SELECT NULL)) AND n > @len
 THEN cteSplit.i
 ELSE 0
 END

)
```
This quickly counts the matches up to the middle and then counts the next bunch.

There’s a better way, but I’ll leave you to figure that out in the comments.

In any case, I solved both of day 1. Now on to day 2 during the next break.
January 4, 2018
Restoring a Copy Only Backup–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

There was a question posted recently at SQLServerCentral about whether a copy only backup could be restore with a transaction log backup from a database. I was positive this could, but decided I needed to repro and test for someone as there wasn’t a good BOL reference.

The Tests

Here’s what I did. First, I created a table in a database. I often do this and drop in messages to allow me to track the progress of backups and restores. This post follows my progress.

The Backups

Here’s my basic script:
```
CREATE TABLE logger(msg VARCHAR(200), msgdate DATETIME DEFAULT GETDATE())

INSERT logger (msg) SELECT 'pre full backup'
```
Next, I made a backup and added a message.
```
INSERT logger (msg) SELECT 'pre-log backup 1'
BACKUP LOG nba TO disk = 'nba_1.trn'
INSERT logger (msg) SELECT 'log backup 1 complete'
```
Once this is done, I’m in a state that I expect. A normal full backup, a normal log backup, and some data to help me track where I am.

Now let’s make a copy only backup.
```
INSERT logger (msg) SELECT 'pre copy-only backup '
BACKUP DATABASE nba TO DISK  = 'nba_copy.bak' WITH COPY_ONLY
INSERT logger (msg) SELECT 'copy-only backup complete'
```
This now means I have an open log sequence in the first log backup (post full backup) and a few log records since then. Some of these are inside the copy only backup.

Now let’s add more data and make a new, regular, log backup.
```
INSERT logger (msg) SELECT 'pre-log backup 2'
BACKUP LOG nba TO disk = 'nba_2.trn'
INSERT logger (msg) SELECT 'log backup 2 complete'
```
It’s at this point that I have this sequence:
- Full backup
- Log backup
- Copy-Only Full backup
- Log backup
The Restore

What I want to test is can I restore the Copy-Only backup and a log backup? I think I can, so let’s do that. First, restore from the copy-only backup, using the MOVE option.
```
USE [master]
RESTORE DATABASE [NBA2] 
FROM  DISK = N'D:\SQLServerBackup\MSSQL13.SQL2016\MSSQL\Backup\nba_copy.bak' 
WITH  FILE = 1,  
      MOVE N'NBA' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2.mdf',  
      MOVE N'NBA_log' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2_log.ldf',  
      MOVE N'nba_mo_file1' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2_mo',  
      MOVE N'nba_mo_file2' TO N'E:\SQLServer\MSSQL13.SQL2016\MSSQL\Data\NBA2_mo2'
,  NOUNLOAD,  STATS = 5
, NORECOVERY
```
Tip: Always use NORECOVERY

Now let’s try to restore the log.
```
RESTORE LOG NBA2 FROM DISK = 'nba_2.trn' WITH NORECOVERY

RESTORE DATABASE nba2 WITH RECOVERY
```
This works:

That should prove things. Let’s check the logger table.

That’s what we expect. The final message after log backup2 wasn’t captured in our backup files.

Copy Only Backups

What is a copy only backup? If we check the Copy-Only Backups page, we find that this is a regular backup in and of-itself, but it has the restriction that it cannot be used with differential backups. This also doesn’t change the differential bitmap, so that any differentials that are made ignore this backup and go back to include data changed since the last “normal” full backup.

SQLNewBlogger

Understanding backup and recovery is critical for a data professional. I’d say this is the most important skill, and it’s always worth writing about. Spend a few minutes reviewing scenarios and creating some posts like this to show you understand how the system works.
December 27, 2017
Renaming a Column–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One of the things I rarely do is rename objects. There are good reasons to do so, but often the changes required in other objects and applications isn’t worth the hassle. This is one reason why it would be good to spend a few minutes in design and come up with good names from the beginning.

In any case, do you know how to do this? You could use SSMS and easily change the design of the table. Of course, SSMS might try to rebuild the table, which might not be what you want. Hopefully that’s not the case, though you should always get the script instead of just saving the change.

The code you’d like to see is a simple meta data change that uses sp_rename. In my case, I want to change Qty to Quantity. I’d use this code:

EXEC sp_rename @objname = ‘Sales.OrderLines.Qty’ ,
@newname = ‘Quantity’ ,
@objtype = ‘column’;

I wish we had a direct ALTER TABLE statement that worked here, or better yet, an ALTER TABLE that allowed the entire table code to be shown (that’s not coming), but I’m not holding onto any hope that Microsoft will change this.

If you’re a person that thinks you might need a temp table that you insert data into and then two renames of the tables, that’s not the best way. Simple meta data changes are always preferred.

SQLNewBlogger

I ran into this while helping someone test a change and thought this was a good, easy reminder of how to change names. You could show you understand this in a blog in five minutes.

December 11, 2017
ROLLing UP Totals–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was editing an article recently that talked about ROLLUP, and I wanted to play with this a bit more. I hadn’t used this much in my career, but it’s a handy operator that’s worth including in your toolkit.

ROLLUP is used to provide additional totals for your aggregates while using GROUP BY. Here’s a little example. I’ve got some data for American Football quaterbacks. In this case, I’ve extracted some stats for a few noteworthy players today. Here’s a sample:

I want to aggregate some data together. For example, let’s say that we want to look at the total touchdowns scored per year by these players in their first 5 years. That’s a simple query:

SELECT
   Season = qs.CareerYear
   , TDs = SUM(qs.Touchdowns)
FROM dbo.QBStats AS qs
WHERE qs.CareerYear <= 5
GROUP BY qs.CareerYear

This gives me data like this:

However, when I look at this, I don’t have a total across all years. In my set, there are players that have played up to 18 seasons, and I want to know the aggregate number of TDs. I can get that by adding WITH ROLLUP. This is added

after the group by. However, I can also change this to GROUP BY ROLLUP and include the columns in parenthesis.

SELECT
   Season = qs.CareerYear
   , TDs = SUM(qs.Touchdowns)
FROM dbo.QBStats AS qs
WHERE qs.CareerYear <= 5
GROUP BY ROLLUP (qs.CareerYear)

This is OK, but the NULL isn’t great. What can I do here? I can add an ISNULL or COALESCE to my query and get this:

Useful, but what about if I’m aggregating by individual players? I can add the player to the column list, but I also need to add the player to the ROLLUP (or GROUP BY) list as well. If I do that, I get this:

That’s quite a bit more data, and I now see NULL values in the Player name. What’s this?

These are the totals for that subgroup. In this case, the first NULL above, next to the 1, is the total for all players for season 1. I can use another trick to clean this up:

This now shows some data in an easier to understand fashion, with some totals. We can see for season 1 that 3 players really started their careers in a strong fashion and 3 didn’t.

At the end, however, we get a different result.

This is because we have handled the player name as a NULL for the second column, but not the season as a NULL. For this last row, out “Total” text comes from an ISNULL of the first column. Here we’d want to do this:

This is where COALESCE comes in handy, allowing us to account for multiple NULL values.

ROLLUP is a nice way to get totals for each of your GROUP BY columns. This can make some of your reporting easier, and perhaps even faster by having the totals calculated in the result set.

SQLNewBlogger

This was a fairly easy piece to write. It took longer to get data together and find a set that had two grouping elements and was interesting than write the queries. I also had to play with ROLLUP a bit to clean up the NULLs, which always made this seem like a useless operator.

I think I’d look to use this more if I had the chance for many reports, as summing totals in some client tools is a pain.

November 6, 2017