This post is a response to this month's T-SQL Tuesday #108 prompt by Malathi Mahadevan.  T-SQL Tuesday is a way for the SQL Server community to share ideas about different database and professional topics every month.

This month's topic asks to share how we learn skills other than SQL Server.

Watch this week's video on YouTube

I enjoy learning to do new things; there's a major sense of accomplishment I feel when I can tell myself, "Wow, I went from knowing almost nothing to being able to have this new skill."

Over the years I've realized I'm pretty consistent in how I go about learning something new, so what follows is my process for learning a new skill.

What I Am Learning

Recently, my non-SQL Server related learning goals have been to learn to use plain old vanilla JavaScript.

In this case I'm not necessarily starting from nothing (I have been writing JavaScript for close to 20 years now...) but previously where it was necessary to use a library like jQuery to get any kind of compatibility and consistency across browsers, the time has finally come where the JavaScript (ECMAScript) standard is mostly implemented correctly in most modern browsers.  No more need for large helper libraries!

And so the appeal here is that if I can ditch the overhead of a large library, my code will be simpler, easier to maintain, and faster to load and execute.

Steps to Learning a New Skill:

1. Commitment

For
me, the hardest part to learning a new skill is time management: if I don't
make time for it, it won't happen on its own.

I think the easiest way to make time to learn a new skill is to incorporate it into a project at work.  By aligning it with your day job, you're guaranteeing some time to work on it most days.  Yes, critical projects and deadlines do come up where learning has to be set aside temporarily, but if you can find a project that doesn't have urgent deadlines AND aligns with learning goals, then you'll be good to go.

For me, learning vanilla JavaScript is a great "at-work" project since
I'm already developing a lot of web apps with JavaScript anyway – the main
difference is I'll be using the standard JavaScript functionality instead of
working through a library like jQuery.

Now obviously this won't work in all scenarios: if you want to learn to build drones and you do development work for a chain of grocery stores, you probably can't figure out a way to align your interest with work (unless of course your company is trying to build out a drone delivery service).

In that case, you need to set aside time at home. This essentially comes down to your own discipline and timemanagement.  The key here is that youneed to set up time regularly and set yourself deadlines.  Instead of having the deadline of a workproject to help motivate you to learn, you need to tell yourself "I'mgoing to get this chunk of plastic and copper wiring up in the air by the endof the month" and try to deliver on that goal.

2. Go Cold Turkey

This is the hardest part of kicking any old habit. 
Ideally when learning something new, I like to use it exclusively in all
scenarios where I can.

This may not always be possible: sometimes there is a deadline you have to meet and trying a new skill that slows you down is not always the best idea.

But even if that's your scenario, pick at least one project to go completely cold turkey on for learning your new skill.  Going cold turkey on a project will force you to work through the hurdles and actually learn the necessary skills.

Thiscan be challenging.  I have the jQuerysyntax and methods ingrained in my brain from years of use; switching to usingstandard JavaScript is tough because I'm frequently having to look up how to dothings.  But if I picked the rightproject (ie. one without urgent deadlines), then this becomes a fun learningexperience instead of something stressful.

3. Build a Collection of Resources

The internet is awesome: it contains nearly all of the information you could ever want for learning a new skill.  The internet can also be a terrible place for learning a new skills if used incorrectly.

When learning something new, I try to find resources that guide me through a topic.  Whether it's a book, a website with a structured guide, a video course, or documentation with clear examples, it's important to find something that will teach you the why as well as the how.  I prefer using something with structure because it helps me learn the fundamentals correctly.

With my JavaScript learning, I have been enjoying the guides and daily newsletter at https://vanillajstoolkit.com/ .  That site also has clear documentation for the most common features.  The "official" documentation (https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference) is good to reference too, but can be overwhelming when first starting out.

What I don't like doing is searching for each question I have on StackOverflow.  Don't get me wrong, I love StackOverflow, but when learning some brand new skill I don't think it always provides the best answers.  Sometimes you get good answers, but sometimes you'll come across answers that, while technically correct, apply to some edge case, old version of a language, etc... that make them less-than-helpful when learning a new skill.

4. Document and Share

