## 2/16/20

### Counting currency...

I was just stumbling over an problem on a forum. They wanted to count currency. Let us imagine you have an ATM that delivers every single coin and bill from a specific currency, and people will withdraw any specific amount, and you want to calculate how many of each to deliver.

Well, look no further.

DECLARE @Pengar int = 9888;
DECLARE
@Valörer TABLE (Värde int, Typ char(5), Benämning varchar(25))
INSERT INTO @Valörer (Värde, Typ, Benämning)
VALUES (1, 'Mynt', '1 kr')
, (2, 'Mynt', '2 kr')
, (5, 'Mynt', '5 kr')
, (10, 'Mynt', '10 kr')
, (20, 'Sedel', '20 kr')
, (50, 'Sedel', '50 kr')
, (100, 'Sedel', '100 kr')
, (200, 'Sedel', '200 kr')
, (500, 'Sedel', '500 kr')
, (1000, 'Sedel', '1000 kr');
WITH
cash AS (
SELECT
ROW_NUMBER() OVER(ORDER BY Värde DESC) AS Ordning,
Värde, Typ, Benämning
FROM @Valörer WHERE Värde <= @Pengar
),
b AS (
SELECT
Ordning, Värde, Typ, Benämning,
(@Pengar - (@Pengar % Värde))/Värde Antal,
@Pengar % Värde AS Rest
FROM cash a WHERE Ordning = 1
UNION ALL
SELECT
a.Ordning, a.Värde, a.Typ, a.Benämning,
(b.Rest - (b.Rest % a.Värde))/a.Värde,
b.Rest % a.Värde
FROM cash a INNER JOIN b ON a.Ordning = b.Ordning + 1
),
cashing as (
SELECT
Typ, Benämning, Antal,
Antal * Värde AS [Valör Värde],
SUM(Antal * Värde) OVER(ORDER BY Ordning ASC) AS [Totalt Värde]
FROM b WHERE Antal > 0
)
SELECT * FROM cashing;

## 2/7/20

### Really, is the backup running?

Yes, and now you launched all those backup statements, and you wonder when they are done...

SELECT
session_id,
request_id,
command,
d.name as database_name,
start_time,
DATEDIFF(SECOND, start_time, GETDATE()) AS elapsed_time_in_seconds,
percent_complete AS estimated_percent_complete,
DATEADD(SECOND, ROUND(DATEDIFF(SECOND, start_time, GETDATE()) / percent_complete * 100.0, 0), start_time) AS estimated_finish_time
FROM sys.dm_exec_requests r INNER JOIN sys.databases d ON r.database_id = d.database_id WHERE command LIKE '%BACKUP%' OR command LIKE '%RESTORE%'

## 2/5/20

### Just a sample script of refreshing indexes in the current database

-- To rebuild or reorganize just the "few" fragmented of all indexes
-- in the current database...
-- And remember, this is just a sample of how you could do it.
-- See this as inspiration rather than the "best way"

DECLARE iterator CURSOR

FOR
SELECT
'ALTER INDEX [' + INDEX_NAME + '] ON [' + SCHEMA_NAME + '].[' + TABLE_NAME + '] ' +
CASE
WHEN avg_fragmentation_in_percent < 30 AND page_count > 200 THEN 'REORGANIZE'
ELSE 'REBUILD'
END stmt
FROM (
SELECT
c.name SCHEMA_NAME,
t.name TABLE_NAME,
i.name INDEX_NAME,
s.avg_fragmentation_in_percent,
s.page_count,
i.indid
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s
INNER JOIN sys.tables t ON s.object_id = t.object_id
INNER JOIN sys.sysindexes i ON s.object_id = i.id AND s.index_id = i.indid
INNER JOIN sys.schemas c ON t.schema_id = c.schema_id
WHERE s.avg_fragmentation_in_percent >= 10 AND i.indid > 0) m
ORDER BY m.indid ASC
DECLARE
@stmt varchar(max)
OPEN iterator
FETCH NEXT FROM iterator INTO @stmt
WHILE @@FETCH_STATUS = 0
BEGIN
-- PRINT
'EXECUTING: ' + @stmt -- Just if you want some output

EXEC (@stmt)
FETCH NEXT FROM iterator INTO @stmt
END
CLOSE
iterator

DEALLOCATE iterator

## 2/1/20

### Do I have that backup somewhere...

