Friday, January 31, 2020

New Youtube Channel

Last year I noticed I was blogging way less than I should, in part because of new projects: work, personal stuff, local community activities, university projects... you name it.

As for 2020's goals, I want to be more active on my blog and try new stuff as well. One of the new things I am doing is starting to record videos (my first experience doing this, even when I have presented a couple of webinars, this is a whole new skill to learn).

According to some studies (don't ask me the accuracy of those), young people prefer videos over text, so I am giving it a try!

What am I using?


For now, my setup is very simple (and cheap):

  • Blue Yeti microphone - Best mic to create videos or do webcasts (check reviews), connect and start using it.
  • Logitech webcam C615 - not sure if I want to record myself!👽
  • Logitech G231 headset - Cheap but excellent sound and response, very comfortable also.
  • Camtasia Studio - To screen record and video editing, I think everybody uses it anyway.
  • My Dell laptop - That works just fine for what I need, no super fancy or powerful Mac required at this point.
  • Samsung Monitor - To be able to record screen and see my notes
  • Microsoft  Wireless desktop 3050 - Keyboard and Mouse, nothing special here, just cheap and do their job. One cool thing is it has keys at the top where you can program custom actions or key combinations (I have key combinations and custom apps mapped to them)
  • Logitech Spotlight- Not needed, but useful if you want to stand up and want to change slides. I feel more comfortable recording while standing up, it feels more natural for me, maybe when I have more experience I can sit down.

A picture of my setup:

Need a better desk! hahaha



What is my plan?


For now, learn how to create & edit videos, Camtasia has some tutorials to get started where you can learn the basics on how the product works.
They offer some free courses as well, to learn how to create quality content.

After that, creating short videos with particular topics related to data platform and cloud, and see how people respond to them. Maybe create a series of tutorials in the future.

I plan to do the videos in Spanish for now since it is my mother language, and I want to reach our local user group. Will consider to add English subs or mix English/Spanish.

Ok, but once you have your videos created, you have to share them somewhere, I have chosen Youtube because of its simplicity and popularity, also you can upload to the platform directly from Camtasia.

My youtube channel


I choose to create a new channel with my name, I had a previous one I created some years ago, but the name was not so professional (if you know what I mean! 😂)

I plan to do one series of short videos (around 1 minute) called "SQL en un minute" (SQL in a minute), and another set for longer videos dedicated to tutorials or product reviews.

You can visit it here (you are encouraged to subscribe! 🤓)

And this is my first video, let me know what you think!




Friday, January 3, 2020

T-SQL Basics: using OUTPUT

You are working on a database development project, and you need to obtain the records before/after an INSERT, DELETE, UPGRADE or MERGE Statement to present it to the user or do any work with that data.


A common approach that I see a lot and involves a lot of code to obtain the before/after information can be the following (maybe some IF and TRY...CATCH also) :


DECLARE @id INT = 1;

SELECT PostalCode
    , ModifiedDate
FROM Person.Address a
WHERE a.AddressID = @id;

UPDATE Person.Address
SET PostalCode = 95012, 
    ModifiedDate = 'Jan 02 2020'
WHERE AddressID = @id;

SELECT PostalCode
    , ModifiedDate
FROM Person.Address a
WHERE a.AddressID = @id;

The results will be something like this (before and after):


And the execution plan will look like this, each extra SELECT statement adds more workload to our server (independently of the resources used) :



Even when the code is easy to read, but if you use this pattern over all your codebase, maintain it can become difficult if you have to change object names or implement it on another system.

T-SQL language provides the OUTPUT clause, that allows you to retrieve information from a DML statement in the same batch.

NOTE: OUTPUT clause will return rows even when the statement presents an error and is rolled back, so you should not use the results if any error is encountered.

Sample usage is as follows:

-- Values After INSERT INTO --
INSERT INTO <MyTable>
OUTPUT INSERTED.*
VALUES (@a, @b);

-- Values After INSERT FROM --
INSERT INTO <MyTable>
OUTPUT INSERTED.*
SELECT * FROM <MyOtherTable>;

-- Values Before/After UPDATE --
UPDATE <MyTable>
SET <myField> = @value
OUTPUT 
 DELETED.<myField> as [ValueBefore],
 INSERTED.<myField> as [ValueAfter]
WHERE id = @key;

-- Deleted records After a DELETE --
DELETE <MyTable>
OUTPUT DELETED.*   
WHERE id = @key;


If we refactor the first example to use OUTPUT we obtain this code:

DECLARE @id INT = 1

UPDATE Person.Address
SET PostalCode = 95012, 
    ModifiedDate = 'Jan 02 2020'
OUTPUT 
 DELETED.PostalCode, 
 DELETED.ModifiedDate, 
 INSERTED.PostalCode, 
 INSERTED.ModifiedDate
WHERE AddressID = @id

Less code and also easy to follow and maintain, and the results will be something like this:


What if we take a look at the execution plan? we can see that this time just one sentence is executed (as expected):


Of course, performance can vary depending on how many rows are you retrieving back to the user, current workload, index and table design that is very own to your environment, but the more you know, the better you can adapt each situation to achieve the optimal performance.

Speaking of which, what if we have a lot of fields or records to work with, it is possible to store the output results on another table? of course, you can, as we can see in this other example:

-- To review the correct data has been deleted
-- we can output to a temp table
DELETE
FROM Person1
OUTPUT DELETED.*
INTO PersonBackupInfo
WHERE EmailPromotion = 0;

-- we can review/recover data if needed
SELECT * 
FROM PersonBackupInfo;

We can implement a deleted data review/rollback logic to our application to protect it from user errors (the typical Ooops queries) and protect our databases from an unnecessary point in time restores.

Like everything in life, nothing is perfect, and there are some remarks you should consider to implement it on your applications, so be sure to read it before using it.