As I learn, I document what I learn.  This could be as simple as code snippets that I find myself using all the time, or it could be links to additional helpful resources.

Eventually I like writing up what I'm learning.  Even if no one reads it, summarizing your thoughts and ideas will help clarify and retain them better.  A summarized document or blog post also provides an additional reference for you to use if you need to in the future.

I haven't been blogging publicly about my JavaScript learning, but I have been
taking notes and sharing with individuals who are learning along with me.

5. Rinse and Repeat

That's it!  On my first pass at learning a new skill I try to finish a small project to get some immediate satisfaction.  I then pick a new project that's a little bit more ambitious, but still be manageable because I now have some knowledge that I didn't have before starting my first project.

Baby steps.  A little bit each day (or every other day).  Do it enough times and eventually you find yourself being fully capable of whatever skill you set out to learn.

This post is a response to this month's T-SQL Tuesday #107 prompt by Jeff Mlakar.  T-SQL Tuesday is a way for the SQL Server community to share ideas about different database and professional topics every month.

This month's Halloween themed topic asks to "... share a story about a project you worked on or were impacted by that went horribly wrong."

Watch this week's video on YouTube

I've been fortunate enough to never have been part of a large disastrous project at work.  My projects always have a "fail fast" mentality, so they never build up to a point where they come crashing down in a death spiral.

But that's not to say I haven't experienced my own project horror story in my personal work.

A while back I made a goal to produce a quality SQL Server focused blog post and video every week.  Essentially this means I am starting a new small-scale project each week where I play the part of project manager, developer, analyst, etc... with a delivery deadline of every Tuesday morning.  While I've gotten better at this process over time, I have also failed to meet my personal goals numerous for a variety of reasons.

Scope Creep-y

In order to meet my weekly deadline, I need to stay laser focused on the topic I choose for that particular week.  If I get additional ideas while writing and start trying to incorporate them into my post (ie. scope creep), I inevitably miss midweek milestones and have to try to make up time elsewhere to make my deadline.

One instance of scope-creep I experienced earlier this year was when I was trying to write a post on how to build a table-driven validation system.

I've built many table-driven processes in the past so this seemed like it would be an easy topic to write about.  I started that week's blogging process by building the demo templates that would include table structures, execution scripts, etc...

Instead of wrapping up my basic demos so I could move on to writing the actual post, I kept building out demos for more features: logging functions, parameterization, SQL injection protection, common performance problems, etc...

It was exciting to be building all of this out, but instead of creating one-week's content, I realized I had started working on enough demos for several weeks of posts.  This wouldn't have necessarily been a bad thing on its own; after all it's nice to be a few weeks ahead on content creation.

However, I didn't quite finish enough demos for any one post in particular, and due to some other life events I didn't get back to working on my demos until Sunday afternoon.  Normally at that point I'd already have my demos done, a blog post written, a video filmed, and either a finished video edit that I'm uploading or getting really close to uploading to YouTube.  What I had instead was a bunch of half-finished SQL demos saved in a very rough outlined blog post.

The Project Graveyard

This isn't the first time poor time management and scope creep has gotten me in trouble:

I have several posts that I've invested a good amount of time into but never released because they are incomplete.  In almost all of these cases my problems stemmed from poor planning and scope creep.

In the case of my table-driven post, by late-Sunday afternoon I realized I was going to miss my weekly deadline goal if I continued with that post, so I scrapped the idea for now and quickly wrote and shot a different post on an SSMS trick instead.  It was discouraging to have to do that, but at the end of the day I was able to meet my weekly deadline even if it was with a different result than I initially expected.

You might be thinking, "Why not ignore deadlines and release the post later in the week/month?"  For me, I like my weekly deadlines because I like the creative challenges that come from having time constraints.  It forces me to limit my scope and work on different projects on a regular basis.  My goal from blogging and video making is to learn how to present information in a succinct manner so that my communication skills, both written and verbal, improve.  So while I can (and probably will) complete these posts at some point in the future, I treat them as failures for that particular week's project.

And while failures aren't particularly fun, they can wind up being great learning opportunities: after all, I haven't gotten so off track due to scope creep ever since.

This post is a response to this month's T-SQL Tuesday #106 prompt by Steve Jones.  T-SQL Tuesday is a way for the SQL Server community to share ideas about different database and professional topics every month.

