In case you’re interested, the GDPR law is actually not bad to read. You might be affected by this, so go through the regulations. I’m doing that this week.
You can also see a nice article from David Poole at SQLServerCentral.
I do think GDPR will affect many of us, but to what extent, I’m not sure. Comments on what you think welcome.
This is day 2 of the Advent of Code 2017. If you want to read about the puzzles, start with Day 1. As I worked through the puzzles, I decided that I should be testing using their test sets and solving the issues that way. This lets me use the sample data, but also add in my own sets to cover strange situations.
Here are the tests that I used for each part of day 2.
This wasn’t a tough puzzle, and the test is fairly simple. I had a function that solves the puzzle with the help of input. My test just sets up the sample input in the table, tab delimited, and then calls the function to calculate the total.
EXEC tsqlt.NewTestClass @ClassName = N'tDay2'
go
CREATE OR ALTER PROCEDURE tDay2.[test day2 sample input]
AS
BEGIN
---------------
-- Assemble
---------------
DECLARE
@expected INT 18,
@actual int;
EXEC tsqlt.faketable @TableName = 'Day2', @SchemaName = 'dbo';
INSERT dbo.Day2 (DataRow)
VALUES ('5 1 9 5')
, ('7 5 3')
, ('2 4 6 8')
---------------
-- Act
---------------
SELECT @actual = SUM(b.diff)
FROM day2 a
CROSS APPLY dbo.AdventChecksum (a.DataRow) b
---------------
-- Assert
---------------
EXEC tSQLt.AssertEquals
@Expected = @expected,
@Actual = @actual,
@Message = N'An incorrect calculation occurred.';
END
GO
EXEC tsqlt.run 'tDay2.[test day2 sample input]';
The test here just calls a different function and has different input.
CREATE OR ALTER PROCEDURE tDay2.[test day2 b sample input]
AS
BEGIN
---------------
-- Assemble
---------------
DECLARE
@expected INT = 9,
@actual int;
EXEC tsqlt.faketable @TableName = 'Day2', @SchemaName = 'dbo';
INSERT dbo.Day2 (DataRow)
VALUES ('5 9 2 8')
, ('9 4 7 3')
, ('3 8 6 5')
---------------
-- Act
---------------
SELECT @actual = SUM(b.divmatch)
FROM day2 a
CROSS APPLY dbo.AdventChecksum3 (a.DataRow) b
---------------
-- Assert
---------------
EXEC tSQLt.AssertEquals
@Expected = @expected,
@Actual = @actual,
@Message = N'An incorrect calculation occurred.';
END
GO
EXEC tsqlt.run 'tDay2.[test day2 b sample input]';
GO
I got a note from United this week that summarized my travel for 2017. I’ve been feeling a bit itchy as I haven’t had any trips scheduled for 2018 so far. Strange for me, but looking over the summary, I’m glad.
Last year I flew 44 times with United, for 81k miles. I had 25 domestic flights and 19 international ones. That’s kind of crazy. Of course, outside of Denver, London is my most visited location.
For hotels, I logged 26 stays with Hilton that totaled 62 nights. That was low because I got stuck in a few other hotels at events early in the year. Plus I think I had 8 or 9 Air BnB nights. A lot of time away from home.
I’m a numbers person, so it’s neat to see the summaries from the services I use.
The one big number for me that shows me I traveled too much? Total workouts for the year: 265. That’s way too low.
When I started working with SQL Server and Windows, it seemed that the administrative side of many actions was limited. We could add and drop many items, but that was it. Relatively few tools allowed setting limits or disabling them, which was a pain. After all, I’d gotten used to setting up accounts for vendors and contractors in Active Directory, often disabling them when they weren’t in use. I couldn’t do this in SQL Server for many objects, which was a pain and an administrative burden to reset them up when troubleshooting issues. This was pre-PowerShell and .NET when any SMO access was a project in and of itself.
These days SQL Server has done a great job of adding in the ability to pause or disable many objects. We’ve had the ability to lock out an account for many versions, which is a great way to setup a vendor tech support account when it’s needed. This is especially important for security these days, as we may want to be sure that we prevent access by any suspect accounts. Disabling them allows us to prevent their use, but keep all their rights and permissions in the even they are valid accounts.
We can disable indexes, which can be useful as a precursor to deleting them at some point. We can disable triggers, which is incredibly useful when you are testing or debugging actions on a table. We can disable Extended Events, audits, and more. All of these are useful actions for a developer or administrator, if you use them.
When things go wrong, we’re often stressed and pressed for time. If there are issues with a system, many of us make snap decisions, which might fix the problem or make the situation worse. Even in those cases where we fix an issue, deleting or dropping objects might cause is extra work later. My question for you is what’s your first reaction? When you need to make a quick change to remove something, security access, an index, etc., do you drop or disable?
I would hope that you disable, as this removes the effects but keeps the object in the system with associated meta data. Rebuilding permissions or trying to get the old trigger code is a pain (since few people use a VCS, please start doing this). It’s possible that you won’t even be able to get things reset back up in the same manner. That might be fine, but it’s not ideal as new code should be tested, and in a crisis, it likely won’t be looked at too closely. At least the previous version of the code was tested in production.
Build the habit to disable, not drop, and I think you’ll be glad you did.
Steve Jones
Listen to the MP3 Audio ( 3.8MB) podcast or subscribe to the feed at iTunes and Libsyn.
