Voice of the DBA

Tag: T-SQL

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

This is day 4 of the Advent of Code 2017. If you want to read about the puzzles, start with Day 1.

This is Day 4, which deals with checking a series of passphrases for duplicates. The idea is that for some input, each of the “words” in the string must be unique. This means that if I have a phrase of “dog cat horse”, it’s valid. If I have “dog cat bird cat”, it’s invalid.

This feels like an easy thing to do in SQL. Let’s begin.

First, I’ll build a table and load some data.
```
CREATE TABLE Day4
(
     passphrase VARCHAR(100)
)
GO
INSERT dbo.Day4
(
     passphrase
)
VALUES
    ('aa bb cc dd ee' )
  , ('aa bb cc dd aa')
  , ('aa bb cc dd aaa')
```
This is the test set. In this set, there are two values that are valid and one that isn’t. I need to figure out which is which.

This seems like a perfect place to use String_Split(). Since this is a small set (512 rows), performance isn’t a big concern. If it were, I’d be moving to use Jeff Moden’s string splitter.

For my tests, I decided to try something quick and dirty. I built a quick inline TVF to return string_split values. This is the code:
```
CREATE FUNCTION dbo.words
     (@input AS VARCHAR(100))
RETURNS TABLE
AS
RETURN ( SELECT value FROM STRING_SPLIT(@input, ' ') AS ss )
```
This made things easier for me to read. I like seeing the code clean, and this way I could easily most to a custom version of Jeff’s code if I wanted.

I quickly tried a short CTE with a CROSS APPLY to get the sets of rows that didn’t have unique passphrases. I thought this was easier since I know these are incorrect. If I look for corrects, it’s a harder issue as I’ll have counts of 1 in both correct and incorrect strings.
```
WITH ctePass
AS
(
SELECT d.passphrase 
        , ss.value, 
        cnt = COUNT(*)
FROM day4 AS d
     CROSS APPLY dbo.words(d.passphrase) AS ss
--WHERE d.passphrase = 'aa bb cc dd ee'
GROUP BY d.passphrase, ss.value 
HAVING COUNT(*) > 1
)
```
I also added a CTE that just gets me the total count.
```
, cteCount
AS
(SELECT total = COUNT(*) FROM day4)
```
Next, I added a CTE that would get my unique passphrases from the first CTE.
```
, cteUnique
AS
(
SELECT ctePass.passphrase
, cnt = COUNT(cnt) 
  FROM ctePass
  GROUP BY ctePass.passphrase
  )
```
Finally, I subtract the invalids from the total to get the vlaids. This gave me the answer that solved the puzzle.
```
SELECT total - COUNT(*) FROM cteUnique, cteCount
GROUP BY cteCount.total
```
Now let’s load the actual data. I cut and pasted data into a file. Let’s load that.
```
TRUNCATE TABLE day4
GO
BULK INSERT dbo.Day4 FROM 'e:\Documents\GitHub\AdventofCode\2017\Day4\Day4_Input.txt'
GO
SELECT 
  *
  FROM dbo.Day4 AS d
```
I can see I have 512 rows in my table, so my result must be between 1 and 512. When I ran this, I got the correct result.

A slight admission here. I misread at first, so I didn’t have the total and just got the 57. When that didn’t work, I re-read things and realized this. I tried to check for the valids, but realized that it was simpler if I just subtracted.

Part II

Part II is tricky, and I made a mistake the first time I tried to solve this. It took a bit, but I eventually figured out the issue.

In this part, we have to avoid anagrams. That means if the phrase as ‘car’ and ‘rac’, it’s invalid. Getting anagrams is tricky, and I looked around to find a post on SO that covers this. I adapted that code from Pawel Dyl to search for anagrams.