This month's topic asks to share our experiences with triggers in SQL Server.

Watch this week's video on YouTube

Triggers are something that I rarely use.  I don't shy away from them because of some horrible experience I've had, but rather I rarely have a good need for using them.

The one exception is when I need a poor man's temporal table.

Temporal Table <3

When temporal tables were added in SQL Server 2016 I was quick to embrace them.

A lot of the data problems I work on benefit from being able to view what data looked like at a certain point back in time, so the easy setup and queriability of temporal tables was something that I immediately loved.

No System Versioning For You

Sometimes I can't use temporal tables though, like when I'm forced to work on an older version of SQL Server.

Now, this isn't a huge issue; I can still write queries on those servers to achieve the same result as I would get with temporal tables.

But temporal tables have made me spoiled.  They are easy to use and I like having SQL Server manage my data for me automatically.

Fake Temporal Tables With Triggers

I don't want to have to manage my own operational versus historical data and write complicated queries for "point-in-time" analysis, so I decided to fake temporal table functionality using triggers.

Creating the base table and history table are pretty similar to that of a temporal table, just without all of the fancy PERIOD and GENERATED ALWAYS syntax:

CREATE TABLE dbo.Birds  
(   
 Id INT IDENTITY PRIMARY KEY,
 BirdName varchar(50),
 SightingCount int,
 SysStartTime datetime2 DEFAULT SYSUTCDATETIME(),
 SysEndTime datetime2 DEFAULT '9999-12-31 23:59:59.9999999'  
);
GO
CREATE TABLE dbo.BirdsHistory
(   
 Id int,
 BirdName varchar(50),
 SightingCount int,
 SysStartTime datetime2,
 SysEndTime datetime2  
) WITH (DATA_COMPRESSION = PAGE);
GO
CREATE CLUSTERED INDEX CL_Id ON dbo.BirdsHistory (Id);
GO

The single UPDATE,DELETE trigger is really where the magic happens though.  Everytime a row is updated or deleted, the trigger inserts the previous row of data into our history table with correct datetimes:

CREATE TRIGGER TemporalFaking ON dbo.Birds
AFTER UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @CurrentDateTime datetime2 = SYSUTCDATETIME();

/* Update start times for newly updated data */
UPDATE b
SET
       SysStartTime = @CurrentDateTime
FROM
    dbo.Birds b
    INNER JOIN inserted i
        ON b.Id = i.Id

/* Grab the SysStartTime from dbo.Birds
   Insert into dbo.BirdsHistory */
INSERT INTO dbo.BirdsHistory
SELECT d.Id, d.BirdName, d.SightingCount,d.SysStartTime,ISNULL(b.SysStartTime,@CurrentDateTime)
FROM
       dbo.Birds b
       RIGHT JOIN deleted d
              ON b.Id = d.Id
END
GO

The important aspect to this trigger is that we always join our dbo.Birds table to our inserted and deleted tables based on the primary key, which is the Id column in this case.

If you try to insert/update/delete data from the dbo.Birds table, the dbo.BirdsHistory table will be updated exactly like a regular temporal table would:

/* inserts */
INSERT INTO dbo.Birds (BirdName, SightingCount) VALUES ('Blue Jay',1);
GO
INSERT INTO dbo.Birds (BirdName, SightingCount) VALUES ('Cardinal',1);
GO
BEGIN TRANSACTION
INSERT INTO dbo.Birds (BirdName, SightingCount) VALUES ('Canada Goose',1)
INSERT INTO dbo.Birds (BirdName, SightingCount) VALUES ('Nuthatch',1)
COMMIT
GO
BEGIN TRANSACTION
INSERT INTO dbo.Birds (BirdName, SightingCount) VALUES ('Dodo',1)
INSERT INTO dbo.Birds (BirdName, SightingCount) VALUES ('Ivory Billed Woodpecker',1)
ROLLBACK
GO

/* updates */
UPDATE dbo.Birds SET SightingCount = SightingCount+1 WHERE id = 1;
GO
UPDATE dbo.Birds SET SightingCount = SightingCount+1 WHERE id in (2,3);
GO
BEGIN TRANSACTION
UPDATE dbo.Birds SET SightingCount = SightingCount+1 WHERE id =4;
GO
ROLLBACK