Yesterday evening I just cooked up a simple backup recipe, reason was just a simple weekly full/differential backup over all non-system databases on one instance. It's far from perfect, but it works.

It consists of a database to store information about backups of databases in a table with information about the last backups, and a procedure to do the backup. The below script also adds all databases with an database_id higher than 4, i.e. excludes master, model, msdb, and tempdb. If you want specific databases only just change the content of the Managed_Databases table.

USE master;
GO
CREATE DATABASE Simple_Maintenance;
GO
USE Simple_Maintenance;
GO
CREATE TABLE Managed_Databases
(
[db_id] int NOT NULL,
[db_name] sysname NOT NULL,
Last_Full_Backup datetime2(0) NULL,
Last_Full_Backup_Number int NULL,
Last_Differential_Backup datetime2(0) NULL,
Last_Differential_Backup_Number int NULL
);
GO
INSERT INTO Managed_Databases([db_id], [db_name])
SELECT database_id, name FROM sys.databases WHERE database_id > 4;
GO
And then we have the procedure, it's some lines of code. Hopefully you can read through it.

USE Simple_Maintenance
GO
CREATE OR ALTER PROCEDURE Make_Backup
@backup_destination varchar(max) = 'F:\Databases\MSSQL13.MSSQLSERVER\MSSQL\Backup',
@full_backup_day int    = 7,
@debug int       = 1
/*
Day for full backup
1 Monday
2 Tuesday
3 Wednesday
4 Thursday
5 Friday
6 Saturday
7 Sunday
*/
AS
BEGIN
SET DATEFIRST 1;
DECLARE
@database varchar(max),
@name_template varchar(max)  = '@database_@type_@number',
@type varchar(max),
@number varchar(max);
DECLARE
@backup varchar(max) = '
BACKUP DATABASE [@database]
TO DISK = ''@backup_destination\@name.BAK''
WITH
STATS = 10,
INIT, -- I FORGOT THIS IN THE ORIGINAL RECIPE
COMPRESSION
',
@exec varchar(max),
@db_id int,
@day int = DATEPART(weekday, getdate()),
@name varchar(max);
DECLARE dbs CURSOR FOR
SELECT [DB_ID], [DB_NAME] FROM Managed_Databases;
OPEN dbs;
FETCH NEXT FROM dbs INTO @db_id, @database;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @day = @full_backup_day
BEGIN
SET @type = 'FULL';
SELECT @number = CAST((ISNULL(Last_Full_Backup_Number, 2) % 2) + 1 as varchar(max)) FROM Managed_Databases WHERE [DB_ID] = @db_id;
SET @name = REPLACE(
REPLACE(
REPLACE(
@name_template,  '@database',@database),
'@type', @type),
'@number', @number);
SET @exec = REPLACE(
REPLACE(
REPLACE(
@backup, '@database',@database),
'@backup_destination', @backup_destination),
'@name', @name);
END
ELSE
BEGIN
SET  @type = 'DIFFERENTIAL';
SELECT @number = CAST((ISNULL(Last_Differential_Backup_Number, 2) % 2) + 1 as varchar(max)) FROM Managed_Databases WHERE [DB_ID] = @db_id;
SET @name = REPLACE(
REPLACE(
REPLACE(
@name_template,  '@database',@database),
'@type', @type),
'@number', @number);
SET @exec = REPLACE(
REPLACE(
REPLACE(
@backup, '@database',@database),
'@backup_destination', @backup_destination),
'@name', @name) +
', ' + @type;
END;
IF @debug = 1
BEGIN
PRINT @exec;
END
ELSE
BEGIN
IF @day = @full_backup_day
BEGIN
EXEC (@exec);
UPDATE Managed_Databases
SET
Last_Full_Backup = getdate(),
Last_Full_Backup_Number = CAST(@number as int)
WHERE [DB_ID] = @db_id;
END
ELSE
BEGIN
IF EXISTS(SELECT 1 FROM Managed_Databases WHERE [DB_ID] = @db_id AND Last_Full_Backup IS NOT NULL)
BEGIN
EXEC (@exec);
UPDATE Managed_Databases
SET
Last_Differential_Backup = getdate(),
Last_Differential_Backup_Number = CAST(@number as int)
WHERE [DB_ID] = @db_id;
END;
END;
END;
FETCH NEXT FROM dbs INTO @db_id, @database;
END;
CLOSE dbs;
DEALLOCATE dbs;
END;
And finally you just need to run the procedure. If you execute it with default parameters it will run in debug mode and only print out the syntax for doing backups. The prinout do not care if you have a full backup or not when there is a differential backup day. It gives you the syntax.