My first task was to put this into a function:
```
CREATE OR ALTER FUNCTION dbo.CheckAnagram
     (@w1 VARCHAR(50)
     , @w2 VARCHAR(50)
)
RETURNS int
AS
begin

declare @r INT;

WITH Src
AS
(
SELECT T.W1 ,
        T.W2
FROM
(
     VALUES
         (@w1, @w2)
) AS T (W1, W2)
) ,
      Numbered
AS
(
SELECT Src.W1 ,
        Src.W2 ,
        Num = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
FROM Src
) ,
      Splitted
AS
(
SELECT Num ,
        Word1 = W1 ,
        Word2 = W2 ,
        L1 = LEFT(W1, 1) ,
        L2 = LEFT(W2, 1) ,
        W1 = SUBSTRING(W1, 2, LEN(W1)) ,
        W2 = SUBSTRING(W2, 2, LEN(W2))
FROM Numbered
UNION ALL
SELECT Num ,
        Word1 ,
        Word2 ,
        L1 = LEFT(W1, 1) ,
        L2 = LEFT(W2, 1) ,
        W1 = SUBSTRING(W1, 2, LEN(W1)) ,
        W2 = SUBSTRING(W2, 2, LEN(W2))
FROM Splitted
WHERE LEN(W1) > 0
       AND LEN(W2) > 0
) ,
      SplitOrdered
AS
(
SELECT Splitted.Num ,
        Splitted.Word1 ,
        Splitted.Word2 ,
        Splitted.L1 ,
        Splitted.L2 ,
        Splitted.W1 ,
        Splitted.W2 ,
        LNum1 = ROW_NUMBER() OVER (PARTITION BY Num ORDER BY L1) ,
        LNum2 = ROW_NUMBER() OVER (PARTITION BY Num ORDER BY L2)
FROM Splitted
)
SELECT @r =  CASE
                   WHEN COUNT(*) = LEN(S1.Word1)
                        AND COUNT(*) = LEN(S1.Word2) THEN
                       1
                   ELSE
                       0
               END
FROM SplitOrdered AS S1
     JOIN
     SplitOrdered AS S2
         ON S1.L1 = S2.L2
            AND S1.Num = S2.Num
            AND S1.LNum1 = S2.LNum2
GROUP BY S1.Num ,
          S1.Word1 ,
          S1.Word2

IF @r IS NULL 
  SET @r = 0
RETURN @r
end
GO
```
Next, I wanted to get a list of items to check. Again, STRING_SPLIT is what I used, but a Jeff’s code works as well. I decided to use another function, which will split the string and then do a cross join to run both combinations of works against each other to check for anagrams.
```
CREATE OR ALTER FUNCTION dbo.SplitandCheck
(@string AS VARCHAR(100))
RETURNS int
AS
BEGIN
DECLARE @r INT = 0;

WITH mycte
AS
(
SELECT checks = CASE WHEN a.value = b.value then 0
ELSE dbo.CheckAnagram(a.value, b.value)
end
FROM STRING_SPLIT(@string, ' ') AS a CROSS JOIN STRING_SPLIT(@string, ' ') AS b
)
SELECT @r = SUM(a.checks)
FROM mycte a
```
This seemed to work, but when I got the final result with this code, it was wrong. Too high.
```
WITH mycte
AS
(
SELECT d.passphrase, IsAnagram = dbo.SplitandCheck(d.passphrase)
FROM dbo.Day4 AS d
)
SELECT mycte.IsAnagram, COUNT(*)
FROM mycte
--  WHERE mycte.IsAnagram = 0
GROUP BY mycte.IsAnagram
```
I had checked for 0s, but also included other results to try and debug my code. As I went through here, I realized that some fo the input data had a string like “oicgs rrol zvnbna rrol”. Clearly “rrol is an anagram of “rrol”. Initially I was knocking those out as a cross join includes those anyway.

As a result, I added this code to my function.
```
IF @r = 0
AND EXISTS(
SELECT COUNT(*)
FROM STRING_SPLIT(@string, ' ') AS ss
GROUP BY ss.value
HAVING COUNT(*) > 1
)
SET @r = 1
```
This will check for duplicate values. If there are dups, then clearly we have an issue already.

This give me a set of items with 1 dup as well as those with anagrams. The 0 result is the count of valid passphrases.
January 18, 2018
Advent of Code 2017 Day 2–#SQLNewBlogger

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

This is day 2 of the Advent of Code 2017. If you want to read about the puzzles, start with Day 1. Here is a look at Day 2, which deals with checksums of rows. However, it’s not just rows, but rows and columns.

Part I

In part 1, we are given a series of numbers in rows and columns, essentially a spreadsheet. We need to look at each row and compare all the values in columns.

This is slightly tricky for T-SQL. The input is a series of text values, so I cheated slightly and inserted those as rows into a table (I like tables). If I were taking this as a programming item, I’d have an import process, so I’ll do that here inline.

