Format

Yeah, yeah, don't laugh but I discovered this one just a few years ago. Even though it has been available in SQL for quite some years before that. At least since SQL 2014 so be careful if your query has to run on an older version.

This one is about formating. When I needed to get a formated date, I was using this one since SQL 2000 was still hot and happening:

select
right('00' + cast(datepart(Day, getdate()) as nvarchar(2)), 2) + '-' +
right('00' + cast(datepart(Day, getdate()) as nvarchar(2)), 2) + '-' +
cast(datepart(Year, getdate()) as nvarchar(4)) as FormatedDate

Hey! I told you not to laugh. ;-)

So you can imagine my surprise when I saw one of our consultants using this instead:

select format(getdate(), 'dd-MM-yyyy')

It is just so much simpler! In my defense, I have a text file containing SQL code snippets I often use. So I didn't type it out any time I needed it. And if you are an experienced dinosaur like me, do not forget that new handy functions are coming out with each new version.

Another thing with this one might come in handy when you want to have the date written out in full:

select format(getdate(), 'D', 'nl-NL')

While writing this post, that one gave me 'vrijdag 12 februari 2021' as the result. To find out what you can use as the last parameter, just Google for Culture Codes.

You can also do that trick with numbers. For example when you want to format an amount, using the currency symbol:

select format(1234.50, 'c', 'nl-NL')

That one will give you '€ 1.234,50'. And you can use it for just numbers as well. Using the correct usage of commas and decimal points depending on which culture you specified:

select format(1234.50, 'N', 'nl-NL')

One thing you have to be careful with is the use of point and colons. You can use them, but you have to put a backslash (\) in front of them. So if you want to format a number like '01.234.567', you should use this:

select format(1234567, '00\.000\.000')

Create? Or Alter?

Ok, enough about formating. Here is another one I used to do with a lot of code, but it is so much simpler today.

Let's say you want to copy a procedure, view, trigger or function to another database and you are not really sure if it already exists there. Safest way to do that is to check for that, drop it if it exists and then just create it. Something like this:

if exists(select name from sysobjects where xType = 'V' and name = 'Customers')
begin
drop view Customers
end
GO
create view Customers as
-- Code goes here
GO

Now what if I told you that it can also be done like this?

create or alter view Customers as
-- Code goes here
GO

Wow! Much simpler, huh? Do note, this is supported from SQL 2016 and up, so watch out with older databases.

Go! Go! Go!

This one I stumbled upon by accident. After typing a query I accidentaly hit the 5 button instead of the F5 button. And when I hit the F5 button after that, it executed the same query 5 times! Try it with this one:

select top 1 *
from sys.objects
GO 5

Now, that by itself is nice but not really useful. But let's say you need to populate a table with test data? Then use the number after GO like this:

create table testtable (
ID int identity,
Description nvarchar(50)
)
GO

insert into testtable(description)
values('Blabla')
GO 100

select * from testtable
GO

The first statement (the part before the first GO) is executed only once. But with the second GO I added 100 right behind it. SQL will then execute those lines 100 times.

This one has been around since SQL 2005 so it will pretty much work on most databases today.

Concat

There is a good chance you are already familiar with concat. The function to put strings together. It is easier to use than using the plus sign between the different strings. And an added benefit is that it will handle null values as well. Just try these two queries and see the difference:

select 'A' + null + 'C'
select concat('A', null, 'C')

But did you know it also handles casting and converting as well? Try this one for example:

select concat('The current date and time is ', getdate())

Yes, it will actually return something like 'The current date and time is Feb 13 2021 12:49AM'.

Grouping Sets

Last one for today is one that might be useful for troubleshooting. With this one you can create subtotals in your query. Yes, you read that right. You can actually show subtotals in the query results.

Now usualy such formating should be done in reports build on your query. But every now and then you simply do not have a reporting tool available, or it might just an one-time-only query and there is no need for a report.

So let's say you have an invoices table and you want to show the revenue per customer per year. But you also want to have a subtotal for all the years per customer. That's where grouping sets come in. Take a look at this one:

select datepart(year, fakdat), c.cmp_name, sum(f.tot_bdr)
from frhkrg f
inner join cicmpy c on f.debnr = c.debnr
group by
grouping sets (
(datepart(year, fakdat), c.cmp_name),
(c.cmp_name)
)
order by c.cmp_name

In the group by I specified 2 grouping sets. The first group by is on the year and the customer name. This will give you one row per customer per year.

The second one however is only on the customer name. And that one will give you the subtotal for that customer for all the years. Here is what the results look like:

You can add more grouping sets if you like. For example the first one could be on Year, Period and Customer. Then another one on Year and Customer which will show a subtotal of all the periods from that year. And then one on the customer only which will show the subtotal for that customer for all years.

Grouping Sets have been around since SQL 2014 so they are pretty safe to use today.

That's it for today!

Yes, those were my 5 SQL tips and tricks for today. If you enjoyed them, let me know and as I am already thinking about the next 5. 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!