The procedure do not have any error checking either, you should probably add try...catch logic, and also checks in the database exists, and are in a backupable state. As I said, I cooked this up for fixing a simple need.

## 1/16/20

### Shinking transactionlogs

Hey friends, I usually get questions and concerns like "how do I shrink this database", "the logfile is so big", "please help me".

How do I shrink a database?
DBCC SHRINKDATABASE('databasename', 10)
-- 10 is the amount of percent free space in the database after the shrink operation

How do I shrink a transactionlog?
DBCC SHRINKFILE(2, 10)
-- 2 is the second file of the database, usually the transactionlog
-- 10 is the amount of percent free space in the file after the shrink operation

How do I get stats ablout the transactionlog?
These two queries might be of help...
select * from sys.dm_db_log_stats(db_id())
select * from sys.dm_db_log_info(db_id())

## 12/18/19

### dbo.digits() - a sample In-line TVF using CTE

I found a question in one of the online forums where a user wanted to generate digits, like for an counter, but control how many digits and a range of allowed digits.

ex. I want 2 digits, and only the digits between 3 and 5 giving me a result like this.

digits
33
34
35
43
44
45
53
54
55

I came up with this solution...

CREATE FUNCTION dbo.digits(
@len int,
@min int,
@max int
)
RETURNS TABLE
AS
RETURN
WITH
digits (digit) AS (
SELECT '0' UNION ALL SELECT '1' UNION ALL SELECT '2' UNION ALL
SELECT '3' UNION ALL SELECT '4' UNION ALL SELECT '5' UNION ALL
SELECT '6' UNION ALL SELECT '7' UNION ALL SELECT '8' UNION ALL
SELECT '9'
),
selection AS (
SELECT
digit
FROM digits
WHERE
CAST(digit AS int) BETWEEN @min AND @max
),
result AS (
SELECT
0 AS position,
CAST(digit as varchar(max)) digit
FROM selection
UNION ALL
SELECT
r.position + 1,
CONCAT(s.digit,r.digit)
FROM result r
CROSS JOIN selection s
WHERE r.position < @len
)
SELECT
digit AS digits
FROM result
WHERE position = @len - 1

You then execute the TVF with this line:
SELECT * FROM dbo.digits(2, 3, 5) ORDER BY 1

## 11/25/19

It's been a while since last post, and it is not as frequent as I wish, but it's about time or more lack of time.

First I want to celebrate Microsoft SQL Server for yet another amazing release. Now it is Microsoft SQL Server 2019, there are so many new features I guess I must start writing some about everything I love in this product. And that is almost a promise, I'll try to do more posts, and feature all this goodness.

Now over to todays topic, time. I found a question on the SQL Server 2008 - 2019 Facebook group, https://www.facebook.com/groups/7193059737/10157742825254738. And it was from Islam Selim:
Hi all
I need to create time column in view calculate differeance between two other datetime columns.. Plz help

CREATE FUNCTION dbo.fn_TimeBetween (
@start datetime,
@end datetime,
@days int  = 0 -- 0 for 00H,00M,00S (Default), 1 for 00D,00H,00M,00S
)
RETURNS varchar(max)
AS
BEGIN
DECLARE
@distance int = DATEDIFF(second, @start, @end),
@result varchar(max);
DECLARE
@seconds int = @distance % 60
SET
@distance = (@distance - @seconds) / 60
DECLARE
@minutes int = @distance % 60
SET
@distance = (@distance - @minutes) / 60
DECLARE
@hours int = @distance % 24
SET
@distance = (@distance - @hours) / 24
IF @days = 0
SET @result = CAST(@distance * 24 + @hours AS varchar(max)) + 'H,' +
RIGHT('0' +  CAST(@minutes as varchar(max)), 2)    + 'M,' +
RIGHT('0' +  CAST(@minutes as varchar(max)), 2)    +'S'
ELSE
SET @result = CAST(@distance AS varchar(max))     + 'D,' +
RIGHT('0' +  CAST(@hours AS varchar(max)), 2)    + 'H,' +
RIGHT('0' +  CAST(@minutes as varchar(max)), 2)    + 'M,' +
RIGHT('0' +  CAST(@minutes as varchar(max)), 2)    + 'S'
RETURN @result
END