I have some idempotent logic here as I was testing and didn’t want to recreate tables and inserts, nor did I want to comment/uncomment things in/out.

IF NOT EXISTS( SELECT name FROM sys.sysobjects AS s WHERE name = ‘Day2’)
CREATE TABLE Day2
( DataRow VARCHAR(1000)
)
GO
IF (SELECT COUNT(*) FROM dbo.Day2 AS d) = 0
INSERT Day2 VALUES
( ‘179    2358    5197    867    163    4418    3135    5049    187    166    4682    5080    5541    172    4294    1397’),
( ‘2637    136    3222    591    2593    1982    4506    195    4396    3741    2373    157    4533    3864    4159    142’),
( ‘1049    1163    1128    193    1008    142    169    168    165    310    1054    104    1100    761    406    173’),
( ‘200    53    222    227    218    51    188    45    98    194    189    42    50    105    46    176’),
( ‘299    2521    216    2080    2068    2681    2376    220    1339    244    605    1598    2161    822    387    268’),
( ‘1043    1409    637    1560    970    69    832    87    78    1391    1558    75    1643    655    1398    1193’),
( ’90    649    858    2496    1555    2618    2302    119    2675    131    1816    2356    2480    603    65    128′),
( ‘2461    5099    168    4468    5371    2076    223    1178    194    5639    890    5575    1258    5591    6125    226’),
( ‘204    205    2797    2452    2568    2777    1542    1586    241    836    3202    2495    197    2960    240    2880’),
( ‘560    96    336    627    546    241    191    94    368    528    298    78    76    123    240    563’),
( ‘818    973    1422    244    1263    200    1220    208    1143    627    609    274    130    961    685    1318’)

The input might be different for you, so you’d have to do your own work.

Splitting a row of data into more rows seems like something easier done with the STRING_SPLIT function, so I’ll use that. This will give me a series of values from the table. I decided to use an inline TVF for this, since it’s a bit easier to read.

The trick here is that the data is tab separated, and \t doesn’t work in SQL Server. However, CHAR(9) works, so let’s use that. Here’s the function:

CREATE FUNCTION AdventChecksum ( @input NVARCHAR(200))
RETURNS TABLE
AS RETURN
SELECT diff = MAX(CAST(myint AS INT)) – MIN(CAST(myint AS INT))
FROM (SELECT myint = CAST(value AS INT) FROM STRING_SPLIT(@input, CHAR(9))) AS ss
GO

I need to get the difference between the largest and smallest values. STRING_SPLIT will return strings, so I cast this to an INT, then use MAX() and MIN() for the row, subtract one from the other, and return that.

Now I have a way to get a row checksum, so let’s get the entire table. We’ll do that with a CROSS APPLY, and then sum up all the values returned:

SELECT SUM(b.diff)
FROM day2 a
CROSS APPLY dbo.AdventChecksum (a.DataRow) b

I guess it works, because I got the right value from a test set, and the puzzle shows as solved.

Part II

Part II adds a twist. Now I don’t need to just compare two values, I actually need to see if any of the values are equally divisible by the other. This means I need to compare all values against each other.

The STRING_SPLIT() function still works well here, however, I need all combinations of the values divided by each other. An evenly divisible set of numbers would have this pseudocode:

INT(a) / INT(b) = FLOAT(a) / FLOAT(b)

Or, there’s another way to look at this. The remainder is 0, so the remainder of the values, using the modulo (%) function, is 0.

One last trick, each number is divisible by itself, so let’s avoid those. The instructions don’t mention the possibility that the only values evenly divisible are matches, so we’ll assume that’s not the case. Checking inputs, this appears to be OK.

I’ll create a new function, where I CROSS JOIN the STRING_SPLIT to get all combinations.

CREATE FUNCTION AdventChecksum2 ( @input NVARCHAR(200))
RETURNS TABLE
AS RETURN
SELECT divmatch = CASE WHEN (CAST(a.value AS INT) % CAST(b.value AS INT)) = 0 AND a.value <> b.value
        THEN (CAST(a.value AS INT) / CAST(b.value AS INT))
      ELSE 0
      end
       FROM STRING_SPLIT(@input, CHAR(9)) a
   CROSS JOIN STRING_SPLIT(@input, CHAR(9)) b
GO

Now I cross apply as in Part I and sum the values, which works. The puzzle is solved.

January 11, 2018
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