Words: 400
Time to read: ~ 2 minutes
Continue reading “Getting Details from a Maintenance Plan using PowerShell”
You must know your enemy before you can replace your enemy…
Words: 400
Time to read: ~ 2 minutes
Continue reading “Getting Details from a Maintenance Plan using PowerShell”
This is yet another time that a blog post has come about from a question by a developer. They’re good guys, I guess, they keep me on my toes.
This time it was with change logging. We didn’t have Change Data Capture (CDC), or Temporal Tables enabled (have you seen the YouTube videos by Bert Wagner ( blog | twitter ) on these?). What we did have was “manual logging” and no, I’m not even talking about Triggers.
What we had was INSERT
statements, directly after a MERGE
statement, that inserted into a table variable a hard-coded name of the column, the old value, and the new value.
Is that what I would do? Doesn’t matter, it was there before I got there, seems to work, and is low down on the list of priorities to change.
The question was, every time that they needed to add a column to a table, and change log it, they had to add multiple lines to the change tracking procedure and the procedure was getting gross and hard to maintain.
Something to do with DRYness?
You know the drill by now, I quite like to play along so let us facilitate that (from now on I’m going to use Gist, formatting with native WordPress is starting to annoy me).
USE Pantheon; | |
GO | |
DROP TABLE IF EXISTS dbo.GotAnyChange; | |
CREATE TABLE dbo.GotAnyChange | |
( | |
GotAnyChangeID int IDENTITY(1, 1), | |
Column1 int, | |
Column2 char(1), | |
Column3 date, | |
Column4 bigint, | |
Column5 varchar(50), | |
Column6 datetime | |
); | |
INSERT INTO dbo.GotAnyChange | |
( Column1, | |
Column2, | |
Column3, | |
Column4, | |
Column5, | |
Column6 | |
) | |
VALUES | |
(0, — Column1 – int | |
'A', — Column2 – char(1) | |
GETDATE(), — Column3 – date | |
0, — Column4 – bigint | |
REPLICATE('A', 50), — Column5 – varchar(50) | |
GETDATE() — Column6 – datetime | |
); | |
SELECT * | |
FROM dbo.GotAnyChange; |
This will create our table and, luckily, all of it’s columns are important enough to warrant capturing when they get changed.
Let’s take a look at the code that they were using, shall we?
USE Pantheon; | |
GO | |
DECLARE @Id int = 1 | |
— ChangeLog data store | |
— Yes, I know that they're all varchars… | |
DECLARE @ChangeLogTemp table | |
( | |
ColumnName varchar(100), | |
OldValue varchar(20), | |
NewValue varchar(20) | |
); | |
— Old & New value data store | |
DECLARE @OutputTemp table | |
( | |
OldColumn1 int NOT NULL, | |
NewColumn1 int NOT NULL, | |
OldColumn2 char(1) NOT NULL, | |
NewColumn2 char(1) NOT NULL, | |
OldColumn3 date NOT NULL, | |
NewColumn3 date NOT NULL, | |
OldColumn4 bigint NULL, | |
NewColumn4 bigint NULL, | |
OldColumn5 varchar(50) NOT NULL, | |
NewColumn5 varchar(50) NOT NULL, | |
OldColumn6 datetime NULL, | |
NewColumn6 datetime NULL | |
); | |
— Actual update, outputting the old and new values | |
UPDATE gac | |
SET gac.Column1 = 1, | |
gac.Column2 = 'B', | |
gac.Column6 = GETDATE() | |
OUTPUT Deleted.Column1, Inserted.Column1, | |
Deleted.Column2, Inserted.Column2, | |
Deleted.Column3, Inserted.Column3, | |
Deleted.Column4, Inserted.Column4, | |
Deleted.Column5, Inserted.Column5, | |
Deleted.Column6, Inserted.Column6 | |
INTO @OutputTemp | |
FROM dbo.GotAnyChange AS gac | |
WHERE gac.GotAnyChangeID = @Id; | |
— Capture changes. 1 for each column we want to capture | |
INSERT INTO @ChangeLogTemp | |
SELECT 'Column1', | |
OldColumn1, | |
NewColumn1 | |
FROM @OutputTemp | |
WHERE ISNULL(OldColumn1, NewColumn1) <> NewColumn1; | |
INSERT INTO @ChangeLogTemp | |
SELECT 'Column2', | |
OldColumn2, | |
NewColumn2 | |
FROM @OutputTemp | |
WHERE ISNULL(OldColumn2, NewColumn2) <> NewColumn2; | |
INSERT INTO @ChangeLogTemp | |
SELECT 'Column3', | |
OldColumn3, | |
NewColumn3 | |
FROM @OutputTemp | |
WHERE ISNULL(OldColumn3, NewColumn3) <> NewColumn3; | |
INSERT INTO @ChangeLogTemp | |
SELECT 'Column4', | |
OldColumn4, | |
NewColumn4 | |
FROM @OutputTemp | |
WHERE ISNULL(OldColumn4, NewColumn4) <> NewColumn4; | |
INSERT INTO @ChangeLogTemp | |
SELECT 'Column5', | |
OldColumn5, | |
NewColumn5 | |
FROM @OutputTemp | |
WHERE ISNULL(OldColumn5, NewColumn5) <> NewColumn5; | |
INSERT INTO @ChangeLogTemp | |
SELECT 'Column6', | |
OldColumn6, | |
NewColumn6 | |
FROM @OutputTemp | |
WHERE ISNULL(OldColumn6, NewColumn6) <> NewColumn6; | |
— Capture all changes as an XML | |
SELECT ColumnName AS CN, | |
OldValue AS OV, | |
NewValue AS NV | |
FROM @ChangeLogTemp | |
FOR XML PATH('Change'), ROOT('Changes'); |
And the results?
You can probably see the problem here.
Hey! It’s legacy code, let’s focus on just 1 problem at at time!
The main issue that I was asked about was every time a column was deemed important and needed to be added to the list, they had to insert another INSERT INTO @ChangeLogTemp...
and they thought that it wasn’t sustainable in the long run.
Hmmm it also comes across as very RBAR doesn’t it? Every time we want to include another column to the change tracking, we have to add them row by agonizing row. The script is already big enough, if we keep adding more, it will get massive!
Set based is 90% of the time the right way to go but how do we do set based solutions on the same table?
The first thing I do is to change that table variable into a temp table. Stats, indexes (if necessary), and I can query the results as we go along. Much better!
The second thing is that, whether by luck or by design, the legacy code has the same naming conventions for the columns; new column values are have the prefix “New%” in the column name and old columns have the “Old%” prefix.
This works for us because we can now split the new columns into 2 derived tables, New and Old, and that way we have the differences.
Have you ever tried to find the differences between two consecutive rows of data? It’s fiendishly difficult. WHERE Column1 on row1 != Column1 on row2
apparently just does not work, le sigh.
I’ve talked before about PIVOT
but now I’m going to introduce you to it’s little brother, UNPIVOT
, which “rotating columns of a table-valued expression into column values”
I say “little brother” because the whole document talks about PIVOT, with only brief mentions of UNPIVOT in the notes.
If you’re writing documentation like this, please stop.
With UNPIVOT
we can create a table of our rows around our ID and Column names…
… and with this, we can join on our ID and Column names and get to our more intuitive WHERE OldValue != NewValue
.
Bringing it all together!
USE Pantheon; | |
GO | |
DECLARE @ChangeLogAsXml xml, | |
@Id int = 1; | |
DROP TABLE IF EXISTS #OutputTableNew; | |
CREATE TABLE #OutputTableNew | |
( | |
GotAnyChangeID int NOT NULL, | |
OldColumn1 int NOT NULL, | |
NewColumn1 int NOT NULL, | |
OldColumn2 char(1) NOT NULL, | |
NewColumn2 char(1) NOT NULL, | |
OldColumn3 date NOT NULL, | |
NewColumn3 date NOT NULL, | |
OldColumn4 bigint NULL, | |
NewColumn4 bigint NULL, | |
OldColumn5 varchar(50) NOT NULL, | |
NewColumn5 varchar(50) NOT NULL, | |
OldColumn6 datetime NULL, | |
NewColumn6 datetime NULL | |
); | |
UPDATE gac | |
SET gac.Column1 = 2, | |
gac.Column2 = 'C', | |
gac.Column6 = GETDATE() | |
OUTPUT inserted.GotAnyChangeID, | |
Deleted.Column1, Inserted.Column1, | |
Deleted.Column2, Inserted.Column2, | |
Deleted.Column3, Inserted.Column3, | |
Deleted.Column4, Inserted.Column4, | |
Deleted.Column5, Inserted.Column5, | |
Deleted.Column6, Inserted.Column6 | |
INTO #OutputTableNew | |
FROM dbo.GotAnyChange AS gac | |
WHERE gac.GotAnyChangeID = @Id; | |
SELECT New.ColumnName AS CN, | |
New.NewValue AS NV, | |
Old.OldValue AS OV | |
FROM | |
( | |
SELECT Unpvt.GotAnyChangeID, | |
Unpvt.ColumnName, | |
Unpvt.NewValue | |
FROM | |
( SELECT [GotAnyChangeID], | |
CAST(ISNULL([NewColumn1], '') AS nvarchar(20)) AS [Column1], | |
CAST(ISNULL([NewColumn2], '') AS nvarchar(20)) AS [Column2], | |
CAST(ISNULL([NewColumn3], '') AS nvarchar(20)) AS [Column3], | |
CAST(ISNULL([NewColumn4], '') AS nvarchar(20)) AS [Column4], | |
CAST(ISNULL([NewColumn5], '') AS nvarchar(20)) AS [Column5], | |
CAST(ISNULL([NewColumn6], '') AS nvarchar(20)) AS [Column6] | |
FROM #OutputTableNew | |
) AS DataSource UNPIVOT(NewValue FOR ColumnName IN([Column1], [Column2], [Column3], [Column4], [Column5], [Column6])) AS Unpvt | |
) AS New | |
INNER JOIN | |
( | |
SELECT Unpvt.GotAnyChangeID, | |
Unpvt.ColumnName, | |
Unpvt.OldValue | |
FROM | |
( SELECT [GotAnyChangeID], | |
CAST(ISNULL([OldColumn1], '') AS nvarchar(20)) AS [Column1], | |
CAST(ISNULL([OldColumn2], '') AS nvarchar(20)) AS [Column2], | |
CAST(ISNULL([OldColumn3], '') AS nvarchar(20)) AS [Column3], | |
CAST(ISNULL([OldColumn4], '') AS nvarchar(20)) AS [Column4], | |
CAST(ISNULL([OldColumn5], '') AS nvarchar(20)) AS [Column5], | |
CAST(ISNULL([OldColumn6], '') AS nvarchar(20)) AS [Column6] | |
FROM #OutputTableNew | |
) AS DataSource UNPIVOT(OldValue FOR ColumnName IN([Column1], [Column2], [Column3], [Column4], [Column5], [Column6])) AS Unpvt | |
) AS Old | |
ON Old.ColumnName = New.ColumnName | |
AND Old.GotAnyChangeID = New.GotAnyChangeID | |
WHERE New.NewValue <> Old.OldValue | |
FOR XML PATH('Change'), ROOT('Changes'); |
And it works!
It’s not great though.
The whole thing was supposed to be to reduce the amount of changes required when they need to include or exclude columns. All in all though, it’s just 6 lines less. Not exactly the great return that you’d expect.
Yeah, true with the old way for every column we want to add we have to add an extra 6 lines while the new way adds 2.
That means for 1,024 columns:
So, is there anything else that we can do?
I’ve talked before about T-SQL automation with Dynamic SQL and this should be a good candidate for that.
What can we make dynamic here though? How about…
FOR ColumnName IN([Column1], [Column2], [Column3], [Column4], [Column5], [Column6])
bit?CAST(ISNULL([Old/NewColumn], '') AS nvarchar
bit?Well, temp tables exist in the tempdb database, they just get a suffix of a lot of underscores and a hex value.
So to get our column names, we can just query the sys.tables
and sys.columns
catalog views in [tempdb] and we should have what we need.
2. The FOR ColumnName IN (
I’ve talked before about concatenating values so we can use that to generate this part of the script.
3. The CAST(ISNULL(...
This is basically the same as the above. Don’t be put off by needing to add CAST(ISNULL(
before the column names, it’s not as complex as you’d think.
Now that we have our dynamic bits, let’s create the full statements.
USE Pantheon; | |
GO | |
DROP TABLE IF EXISTS #OutputTableDynamic; | |
GO | |
CREATE TABLE #OutputTableDynamic | |
( | |
GotAnyChangeID int NOT NULL, | |
OldColumn1 int NOT NULL, | |
NewColumn1 int NOT NULL, | |
OldColumn2 char(1) NOT NULL, | |
NewColumn2 char(1) NOT NULL, | |
OldColumn3 date NOT NULL, | |
NewColumn3 date NOT NULL, | |
OldColumn4 bigint NULL, | |
NewColumn4 bigint NULL, | |
OldColumn5 varchar(50) NOT NULL, | |
NewColumn5 varchar(50) NOT NULL, | |
OldColumn6 datetime NULL, | |
NewColumn6 datetime NULL | |
); | |
DECLARE @ChangeLogAsXml xml, | |
@Id int = 1; | |
UPDATE gac | |
SET gac.Column1 = 3, | |
gac.Column2 = 'D', | |
gac.Column6 = GETDATE() | |
OUTPUT inserted.GotAnyChangeID, | |
Deleted.Column1, Inserted.Column1, | |
Deleted.Column2, Inserted.Column2, | |
Deleted.Column3, Inserted.Column3, | |
Deleted.Column4, Inserted.Column4, | |
Deleted.Column5, Inserted.Column5, | |
Deleted.Column6, Inserted.Column6 | |
INTO #OutputTableDynamic | |
FROM dbo.GotAnyChange AS gac | |
WHERE gac.GotAnyChangeID = @Id; | |
DECLARE @DynamicSql nvarchar(MAX), | |
@NewColumns nvarchar(MAX), | |
@OldColumns nvarchar(MAX), | |
@columns nvarchar(MAX); | |
SET @NewColumns = | |
( | |
SELECT STUFF( | |
( | |
SELECT TOP (1024) | |
', CAST(ISNULL(' + QUOTENAME(RIGHT(tc.name, LEN(tc.name))) + ', '''') AS nvarchar(20)) AS ' | |
+ QUOTENAME(RIGHT(tc.name, LEN(tc.name) – 3)) | |
FROM tempdb.sys.tables AS tt | |
INNER JOIN tempdb.sys.columns AS tc | |
ON tt.object_id = tc.object_id | |
WHERE tt.name LIKE '#OutputTableDynamic%' | |
AND tc.name LIKE 'New%' | |
ORDER BY tc.column_id | |
FOR XML PATH('') | |
), 1, 2, '')); | |
SET @OldColumns = | |
( | |
SELECT STUFF( | |
( | |
SELECT TOP (1024) | |
', CAST(ISNULL(' + QUOTENAME(RIGHT(tc.name, LEN(tc.name))) + ', '''') AS nvarchar(20)) AS ' | |
+ QUOTENAME(RIGHT(tc.name, LEN(tc.name) – 3)) | |
FROM tempdb.sys.tables AS tt | |
INNER JOIN tempdb.sys.columns AS tc | |
ON tt.object_id = tc.object_id | |
WHERE tt.name LIKE '#OutputTableDynamic%' | |
AND tc.name LIKE 'Old%' | |
ORDER BY tc.column_id | |
FOR XML PATH('') | |
), 1, 2, '' ) ); | |
SET @columns = | |
(SELECT STUFF( | |
( SELECT TOP (1024) | |
', ' + QUOTENAME(RIGHT(tc.name, LEN(tc.name) – 3)) | |
FROM tempdb.sys.tables AS tt | |
INNER JOIN tempdb.sys.columns AS tc | |
ON tt.object_id = tc.object_id | |
WHERE tt.name LIKE '#OutputTableDynamic%' | |
AND tc.name LIKE 'New%' | |
ORDER BY tc.column_id | |
FOR XML PATH('') | |
), 1, 2, '')); | |
SET @DynamicSql = | |
N'SELECT New.ColumnName AS CN, New.NewValue AS NV, Old.OldValue AS OV FROM (SELECT Unpvt.GotAnyChangeId, Unpvt.ColumnName, Unpvt.NewValue FROM (SELECT [GotAnyChangeId], ' + @NewColumns | |
+ N' FROM #OutputTableDynamic ) AS DataSource UNPIVOT (NewValue FOR ColumnName IN (' + @columns | |
+ N') ) AS Unpvt ) AS New INNER JOIN ( SELECT Unpvt.GotAnyChangeId, Unpvt.ColumnName, Unpvt.OldValue FROM (SELECT [GotAnyChangeId], ' + @OldColumns | |
+ N' FROM #OutputTableDynamic ) AS DataSource UNPIVOT (OldValue FOR ColumnName IN (' + @columns | |
+ N')) AS Unpvt) AS Old ON Old.ColumnName = New.ColumnName AND Old.GotAnyChangeId = New.GotAnyChangeId WHERE New.NewValue != Old.OldValue FOR XML PATH(''Change''), ROOT(''Changes'')'; | |
EXECUTE sp_executesql @DynamicSql; |
Results are good!
Overall, the script is longer at nearly double the lines but where it shines is when adding new columns.
To include new columns, just add them to the table; to exclude them, just add in a filter clause.
So, potentially, if every column in this table is to be tracked and we add columns all the way up to 1,024 columns, this code will not increase.
Old way: at least 6,144.
New way: at least 2,048.
Dynamic: no change
Like the script, this was a massive post. Back at the start, I said that a developer came to me because they wanted to get more DRY (?) and stop needing to add more content to the stored procedure.
Do you think the developer used this?
Nope!
I can’t say that I blame them, it’s slightly ugly and unwieldy, and I wrote it so I should love it.
Yet if something was to go wrong and the need was there to open the procedure and troubleshoot it, the first person to open this up is going to let out a groan of despair!
So this request turned into a proof of concept and nothing more. No skin off my back, I have a growing list of tasks to accomplish by 5 minutes ago. Better get back to them.
XML – both easy, easy, lemon easy and difficult, difficult, lemon difficult…
Working on a blog post and I came up against a problem that I had heard of before but did not spend much brain-CPU power against.
I know I’m going to run into this again so let’s document this for future me. Oh he’s going to appreciate this so much!
There are a fair number of questions nowadays about returning data from a database in a comma separated string. Sure the application should probably do that but hey, database servers are expensive, why not get some bang for your bucks!
SQL Server 2017 has this lovely function called STRING_AGG()
Pop in your column and your separator and it takes care of it for you!
…wait not everyone has SQL Server 2017 yet?
…wait I don’t have SQL Server 2017 yet? Oh, I should really fix that…
So what can we do if we are not on SQL Server 2017? Take the advice that I was given for most of my life and STUFF
it!
Our playground:
USE tempdb; -- Test table SELECT dt.comments FROM ( VALUES ( 'XML is the bomb!'), ( '& JSON is cool too...') ) AS dt (comments);
I’m not the biggest fan of stuffing if I’m honest…tastes like dirt to me but hey, it works in 99% of situations…
SELECT STUFF((SELECT ', ' + dt.comments FROM ( VALUES ( 'XML is the bomb!'), ( '& JSON is cool too...') ) AS dt (comments) FOR XML PATH('') ), 1, 1, '') AS CommentsEnXML;
So…SQL Server is trying to be too helpful. What do we do? I normally turn to the SQL community and people like Rob Farley ( blog | twitter ), who has a lovely post about this.
So let’s try it out.
SELECT STUFF((SELECT ', ' + dt.comments FROM ( VALUES ( 'XML is the bomb!'), ( '& JSON is cool too...') ) AS dt (comments) FOR XML PATH(''), TYPE).value('.', 'varchar(max)' ), 1, 1, '') AS CommentsEnXML;
Little hiccup in preparing for my next post. Thankfully I learn from my mistakes and failures (there’d be no help for me otherwise!).
I’ll leave this post with a quote from the blog of the main man himself:
It’s a habit I need to use more often.
Yeah, me too Rob, me too…