/* deletes */

DELETE FROM dbo.Birds WHERE id = 1;
GO
DELETE FROM dbo.Birds WHERE id in (2,3);
GO
BEGIN TRANSACTION
UPDATE dbo.Birds SET SightingCount = SightingCount+1 WHERE id =4;
GO
ROLLBACK

If you run each of those batches one at a time and check both tables, you'll see how the dbo.BirdsHistory table keeps track of all of our data changes.

Now seeing what our dbo.Birds data looked like at a certain point-in-time isn't quite as easy as a system versioned table in SQL Server 2016, but it's not bad:

DECLARE @SYSTEM_TIME datetime2 = '2018-09-07 16:30:11';
SELECT * 
FROM
    (
    SELECT * FROM dbo.Birds
    UNION ALL
    SELECT * FROM dbo.BirdsHistory
    ) FakeTemporal
WHERE 
    @SYSTEM_TIME >= SysStartTime 
    AND @SYSTEM_TIME < SysEndTime;

Real Performance

One reason many people loath triggers is due to their potential for bad performance (particular when many triggers get chained together).

I wanted to see how this trigger solution compares to an actual temporal table.  While searching for good ways to test this difference, I found that Randolph West has done some testing on trigger-based temporal tables.  While our solutions are different, I like their performance testing methodology: view the transaction log records for real temporal tables and compare them to those of the trigger-based temporal tables.

I'll let you read the details of how to do the comparison test in their blog post but I'll just summarize the results of my test: the trigger based version is almost the same as a real system versioned temporal table.

Because of how I handle updating the SysStartTime column in my dbo.Birds table, I get one more transaction than a true temporal table:

You could make the trigger solution work identical to the true temporal table (as Randolph does) if you are willing to make application code changes to populate the SysStartTime column on insert into dbo.Birds.

Conclusion

For my purposes, the trigger-based temporal table solution has a happy ending.  It works for the functionality that I need it for and prevents me from having to manage a history table through some other process.

If you decide to use this in your own pre-2016 instances, just be sure to test the functionality you need; while it works great for the purposes that I use temporal tables for, your results may vary if you need additional functionality (preventing truncates on the history table, defining a retention period for the history, etc... are all features not implemented in the examples above).

This post is a response to this month's T-SQL Tuesday #105 prompt by Wayne Sheffield.  T-SQL Tuesday is a way for the SQL Server community to share ideas about different database and professional topics every month.

This month's topic asks to share a time you ran into a metaphorical brick wall and how you worked it out.

Watch this week's video on YouTube

The Problem: Trimmed JSON Values

Recently I was using FOR JSON PATH to generate a JSON string from a query to use in a web app:

SELECT TOP 100 *
FROM master..spt_values
WHERE type='P'
FOR JSON PATH

The resulting JSON string is 5,580 characters long.

The goal was to read this query result into my .NET app like so:

var jsonResult = db.Database.SqlQuery<string>("SELECT TOP 100 * FROM ... FOR JSON PATH");

Is this the best way to design every app/database interaction?  Probably not, but it was right for this scenario because I didn't want to create models for all of the dynamic data I'd be returning.

Design decisions aside, my "brick wall" issue was that my "jsonResult" value was getting truncated around 2,000 characters instead of displaying the full 5,580.  The JSON string looked great in SSMS, but for the life of me I couldn't figure out why the data was getting chopped off when read into .NET.

Time to Debug

What follows are the usual steps I take when debugging a problem that has me stumped.  I've turned these into a 1950s style educational film so you can laugh at my bad jokes while your learn.

1. Rubber Duck Debugging

The first thing I usually do when I hit a wall like this is talk myself through the problem again.

This technique usually works well for me and is equivalent to those times when you ask  someone for help but realize the solution while explaining the problem to them.

To save yourself embarrassment (and to let your coworkers keep working uninterrupted), people often substitute an inanimate object, like a rubber duck, instead of a coworker to try and work out the problem on their own.

Alas, in this case explaining the problem to myself didn't help, so I moved on to the next technique.

2. Simplify the Problem

