Type on Multiple Lines

Sometimes it would be nice if you could type on multiple lines simultaneously. For example if you copy a bunch of values from the results or an Excel File and you need to put quotes before and after it.

Well, turns out you can do that! Just put your cursor on the first line. Now while you press Shift+Alt use the Arrow Down key to mark the line you want to type on. You will see a vertical line appear to mark where you will be typing. Got it? Now just start typing whatever you want. You can use the Delete and Backspace keys as well.

Backup your Results

This is a nice one especially when you are doing some repairs. Usually I start out writing a Select Query to find the records that need to be repaired. Now before doing an Update statement on those records, it could be helpful to have a backup of those records.

There is an easy way to do that by just adding "into" followed by a new Table Name. What will happen when you execute the Query? The Table will be created with the exact structure as the Table you do the Select from. Take this one for example:

select * into Organizations_20210310
from Organizations
where IsCustomer = 1
GO

select * from Organizations_20210310

When you select from a Table, it will even recognize the Primary Key and Identity Columns. That will not work when you select from Multiple Tables or from a View, but still all the Data Types will be correct.

Triggers and Indexes will also not be copied to the new Table. But even though it will not do all of the work, it does more than enough if you just want a backup of the records you are about to Update.

Common Table Expressions

The Common Table Expressions or CTE’s are used to make Queries more simple by avoiding repeating the same Subqueries over and over again. By moving those into a sort of Select within the Select.

For example, let's say you have a complex Subquery to calculate the Revenue and another one for Costs. Now if you want to calculate the Result, you would need to copy those complex Subqueries and deduct one from the other. Thanks to Larry Tesler, Copy/Paste isn't that difficult, but still. You don't want duplication in your code.

So take a look at this example:

create view ResultsExample as
with tmpResults as (
select
1000 as Revenue,
650 as Costs
)
select
*,
Revenue - Costs as Result
from tmpResults
GO

select *
from ResultsExample
GO

drop view ResultsExample
GO

Using the With Statement I first wrote the Query to get the truly calculated values and named it tmpResults. To keep it simple I just used fixed values for Revenue and Costs, but you can easily imagine some complex Subqueries there.

Now to get the derived values I was able to do that using the tmpResults. By using the "Select *" and then just add the derived values.

So when the Revenue needs to be calculated differently, it only needs to be modified in one place. Cool trick, huh? Or is it just being lazy? ;-)

Oh yeah, in this case I used the Common Table Expression to create a View because you can do that. But I also this one without creating a View when I don't have sufficient rights to do that. So then it's like a one-time-only View. Or I use it like that in a Stored Procedure when I am confident it will only be used there. That helps to keep the number of views down to a minimum.

Recursive Common Table Expressions

Common Table Expressions can also be used to create a hierarchical structure. For example you have Projects that have Child Projects that can have Child Projects and so forth. Or a common example is a hierarchy of employees.

The following is an example of the latter, performed on an Exact Synergy Database.

with Resources as (
select res_id as ResourceID,
FullName as ResourceName,
0 as Level,
0 as Manager,
cast('' as nvarchar(100)) as ManagerName
from humres
where repto_id = 0
and res_id > 0
union all
select h.res_id,
h.FullName,
r.Level + 1,
h.repto_id,
cast(m.FullName as nvarchar(100))
from humres h
inner join Resources r on h.repto_id = r.ResourceID
inner join humres m on h.repto_id = m.res_id
)
select * from Resources
order by Level, ResourceName

As you can see I highlighted some of the lines. Let's see why that is:

with Resources as
This one is define the Common Table Expression and give it a name so it can be used later on.
union all + inner join Resources
The first part of the Query (above the union all) is also known as the Anchor. The second part after the union all joins on the Common Table Expression. As a result of that, the second part becomes recursive and will try and repeat itself while there is data to be found.
cast('' as nvarchar(100))
When using a union all in a Common Table Expression, the Data Types should match exactly. So if you use an empty string it would work in a regular union all. But here the empty string is recognized as a nvarchar(1) which doesn't match the Data Type of the FullName. Since that would result in an error, an explicit Cast is needed.
0 as Level + r.Level + 1
In the Anchor I specified 0 as Level so the top level Employees have that one. Then the Recursive part will get all the subordinates from the top level Employees. Those will have Level 0 + 1 = 1. Again the Recursive part will try and get the subordinates of the Level 1 Employees and give them Level 1 + 1 = 2. When the Recursive part will not find any subordinate, it will automatically stop trying.

These Recursive Common Table Expressions are great when you need to get a hierarchy. Especially when you cannot predict the number of levels you would need to go down.

That's it for Today!

Yes, those were my 4 SQL tips and tricks for today. If you enjoyed them, let me know and as I am already thinking about the next ones. Do you have suggestions for that? Those are welcome too!

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!