Breaking a problem down into smaller solvable parts can help sometimes.  I changed my query to return less data by switching to SELECT TOP 5 and seeing if .NET was still truncating the data.  It wasn't! Mildly successful!

In this case though, I couldn't really build off my simplified example.  As soon as my result passed ~2,000 characters, the JSON string was getting chopped off.

In this step I also figured out if I put my query into a derived table, my .NET code worked beautifully and returned the complete JSON string:

SELECT * FROM
(
SELECT TOP 100 *
FROM master..spt_values
WHERE type='P'
FOR JSON PATH
) t(c)

This was an ugly solution though and I didn't like doing it.  I especially didn't like it because I didn't know why a derived table fixed the output.

3. Check the Internet

As great as the internet is, I try to limit myself to how much searching I do on it when troubleshooting.  Searching for an online solution can quickly devolve into wasting two hours with nothing to show.

I performed some cursory searches on Google, StackOverflow, various forums, blogs, etc... but didn't find anything helpful (fun/sad fact: I searched for solutions again while typing up this post and now find plenty of solutions...who knows what I was doing wrong that day).

4. Ask a Friend

I love working through problems with others because I'm often amazed at how differently others approach a problem.  Often times this leads to a solution I would not have thought of on my own.  I especially enjoy hearing from people new to the subject area because they often have the most creative solutions due to not yet having become cynical and jaded from previous experience :).

I try to hold off on this option until at least trying all of the above techniques because 1) I hate breaking another person's concentration 2) I feel like I learn better if I struggle through a problem myself.

And in this case shopping the problem around didn't help - no one I talked to had a great solution.

5. Take a Break

After trying all of the above, I was out of ideas.  I took a break from the problem for the rest of the day, resolved to give it another try in the morning the morning.

6. RTFM

And the next morning, I had the idea to check the documentation to see what it said about the return type of FOR JSON PATH.

Embarrassingly, this one should be way higher on the list, and I'd like to say that it usually is, but for one reason or another I didn't bother checking until this late in the game.

And wouldn't you know it?  The last paragraph, of the last section, tells me exactly what I needed to know.

The documentation tells me that the JSON string will be broken up across multiple rows and my client app needs to concatenate them all together.  What I ended up doing is a simple String.Join():

var jsonResult = String.Join("",db.Database.SqlQuery<string>("SELECT TOP 100 * FROM ... FOR JSON PATH"));

There's no explanation for why SSMS is able to concatenate these values together but other client apps have to manually do so, but at least I found my documented solution.

Conclusion

Even though I found a somewhat-satisfactory solution in the documentation, my fall back was going to be to use the ugly derived table solution discovered in step 2.  It was ugly, but at some point I would have to call it quits and settle with an ugly workaround rather than spend more time on troubleshooting.

Next time I'll be sure to check the documentation earlier in the process and hopefully that will save me from some of the frustration I encountered in this particular scenario.

This month's T-SQL Tuesday topic asked "What code would you hate to live without?" Turns out you like using script and code to automate boring, repetitive, and error-prone tasks.

Thank you to everyone who participated; I was nervous that July holidays and summer vacations would stunt turnout, however we wound up with 42 posts!

Watch tsqltuesday.com for next month's topic and consider signing up to host.

Watch this week's video on YouTube

Without further ado, here are this month's entries sorted in random order:

• Stuart Moore shares the history behind needing to automate restore testing and writing the SqlAutoRestores PowerShell module to help.  Nowadays his commands are found in dbatools.  Great example of how a project can evolve through the community.
• Arthur Daniels shares his script to identify the key and included columns of indexes in a given table.
• Glenn Berry shares his DMV Diagnostic Queries and the story behind how he started developing them back in 2006.
• Jason Brimhall links to multiple scripts he's shared in the past as well as a new script for remotely auditing server access to catch infilitraters red-handed.
• Doug Purnell talks about how he uses database snapshots and shares some code for how he manages them.
• Jay Robinson shares two C# extensions (shout to my fellow devs!): one to check an enum for a value and a second to cleanly handle the lengthy DBNull.Value syntax.
• Drew Furgiuele shares how he scripts out his indexes to re-apply after snapshot replication.  He then writes very similar functionality using PowerShell in only 6 lines!
• Tim Weigel shares which community scripts he uses regularly, as well as sharing his own scripts around replication, stored procedure execution information, and file manipulation.
• Hugo Kornelis submitted two posts.  The first post shares sp_metasearch which helps with performing impact analysis and the second post follows up with an enhancement he's made to Ola Hallengren's database maintenance scripts to ignore backup BizTalk databases.
• Andy Mallon shares his comprehensive script for checking database, file, data, log, etc... sizes.  Great explanations of his reasoning for writing the queries the way he did.
• Dan Clemens shares his database search script with a switch that includes searching across agent jobs.
• Jess Pomfret wrote a script that shows compression stats for database objects.  Wanting to run it against a whole instance (or across mulitple servers), she wrote a dbatools command to automate the process.
• Kenneth Fisher shows us how he organizes his toolbox using an SSMS solution.
• Rob Farley shares code he's written to demonstrate the pain of using NOLOCK.
• Steve Jones shares a procedure from Microsoft that he uses for transferring logins and passwords between instances.
• Kevin Hill shares two scripts he uses for finding low-hanging index optimization fruit: one that finds queries performing heap or clustered index scans, and another that returns the top 5 missing indexes per database.
• Michael Villegas learned that Azure SQL doesn't allow you to graphically show user roles and permissions, so he wrote a script to query those details (works for on-premise SQL Server as well).
• Nate Johnson shares scripts that identify if tables are being replicated, whether SSRS subscriptions executed, and how much space certain objects and files are consuming.
• William Andrus shares how he uses his search script to find similarly named fields or all instances of a piece of text within a database.
• Bert Wagner (me!) I share my template for generating dynamic table-driven code, making queries more adaptable to future changes.
• Rudy Rodarte shows us a script he uses for iterating over a date range to use for executing date based queries.
• Brent Ozar admits he can't live without sp_Blitz, but this month he shares a script for checking how much plan cache history exists on a server.
• Jeff Mlakar offers a solution for taking all databases on an instance offline (and then back online) again.
• Erik Darling offers a solution for constructing dynamic SQL so that his MAX variables don't get truncated.  He also links to a script for printing long strings in SSMS.
• Chrissy LeMaire takes the hard work out of instance to instance migrations by sharing her single-line dbatools command that will do it all for you.  She also shares how dbachecks automates manual checklist work.
• Glenda Gable mentions two procedures, one that is a high performance cursor rewrite and one  that is a robust log shipping solution.
• Aaron Bertrand shows us how he discovers undocumented SQL Server features by comparing new builds to the previous versions.
• Ryan Desmond writes about his post-install confirguration process and shares code he runs to customize Ola Hallengren's maintenance scripts for his environments.
• Josh Simar shares his database file size code that is optimized for "very large databases" that span multiple files and filegroups.
• Sander Stad discusses the importance of sharing code and offers a few dbatools commands that he's contributed to or authored around backup testing, log shipping, and SQL Server Agent manipulation.
• Andy Levy wrote an SSMS snippet to generate a cursor.  Before you chew him out though, he has some really good uses cases for needing to use them.
• Andy Yun reveals what's in his T-SQL toolbox and explains his organization strategies for 10+ years of scripts he's collected.
• Eduardo Pivaral shares a script he uses to output query results into an HTML table, making it easy to copy into an email.
• Raul Gonzalez shows us a versatile script for searching database tables and returning information on attributes such as column name, size, key definitions, and more.
• Matthew McGiffen wanted to find the most expensive queries on an instance using Query Store instead of the traditional DMVs, so he wrote a script to do just that.
• Daniel Hutmacher shares his beefed up version of sp_help.  Includes ASCII art dependency graphs and database search.
• Christian Gräfe provides a function he wrote for padding the left-side of a value with zeros.
• Adrian Buckman  shares his SQLUndercover Inspector HTML reporting tool, as well as scripts for helping to alter AG groups, checking for running jobs, and auditing failed logins.
• Louis Davidson shares his technique for using relative positioning in date tables to make querying custom periods (eg. your company's fiscal month) easier.
• Lance England shares a PowerShell script to automate generating upsert merge statements for his ETLs.